<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>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?</p>

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

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.