Geek Answers Handbook

Copy BLOB values ​​between databases using pure SQL in SQL Server

I need to pull some BLOB data from a SQL Server 2005 database and create an SQL script to insert the same data into another database on another server.

I am only allowed to do this with SQL scripts; I cannot use any other utility or write a program in Java or .NET to do this.

Another big limitation that I have is that I do not have access to the source database (where the source BLOB data) when I run the script to copy the BLOB data to the target database, so the data should already be encoded in a file SQL script.

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

I can run special T-SQL statements and stored procedures if necessary.

+5
source share
3 answers

TEXTCOPY was an example application that was included in SQL Server 7.0 and 2000, but was no longer available in SQL Server 2005.

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

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

, ( SQL script), , .

, / BULK OPENROWSET: http://msdn.microsoft.com/en-us/library/ms191184.aspx

+2

" SQL Server " :

TEXTCOPY :

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

/ , .

Vinko , , xp_cmdshell .

TEXTCOPY:

SQL Server . - ( "where clause" ) "".

IN (/I), "" SQL Server, . OUT (/O), SQL Server "", .

+1

More articles:


All Articles