Solved

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

Posted on 2011-02-28
9
799 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
Stored procedure 4 36
MS SQL page split per second is high 19 106
Table create permissions on SQL Server 2005 9 43
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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