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

Question

Post reply

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

paulg1

SSC Journeyman

Points: 88
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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rockmoose SSCarpal Tunnel Points: 4571 More actions · Answer 1

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: 323482 More actions · Answer 2

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',

'pic',

'picture',

'"where pic_id = 1"',

'c:\pic.jpg',

'I'

-- Extracting Image

EXEC sp_imp_exp_images 'c:\textCopy.exe',

'PCN1943',

'PUBS',

'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 Journeyman Points: 88 More actions · Answer 3

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.