Data script error? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data script error?

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2004-06-04 : 03:51:48

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,MarinLIf_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,MarinLIf_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

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-04 : 04:17:08

What are you trying to achieve here?

-------
Moo. :)

mariechristine
Starting Member

27 Posts

Posted - 2004-06-07 : 02:50:36

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?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 05:48:17

You will be limitted to 8000 chars in that statement (4000 as you will need unicode) so it's not going to work for image or text datatypes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-07 : 06:26:27

To be honest I think you would be better off extracting your images now onto a standard file system, and leaving them there.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources