In a project I'm involved in i need to migrate data between two identical databases used by a data system.
I'm however having difficulties moving data between two IMAGE columns, located in two identical tables, in two different databases on a SQL 2005 server.
The IMAGE column (named [data]) can contain data for jpg, pdf, doc among basically any other filetype.
After reading quite a bit on the IMAGE column type I'm realising this is not easiest datatype to deal with but unfortunatly i cant change that.
So my thought for moving this column was to create an SSIS package which called a stored procedure for every row in the table that i want to move. I have included this stored procedure below. The procedure was just coded to run on my test copy of one of the databases so its really reading and writing from the same table.
Now, onto the problem..
This method works great for images such as jpeg's, but it does not work at all if the row contains for example a .txt. The data that being moved for texts does not even seem to be from the right place. When I moved a txt using this method i could open the file but the moved file contained xml and unreadable characters instead of the original text.
Anybody have any ideas as how to do this?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Alter PROCEDURE dbo.csp_moveDocumentFromDatabasetoThis
@@idoldfile as int
,@@iduser as int = 1
,@@fileextension as nvarchar(32)
,@@filetype as int
,@@compressed as int = 0
,@idnewFile INT OUTPUT
SET NOCOUNT ON;
INSERT INTO lime_sammanslagning_destination.dbo.[file] /* EDIT HERE, Destination */
([filetype], [status], iduser, fileextension, [compressed])
(@@filetype, 0, @@iduser, @@fileextension, @@compressed)
Select @idnewFile = SCOPE_IDENTITY()
UPDATE lime_sammanslagning_destination.dbo.[file] /* EDIT HERE, Destination */
SET [data] = ( Select [data]
From lime_sammanslagning_destination.dbo.[file] /* EDIT HERE, Source */
Where idfile = @@idoldfile )
Where idfile = @idnewFile