copy blob (text field) data from one server to another

• paulg1

  SSC Enthusiast

  Points: 138

  More actions

  January 13, 2004 at 9:25 am

  #59062

  how do i copy a table with text data type from one server to another.

  dts has problems with truncating the text field.

• rockmoose

  SSCarpal Tunnel

  Points: 4601

  More actions

  January 14, 2004 at 1:38 am

  #489905

  Probably you could set up the other server as a "Linked Server" and use SELECT INTO.

  SELECT * INTO linkedserver.database.schema.TextTable FROM TextTable

  Or create a copy of the table and use regular insert. ( the SELECT INTO doesnt create an exact schema copy of original table. column defaults, indexes, pk's ... are not duplicated )

  /rockmoose

  
  You must unlearn what You have learnt

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 14, 2004 at 6:59 am

  #489942

  I believe I picked this up as one of the scripts submitted to the site;

  there's an export and inport function below; i never tested this myself:

   

  --**************************************

  --    

  -- Name: Procedure to Export/Import Imag

  --     es In/From SQL SERVER

  -- Description:the procedure is used as

  --     an Interface to the TextCopy Utility sup

  --     plied with SQL SERVER in order to simpli

  --     fy handling of BLOBS (images/docs)

  -- By: Eli Leiba

  --

  -- Inputs:@runpath varchar(100), -- text

  --     Copy Location

  @srvrvarchar(50), -- server TO LOAD

  @db varchar(50), -- DATABASE TO LOAD

  @usrvarchar(50), -- login USER

  @pwdvarchar(50), -- login password

  @tblvarchar(50), -- TABLE TO load/unload

  @colvarchar(50), -- COLUMN TO load/unload

  @whrvarchar(200), -- WHERE clause

  @filvarchar(100), -- filename including path

  @modchar(1) -- I FOR LOAD INTO Sql

  --

  -- Assumes:-- usage (assuming TextCopy.e

  --     xe is in c:\)

  CREATE TABLE pic (pic_id int,picture image)

  INSERT INTO pic VALUES (1,null)

  UPDATE pic SET picture = 'xx'

  -- Inserting image

  EXEC sp_imp_exp_images 'c:\textCopy.exe',

   'PCN1943',

  'PUBS',

  'sa',

  'sa',

  'pic',

  'picture',

  '"where pic_id = 1"',

  'c:\pic.jpg',

  'I'

  -- Extracting Image

  EXEC sp_imp_exp_images 'c:\textCopy.exe',

   'PCN1943',

  'PUBS',

  'sa',

  'sa',

  'pic',

  'picture',

  '"where pic_id = 1"',

  'D:\pic.jpg',

  'O'

  --

  -- Side Effects:Import/Export of BLOBS

  --

  --This code is copyrighted and has-- limited warranties.Please see http://

  --     http://www.1SQLStreet.com/xq/ASP/txtCodeId.498/

  --     lngWId.5/qx/vb/scripts/ShowCode.htm--for details.--**************************************

  --    

  CREATE PROCEDURE sp_imp_exp_images

  (@runpath varchar(100), -- textCopy Location

   @srvrvarchar(50), -- server TO LOAD

   @db varchar(50), -- DATABASE TO LOAD

   @usrvarchar(50), -- login USER

   @pwdvarchar(50), -- login password

   @tblvarchar(50), -- TABLE TO load/unload

   @colvarchar(50), -- COLUMN TO load/unload

   @whrvarchar(200), -- WHERE clause

   @filvarchar(100), -- filename including path

   @modchar(1)) -- I FOR LOAD INTO Sql , O FOR output FROM SQL

  AS

   DECLARE @cmd varchar(1000)

   SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +

   ' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +

   ' /F ' + @fil+ ' /' + @mod

   EXEC Master..xp_cmdShell @cmd

  GO

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• paulg1

  SSC Enthusiast

  Points: 138

  More actions

  January 14, 2004 at 7:27 am

  #489949

  thanks lowell,

  this looks like it will help for the image files but i am still troubled by a description field (text datatype).

  we are migrating our backend web database to a new structure, and the product description field contains formatted pages of text.

  We resorted to changing the datatype to a varchar(8000), migrating the data, and then changing back to a text datatype. We do lose some special characters (ie. an apostrophe (') turns into a (?)) but if the text is longer than 8000 I risk truncation of data.