2

I have to import the table data in VARBINARY(MAX) format from one SQL Server (located on remote machine on other domain where i can connect through RDP connection) to my local box.

i did try the method to export the data from source to txt file by casing column to VARCHAR(MAX) but after import the datab is getting changed

Any help

Thanks Atul

Atul Bansal
  • 151
  • 2
  • 12
  • Atul are you Using Import Export Wizard directly to connect Source and Dest SQL Server? or exporting data to TXT files and then importing into local server using Import\Export Wizard ? – knkarthick24 Jun 23 '14 at 08:54
  • i did simple select with output to file to put the data in a txt file and then use the bulk insert SQL Command to import the data back into sql server – Atul Bansal Jun 23 '14 at 09:04
  • Can you plese share the BULK INSERT SQLCODE which you used? – knkarthick24 Jun 23 '14 at 09:31
  • CREATE TABLE #Test ( Col1 VARCHAR(100), Col2 NVARCHAR(MAX) ) BULK INSERT #Test FROM 'E:\DB\Result.txt' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR='\n') – Atul Bansal Jun 23 '14 at 09:34

2 Answers2

1

You might want to try using BCP.

This is a CLI utility for importing/exporting data from SQL server to a file that comes as part of a SQL server install. I tend to use it if I need to back up a diagram from SQL server. It will work fine with VARBINARY column types.

Example: To export: 

C:\TARGET_DIR>bcp [MyDatabaseName].dbo.MyTableName out MyTableName.bcp -c -T -S localhost

To import:

C:\TARGET_DIR>bcp [MyDatabaseName].dbo.MyTableName in MyTableName.bcp -c -T -S localhost

NB

  • the file will be imported or exported from the current directory in these examples.
  • change [MyDatabaseName].dbo.MyTableName to your databasename.your schema name. your table name
  • change localhost to your server name
  • if you have problems using -c as the file storage format, and you are an adminstrator then try -n (native mode) instead.

See the link at the top of this post for a full CLI reference, and more examples.

James S
  • 3,558
  • 16
  • 25
0

Atul please try the following steps instead of directing SELECT to output file

1) Export data using Import/Export Wizard to flat file in the remote machine.(Delimited with | pipe). Use the SAME SELECT statement in the Wizard option(Write a query to specify the data to transfer).

2) Import data using Flat File as the source and your Local Server as Destination in Import/Export Wizard.

Be cautious while converting TEXT datatype to Varbinary(max).
TEXT->VARCHAR(MAX)->VARBINARY(MAX).
Please refer the below screenshot for better understanding. Whtever the data you had in column of Text Datatype should come to Target Table of column has Varbinary(MAX) datatype

enter image description here

knkarthick24
  • 3,106
  • 15
  • 21
  • In that case also it is giving error Error 0xc0208030: Data Flow Task 1: The data type for "Destination - result_new_txt.Inputs[Flat File Destination Input].Columns[Col2]" is DT_IMAGE, which is not supported. Use DT_TEXT or DT_NTEXT instead and convert the data from, or to, DT_IMAGE using the data conversion component. (SQL Server Import and Export Wizard) – Atul Bansal Jun 23 '14 at 09:56
  • Similar situation i faced once how i tackled out is, I created one intermediate table same as target table structure except the VARBINARY(MAX) datatype column with TEXT Datatype. I imported data from TXT file to that intermediate table. Then all you need is direct move from intermediate table to target table.(Its not a generic solution though) – knkarthick24 Jun 23 '14 at 10:19
  • if the we this on the same server the data looks ok But as soon as you export this data in text file or something and then import then the binary data gets changes. i also tried this. sorry man for the trouble – Atul Bansal Jun 23 '14 at 10:53
  • Attached image for your reference. I manually copy out the content(of TEXT Datatype column) from Intermediate table and then wrote update statement to laod that value into my target table. Update TRG set Col2= – knkarthick24 Jun 23 '14 at 10:56