I try to export long binary field to text file and insert into to another database, but the export of the long binary will not be correct. I'm writing this way:

SELECT long_binary_filed from testtable;
output to 'c:\temp\Test1.txt' ENCODING 'utf-8';

What I'm wondering is how I can export long binary fields to a text file so I can add it to another database then with an insert?

asked 18 Oct, 11:10

Rolle's gravatar image

Rolle
439293345
accept rate: 0%


I suggest that you use xp_write_file() since it will not try to do any character conversion on the binary blob when it writes it to the file.

permanent link

answered 18 Oct, 11:24

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

Okay, thanks for the quick reply. If I then export this.

SELECT xp_write_file( 'c:\temp\Test1.txt', long_binary_filed ) FROM testtable;

Do I then read it with xp_read_file? How?

(18 Oct, 11:36) Rolle
Replies hidden

Yes, one way to read the file would be to use xp_read_file.

begin
  declare @blob long binary;
  set @blob = xp_read_file( 'c:\temp\Test1.txt' );
  ...
end;
(18 Oct, 11:46) Mark Culp

Then I do an insert (or update) like this?

INSERT INTO testtable ( long_binary_field ) VALUES ( xp_read_file( 'c:\temp\Test1.txt' ) );
(18 Oct, 12:51) Rolle
Replies hidden

Yes, that should work.

(18 Oct, 13:16) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7

question asked: 18 Oct, 11:10

question was seen: 71 times

last updated: 18 Oct, 13:16