April 18, 2008 at 1:30 pm
Hi Folks,
i use the table Person.address from adventureworks.
i add a column "Datei" with the datatype varbinary(max).
Now i add a file like this:
Update Person.Address
set datei = (Select * from Openrowset( Bulk 'C:\Test.doc', Single_Blob) as x)
where addressid = 2
My question is:
How can I receive the file from database to save it to localdisk; so that i have the file test.doc...
Something like select datei from person.address where addressid=1...
regards
martin
April 18, 2008 at 3:33 pm
If I'm not mistaken, openrowset can write to a file as well as read from it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2008 at 8:55 am
According to Books Online, openrowset cannot export -- BUT you can use the command line bcp:
bcp "select datei from Person.Address WHERE addressid=1 " queryout "c:\TestOut.doc" -T -n
I just tried this successfully, but I had to add the -S switch to specify server\instance as I have SS2000 and SS2005 installed.
April 21, 2008 at 12:55 pm
Hi,
i tried the following:
bcp "select datei from Person.Address WHERE
addressid=1 " queryout "c:\TestOut.doc" -T -n -Slocalhost\AdventureWorks
but i got the following error message:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL-Netzwerkschnittstellen: Fehler beim Su
chen des angegebenen Servers/der angegebenen Instanz [xFFFFFFFF].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Anmeldungstimeout abgelaufen
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]Fehler beim Herstellen einer Verbindung zu
m Server. Bei einer Verbindung zu SQL Server 2005 kann dieser Fehler dadurch ver
ursacht werden, dass SQL Server unter den Standardeinstellungen keine Remoteverb
indungen zulässt.
April 21, 2008 at 1:10 pm
slzbi (4/21/2008)
Hi,i tried the following:
bcp "select datei from Person.Address WHERE
addressid=1 " queryout "c:\TestOut.doc" -T -n -Slocalhost\AdventureWorks
but i got the following error message:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL-Netzwerkschnittstellen: Fehler beim Su
chen des angegebenen Servers/der angegebenen Instanz [xFFFFFFFF].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Anmeldungstimeout abgelaufen
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]Fehler beim Herstellen einer Verbindung zu
m Server. Bei einer Verbindung zu SQL Server 2005 kann dieser Fehler dadurch ver
ursacht werden, dass SQL Server unter den Standardeinstellungen keine Remoteverb
indungen zulässt.
Well, my knowledge of German is limited, but my guess is that you've got "-Sservername\databasename" where you should have "-Sservername\sqlserverinstance". If you just have one (default) instance of SQL Server 2005, then you don't need the -S option, OR you could add it like this: "-Slocalhost" and you don't need to specify instance.
April 21, 2008 at 1:25 pm
by using
bcp "select datei from Adventureworks.Person.Address WHERE
addressid=1 " queryout "c:\TestOut.doc" -T -n -Slocalhost
the file will be created. the file has the same filesize like the original.
But the content is very cryptical.
i can see the original content, but there are many signs like this:
f ÐÏ à¡± á > þÿ . 0 þÿÿÿ - ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿì¥Á }€ ð ¿
April 21, 2008 at 1:30 pm
by comparing the original doc-file with the created file from bcp,
ther are only 2signs differnet in the beginning of the hearer.
when i delete them the doc-file will be correct.
may i use some more parameters?
April 21, 2008 at 2:59 pm
slzbi (4/21/2008)
by comparing the original doc-file with the created file from bcp,ther are only 2signs differnet in the beginning of the hearer.
when i delete them the doc-file will be correct.
may i use some more parameters?
Yes, you have my permission to use more parameters, but only if you use them correctly! 😛
-n is for native format.. is this an MS Word document (.doc) ?
April 22, 2008 at 1:07 am
yes, it is a ms word document
i tried it with a jpg and it works
but with the word document, it didn't work
April 22, 2008 at 9:40 am
Yes, it looks like the Word documents are altered; I've tried it successfully with JPG and PDF files, but not Word.
April 22, 2008 at 10:18 am
Check out the snippet by Dale Joyce at this link:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1641735&SiteID=17
He's got an example using a .doc file with a format file.
April 23, 2008 at 7:42 am
Is this for a production application or is this a one-off operation?
I use VBScript to extract out BLOB values when necessary. It's clean and simple and easier than BCP. Sample script attached.
April 23, 2008 at 12:12 pm
its a one-off operation as the case maybe for testing;
so i prefer a solution in the managent studio;
i wrote an c# program that gives me the result, too.
but as mentioned before i was interested in a statement for the studio.
regards martin
June 24, 2008 at 11:42 am
I have same problem and the solution suggested by tung does not working (add some descriptors at beginning of the file, for JPG also)
1. create a format file "bulk_data.fmt" containing:
9.0
1
1 SQLBINARY 0 0 "" 1 data ""
2. use bcp -f option:
bcp "select datei from Person.Address where addressid=1 " queryout "c:\TestOut.doc" -T -f "bulk_data.fmt"
I was inspired by this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1758317&SiteID=1
It looks very strange to me that Microsoft does not provide a simple way to solve this problem... or may be we don't know this way
July 24, 2008 at 4:14 am
use a format file with this content:
9.0
1
1 SQLBINARY 0 0 "" 1 data ""
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply