BCP Export varbinary to hard disk

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BCP Export varbinary to hard disk

Author

Topic

x_math_x
Starting Member

2 Posts

Posted - 2008-10-22 : 12:35:18

Hi
I try to export a image to me hard disk with

declare @SQLcommand nvarchar(4000);
set @SQLcommand = 'bcp "select top 1 ImgData From tblImages order by IdImage ASC" queryout "c:\test.png" -n -S ' + @@ServerName + ' -U **** -P ****'
exec xp_cmdshell @SQLcommand, NO_OUTPUT

Its work well, except one thing, The output file containt 4 byte more at the beginning of the file, and i checked in the database and the file has the correct size.

did any body know something about that ?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 13:59:00

The default prefix for nvarchar is 4bytes

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 14:36:45

Try specify a format file first,

bcp "select top 1 ImgData from tblImages" queryout "c:\test.png" -S[SERVERNAME] -U****** -p*******
Enter the file storage type of field img [nvarchar]:  {enter}
Enter prefix-length of field img [2]: 0 
Enter length of field img [0]: {enter}
Enter field terminator [none]: {enter}

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: tblImages.fmt
----------------------------------------------------------
Finally you can export correctly:

bcp "select top 1 ImgData from tblImages" queryout "c:\test.png" -S[SERVERNAME] -U****** -p******* -f tblImages.fmt

x_math_x
Starting Member

2 Posts

Posted - 2008-10-22 : 15:05:51

Its Work very well,
Thank you very much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources