Exporting Image data to local hard drive

Question

Post reply

Exporting Image data to local hard drive

bryan.duchesne

Ten Centuries

Points: 1134
More actions
September 14, 2010 at 8:45 am

#157200

I am trying to extract images from a database table that contains and ID and an image column. I am using BCP and the command successfully executes however, the data is being written (I think) to the SQL server local drive but I need to have it on my local hard drive.
SET @sql = 'BCP "SELECT pat_image FROM cids_net.dbo.Patient_image where pat_id = ''003555''" QUERYOUT C:\TEMP\003555.jpg -n -S -T' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql
I cannot seem to see how I can make the QUERYOUT direct the output to my local PC. Anyone have a solution for this?

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

the server thinks C:\ drive is it's own drive....

to write to your PC, you need to use a UNC path:

\\YOURPCNAME\c$\ImageFolder

that might not work if the account that the SQL Service is running under(NOT the login you are using!) has access to network shares or not.

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!

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 2

Unfortunately, our network does not support that way of addressing the local PC.

Lowell SSC Guru Points: 323504 More actions · Answer 3

in that case, if you cannot access the server's C:\Temp folder as in your example, you'll have to use some intermediate item...a vbscript, your own program, etc to connect to SQL from your PC, download the image from the db, and write it to local disk;

here's the first gfoogle item i found:

http://www.motobit.com/tips/detpg_read-write-binary-files/

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!

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 4

Thanks for all the tips. I wrote a VB.NET application (very small) that does the job that I need.