Bulk Copy with Images
-
Hi... I have a table with a column containing images. I'd like that my sql server 2005 stores those images on his backup device. I tried with the following command EXEC xp_cmdshell 'bcp "SELECT Image FROM table WHERE ID=1" queryout "C:\Temp\myImage.jpg" -c -T' I get a file that seems a jpg image... but it's not... I think it's because bcp queryout works only with text data. Does somebody have any idea on how can I get this working?
Life is not short... the problem is only how you organize yourself
-
Hi... I have a table with a column containing images. I'd like that my sql server 2005 stores those images on his backup device. I tried with the following command EXEC xp_cmdshell 'bcp "SELECT Image FROM table WHERE ID=1" queryout "C:\Temp\myImage.jpg" -c -T' I get a file that seems a jpg image... but it's not... I think it's because bcp queryout works only with text data. Does somebody have any idea on how can I get this working?
Life is not short... the problem is only how you organize yourself
-
bcp is not adequate for that, you may need to use textcopy utility. e.g. textcopy /S server /U username /P password /T table /D databasename /C Image /W "WHERE ID=1" /O /F C:\Temp\myImage.jpg
Hi... tanks a lot for your answer... The only problem is that Textcopy is not supported in sql server 2005 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=708866&SiteID=1[^] It was a sql 2000 utility... and I cannot go to all my clients telling that they have to copy Textcopy.exe on their server #@|°@#@¬@#°§@ Microsoft :-S
Life is not short... the problem is only how you organize yourself
-
Hi... I have a table with a column containing images. I'd like that my sql server 2005 stores those images on his backup device. I tried with the following command EXEC xp_cmdshell 'bcp "SELECT Image FROM table WHERE ID=1" queryout "C:\Temp\myImage.jpg" -c -T' I get a file that seems a jpg image... but it's not... I think it's because bcp queryout works only with text data. Does somebody have any idea on how can I get this working?
Life is not short... the problem is only how you organize yourself
hi, search here in codeproject, there´s a lot of code samples on storing images in database. :wtf:
nelsonpaixao@yahoo.com.br trying to help & get help
-
Hi... I have a table with a column containing images. I'd like that my sql server 2005 stores those images on his backup device. I tried with the following command EXEC xp_cmdshell 'bcp "SELECT Image FROM table WHERE ID=1" queryout "C:\Temp\myImage.jpg" -c -T' I get a file that seems a jpg image... but it's not... I think it's because bcp queryout works only with text data. Does somebody have any idea on how can I get this working?
Life is not short... the problem is only how you organize yourself
Wow - I would not have even considered that BCP could extract binary data. I'm pretty sure this is well outside the capabilities of BCP (or bulkcopy for that matter). BTW what happens when you write a datatable to xml if there is an image field in the table? Why would you use BCP for backup of a database. I would have thought standard or compressed (Red-Gate) backup would be a better solution.
Never underestimate the power of human stupidity RAH
-
hi, search here in codeproject, there´s a lot of code samples on storing images in database. :wtf:
nelsonpaixao@yahoo.com.br trying to help & get help
I don't wanna store images in database... that's already done and it's easy stuff! What I wanna do is to have the database exporting a picture from a table to a folder of the server (i.e c:\temp\myimage.jpg) I've googled around and found this textcopy... but as said in the previous post, it's not distributed by sql server 2005
Life is not short... the problem is only how you organize yourself
-
Hi... tanks a lot for your answer... The only problem is that Textcopy is not supported in sql server 2005 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=708866&SiteID=1[^] It was a sql 2000 utility... and I cannot go to all my clients telling that they have to copy Textcopy.exe on their server #@|°@#@¬@#°§@ Microsoft :-S
Life is not short... the problem is only how you organize yourself
In this case you could 1. either use SSIS's Export Column task to put the binary data into an image file (http://technet.microsoft.com/en-us/library/ms139818(SQL.90).aspx). 2. investigate the possibility of writing a little CLR to do that job. if neither is possible check this post which uses sp_OA* (http://www.sqlteam.com/forums/topic.asp?TOPIC\_ID=101754)
-
In this case you could 1. either use SSIS's Export Column task to put the binary data into an image file (http://technet.microsoft.com/en-us/library/ms139818(SQL.90).aspx). 2. investigate the possibility of writing a little CLR to do that job. if neither is possible check this post which uses sp_OA* (http://www.sqlteam.com/forums/topic.asp?TOPIC\_ID=101754)
Hi... Tank you for your answers... I tried another solution: with bcp I temporarily store a vbscript file where I have the code to connect to the database, select the image and store it in the file I want. I then call it using the following command
EXEC xp_cmdshell 'cscript "C:\temp\StoreImage.vbs"'It works very well with SQL Server 2005 in any configuration. But when I try with a SQL Server 2005 Express Edition, when I use trusted connection, the script fails getting an "Access denied" error. Should I change something in the rights of the sqlserver account? but what?Life is not short... the problem is only how you organize yourself
-
Hi... Tank you for your answers... I tried another solution: with bcp I temporarily store a vbscript file where I have the code to connect to the database, select the image and store it in the file I want. I then call it using the following command
EXEC xp_cmdshell 'cscript "C:\temp\StoreImage.vbs"'It works very well with SQL Server 2005 in any configuration. But when I try with a SQL Server 2005 Express Edition, when I use trusted connection, the script fails getting an "Access denied" error. Should I change something in the rights of the sqlserver account? but what?Life is not short... the problem is only how you organize yourself
Found my own response: USE[DataBase] GRANT CONNECT TO [guest] GRANT SELECT TO [guest]
Life is not short... the problem is only how you organize yourself
-
I don't wanna store images in database... that's already done and it's easy stuff! What I wanna do is to have the database exporting a picture from a table to a folder of the server (i.e c:\temp\myimage.jpg) I've googled around and found this textcopy... but as said in the previous post, it's not distributed by sql server 2005
Life is not short... the problem is only how you organize yourself
Hey joZ, how come??? :doh: i store images in database (from pictureboxes) and i load then into pictoreboxes!!! that´s easy stuff to!!! Doesn´t it works the same way in webforms? What i have here is a byte[], i post it if you want, i grabbed that here, did my changes too. Are you sure that, you dont have already the code for that?!:confused: i think so.
nelsonpaixao@yahoo.com.br trying to help & get help
-
Hey joZ, how come??? :doh: i store images in database (from pictureboxes) and i load then into pictoreboxes!!! that´s easy stuff to!!! Doesn´t it works the same way in webforms? What i have here is a byte[], i post it if you want, i grabbed that here, did my changes too. Are you sure that, you dont have already the code for that?!:confused: i think so.
nelsonpaixao@yahoo.com.br trying to help & get help
It's not this the problem... I have a windows application, where I put and receive images in pictureboxes from an sql server (wich naturally can be located on another pc). Now I have the need that this server take the pictures from the table and put them in his c:\temp ... and this with bcp does not work...
Life is not short... the problem is only how you organize yourself