thankyou but I need varbinary or image ie file of any type not a text file...
Main Topics
Browse All TopicsHi
I need a stored procedure that I can pass an output filename and a blob parameter of VarBinary(max).
The stored procedure must then write the blob to a file as specified in filename.
CREATE PROCEDURE up_WriteToFileSystem(@myBl
AS BEGIN
-- Do Magic - write @myBlob to file system @OutFileName
END
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Varchar and Varbinary are the same except for how they pass through code page translation. I'm not sure that would even be a factor when writing to disk.
None the less, the OLE automation classes are very robust. I did a little more research and found an example that is intended for varbinary. For simplicity sake, there is no error handling in this example.
I'd suggest putting this code in a reusable proc that you can exec from your code. Ditto for the GET IMAGE that you will undoutedly want, as well.
Now, it's your turn to give it a try.
Hi
I have tried calling this (c# code below)
The stored procedure get scalled ok, there are no errors, but SQL server does not create he file?
CREATE PROCEDURE up_SaveBinaryToFile(@Binar
AS
BEGIN
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @Binary
SET @FileName = 'c:\temp\' + @FileName
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
END
Thanks it worked - C# should be usefull for someone trying the same thing.
By the way, if you wonder why I go to such lengths and why dont I just write the file from c# - its because the c# app does not not have access permissions to the filesystem where the file needs to be stored.
Big $'s ? Well I am open to offers ;)
brigzy@ntlworld.com
Cheers
Richard
Business Accounts
Answer for Membership
by: dqmqPosted on 2009-11-07 at 11:45:57ID: 25767730
SQL 2008 has a FILESTREAM object. For 2005 (and earlier???), you can code your own using the OLE automation objects:
http://support.microsoft.c
Attached is a sample procedure that you can use to output a varchar(max) to the external file system. Probably you can adapt it for varbinary.
Some people pay big $ for this stuff :>).
Select allOpen in new window