Data script

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Picture of type image so the
insert is wrong in this way. How do i correct it?

/*****MarinLIf TAble ********************/
DECLARE @.ID INT
DECLARE @.typeID INT
DECLARE @.NAME NVARCHAR(50)
Declare @.scName nvarchar(50)
Declare @.distribution nvarchar(600)
Declare @.maxSize nvarchar(200)
Declare @.env nvarchar(200)
Declare @.climate nvarchar(200)
Declare @.country nvarchar(2000)
Declare @.desc nvarchar(4000)
Declare @.pic image

DECLARE CURS CURSOR STATIC FOR
SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic
FROM MarinLif
OPEN CURS
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
PRINT 'SET IDENTITY_INSERT MarinLif ON'
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic)
VALUES (' + convert(varchar,@.ID) + ','
+ convert(varchar,@.typeID) + ','
+ '''' + @.NAME + '''' +
+ '''' + @.scName + '''' + ','
+ '''' + @.distribution + '''' + ','
+ '''' + @.maxSize + '''' + ','
+ '''' + @.env + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.country + '''' + ','
+ '''' + @.desc + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.pic + '''' + ')'
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
END
PRINT 'SET IDENTITY_INSERT MarinLif OFF'
CLOSE CURS
DEALLOCATE CURS

I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!MC (anonymous@.discussions.microsoft.com) writes:
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>...
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?

You can't assign to image variables, so this approach is not going
to work.

You are probably better off using BCP, a command-line which is designed
for importing and exporting data. In this case you could try:

bcp yourdb..MarinLif out MarinLif.bcp -N -T -S source_server
bcp yourotherdb..MarinLif in MarinLif.bcp -N -T -S target_server

-N here means that you are using native datatypes with Unicode. -T is for
trusted connection. -S specifies the server.

For -N to work, the tables must be identical, including column order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erland's response, if both databases are on the same server, you
can use the INSERT INTO... SELECT syntax:

INSERT INTO MyOtherDatabase.dbo.MarinLif
(
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
)
SELECT
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
FROM MyDatabase.dbo.MarinLif

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <anonymous@.discussions.microsoft.com> wrote in message
news:40c4c158$0$165$c397aba@.news.newsgroups.ws...
>
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>
> /*****MarinLIf TAble ********************/
> DECLARE @.ID INT
> DECLARE @.typeID INT
> DECLARE @.NAME NVARCHAR(50)
> Declare @.scName nvarchar(50)
> Declare @.distribution nvarchar(600)
> Declare @.maxSize nvarchar(200)
> Declare @.env nvarchar(200)
> Declare @.climate nvarchar(200)
> Declare @.country nvarchar(2000)
> Declare @.desc nvarchar(4000)
> Declare @.pic image
> DECLARE CURS CURSOR STATIC FOR
> SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic
> FROM MarinLif
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> PRINT 'SET IDENTITY_INSERT MarinLif ON'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic)
> VALUES (' + convert(varchar,@.ID) + ','
> + convert(varchar,@.typeID) + ','
> + '''' + @.NAME + '''' +
> + '''' + @.scName + '''' + ','
> + '''' + @.distribution + '''' + ','
> + '''' + @.maxSize + '''' + ','
> + '''' + @.env + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.country + '''' + ','
> + '''' + @.desc + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.pic + '''' + ')'
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> END
> PRINT 'SET IDENTITY_INSERT MarinLif OFF'
> CLOSE CURS
> DEALLOCATE CURS
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Data Processing Extension - parameters not created

Hi,
I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
using the FsiExtension sample as a basis. My problem is that the
CreateParameter method of the command class doesn't get called, so when I am
to execute my datareader, the parameters aren't there. I have added the
parameters manually to the dataset in the report, but they don't get carried
over to the DPE. I've debugged the code, and the CreateParameter never gets
called. The strange thing is that I had it working earlier, because I then
could access the parameter collection in the debugger, but now suddenly it
fails.
public IDataParameter CreateParameter()
{
return (IDataParameter)(new dbpDataParameter());
}
Any tips?
Brgds
JonasI found the problem, my class only implemented IDbComamand. When I added
IDbCommandAnalysis and
a GetParameters method it worked like it should.
/Jonas
"Jonas" <Jonas@.nospam.pl> wrote in message
news:OyFcwrwiGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
> using the FsiExtension sample as a basis. My problem is that the
> CreateParameter method of the command class doesn't get called, so when I
> am to execute my datareader, the parameters aren't there. I have added the
> parameters manually to the dataset in the report, but they don't get
> carried over to the DPE. I've debugged the code, and the CreateParameter
> never gets called. The strange thing is that I had it working earlier,
> because I then could access the parameter collection in the debugger, but
> now suddenly it fails.
> public IDataParameter CreateParameter()
> {
> return (IDataParameter)(new dbpDataParameter());
> }
>
> Any tips?
> Brgds
> Jonas
>

data import question

Hi,
I am writing a script for a client to update TableA using the data in
TableB. I know how to write an UPDATE query to do this. But the client wants
an ability to add or remove fields that they update using this script. I
know I can do this by letting the client enter the names of the source and
destination fields in a custom table and generate a dynamic query to update
the provided columns. But the problem is there are large number of fields
and sql has the limit of 8000 characters on variables write a dynamic query.
So writing a dynamic query is not an easy option either. So my question is
how do I solve this problem. Please not that we need to update 150+ fields
in 500,000+ records using this script. So it also needs to be as fast as
possible.
Thanks in advance.helpful sql wrote:

> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client wan
ts
> an ability to add or remove fields that they update using this script. I
> know I can do this by letting the client enter the names of the source and
> destination fields in a custom table and generate a dynamic query to updat
e
> the provided columns. But the problem is there are large number of fields
> and sql has the limit of 8000 characters on variables write a dynamic quer
y.
> So writing a dynamic query is not an easy option either. So my question is
> how do I solve this problem. Please not that we need to update 150+ fields
> in 500,000+ records using this script. So it also needs to be as fast as
> possible.
> Thanks in advance.
Here's one possibility:
UPDATE A
SET
col1 = CASE WHEN @.col1 = 'Y' THEN B.col1 ELSE A.col1 END,
col2 = CASE WHEN @.col2 = 'Y' THEN B.col2 ELSE A.col2 END,
col3 = CASE WHEN @.col3 = 'Y' THEN B.col3 ELSE A.col3 END
..
WHERE ...?
David Portas
SQL Server MVP
--|||Dynamic SQL can take several variables to exceed the 8000 byte limit.
DECLARE @.X1 VARCHAR(8000), @.X2 VARCHAR(8000)
EXEC(@.X1 + @.X2)
Andrew J. Kelly SQL MVP
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eC2Vg$IDGHA.3748@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client
> wants an ability to add or remove fields that they update using this
> script. I know I can do this by letting the client enter the names of the
> source and destination fields in a custom table and generate a dynamic
> query to update the provided columns. But the problem is there are large
> number of fields and sql has the limit of 8000 characters on variables
> write a dynamic query. So writing a dynamic query is not an easy option
> either. So my question is how do I solve this problem. Please not that we
> need to update 150+ fields in 500,000+ records using this script. So it
> also needs to be as fast as possible.
> Thanks in advance.
>