Solved

T-SQL: Moving data from one IMAGE column to another column in another table.

Posted on 2011-02-28
9
800 Views
Last Modified: 2012-05-11
Hey.
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
GO
SET QUOTED_IDENTIFIER ON
GO
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
)
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO lime_sammanslagning_destination.dbo.[file]				/* EDIT HERE, Destination  */
			([filetype], [status], iduser, fileextension, [compressed])
		VALUES 
			(@@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

END
GO

Open in new window

0
Comment
Question by:Lime_Sweden
  • 5
  • 3
9 Comments
 

Author Comment

by:Lime_Sweden
ID: 35004708
I've also tried converting the datacolumn to a varbinary before moving it as some forums claimed that this would solve the problem.. but it dident.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35005401
what errors do you run into, using varbinary ?
note: your posts has @@ for some of the variables, is that a mistype? it should be @ only ...
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 35005513
have you considered just using replication to move the data across?

what compatability levels are the databases operating at? are they the same...
same instance / different instances?

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Lime_Sweden
ID: 35005705
Been playing around with this some more now and ive gotten it to work, not quite sure what the problem was but i just went through all the SSIS package steps and checked everything.. Dident change anything but now it works...

@angellll Ive been told to use @@ for all input paramaters as the way it *should* be, and single @ for all internal variables, not sure why besides "its standard".

@Lowfatspread would replication work if i have to keep all the original data in the destination table?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 35005747
well, here is the tech doc for identifiers:
http://msdn.microsoft.com/en-us/library/ms175874%28v=SQL.90%29.aspx

Rules for Regular Identifiers
The rules for the format of regular identifiers depend on the database compatibility level. This level can be set by using sp_dbcmptlevel. When the compatibility level is 90, the following rules apply:
1. The first character must be one of the following:
          * A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
          * The underscore (_), at sign (@), or number sign (#).
            Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
            Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.


so, while one can use @@ for variables, the second @ actually is part of the name, and has no special meaning at all.
looks like a naming convention I would really NOT suggest. but it does not harm.

so, back to the problem: what is the error you get?
with IMAGE, indeed, you cannot just "copy" with INSERT/SELECT, with VARBINARY though, it should work
0
 

Author Comment

by:Lime_Sweden
ID: 35005799
The weird part is that i never got any errors

And that now after going through everything it is actually working, Im pretty sure I havent changed anything from when i asked the question and was getting a weird copy of the file so im quite stumped as to why it actually works now.

This is working:
SET [data] = (Select [data] From lime_sammanslagning_destination.dbo.[file]  Where idfile =@@idoldfile)


0
 

Author Comment

by:Lime_Sweden
ID: 35005810
My worst worry now is that its copying the pointer and that the file is not being copied and im going to be in deep **** once the source table is dropped.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35005873
>My worst worry now is that its copying the pointer
with varbinary: no worries.
with image: I never tried such things :)

but  you can test, can't you, with 2 test tables?!
0
 

Author Comment

by:Lime_Sweden
ID: 35006338
Yeah just tested it, created two test databases and copied a row, then deleted source row and can still retrieve the file.
So no such problems :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question