Copying BLOB values between databases with pure SQL in SQL Server

Question

I need to pull some BLOB data from a SQL Server 2005 database and generate a SQL script to insert this same data in another database, in another server.

I am only allowed to do this using SQL scripts, I can't use any other utility or write a program in Java or .NET to do it.

The other big restriction I have is that I don't have access to the original database (where the original BLOB data is) when I run the script, to copy the BLOB data to the target database, so the data should already be encoded within the SQL script file.

Summing up: is there a way to encode the BLOB data into text so that I can dump it into a SQL INSERT command within a script text file and run it?

I am able to run special T-SQL statements and stored procedures if needed to.

Answer 1

This article "Copy Text or Image into or out of SQL Server" could help:

You could integrate the TEXTCOPY command line tool in a stored procedure:

CREATE PROCEDURE sp_textcopy (
  @srvname     varchar (30),
  @login       varchar (30),
  @password    varchar (30),
  @dbname      varchar (30),
  @tbname      varchar (30),
  @colname     varchar (30),
  @filename    varchar (30),
  @whereclause varchar (40),
  @direction   char(1))

AS

DECLARE @exec_str varchar (255)
SELECT @exec_str =
         'textcopy /S ' + @srvname +
         ' /U ' + @login +
         ' /P ' + @password +
         ' /D ' + @dbname +
         ' /T ' + @tbname +
         ' /C ' + @colname +
         ' /W "' + @whereclause +
         '" /F ' + @filename +
         ' /' + @direction
EXEC master..xp_cmdshell @exec_str

You'll have to change/extend it a little in order to read the created file into the other database.

As Vinko writes in the comment to this answer, hold in mind that this requires enabling xp_cmdshell in the surface area configuration.

Description of TEXTCOPY:

Copies a single text or image value into or out of SQL Server. The value is a specified text or image 'column' of a single row (specified by the "where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is copied into SQL Server, replacing the existing text or image value. If the direction is OUT (/O) then the text or image value is copied from SQL Server into the specified 'file', replacing any existing file.

Answer 2

TEXTCOPY was a sample application included in SQL Server 7.0 and 2000 but no longer available in SQL Server 2005.

However, googling for TEXTCOPY in SQL Server 2005, I found this alternative that might do the trick:

http://sequelserver.blogspot.com/2007/01/texcopy-sql-server-2005.html

It relies on writing and reading the binary data from the filesystem, which in my case is not ideal (ideally I would like to encode the binary data with the SQL script text file itself), but it's the best I've found so far.

Here's another good source on how to do binary import/export operations using BULK OPENROWSET: http://msdn.microsoft.com/en-us/library/ms191184.aspx

Answer 3

Take a look at this question and search for bcp on the page - has example of both import and export for varbinary(max) (which is the new standard type for such columns). you can run arbitrary query for export.

http://stackoverflow.com/questions/1643627/how-to-insert-a-blob-into-a-database-using-sql-server-management-studio