April 14, 2014 at 7:14 pm
Hi All,
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected.
The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
@OpPath VARCHAR(MAX),
@ObjectToken INT
select @File = BinFile FROM Test WHERE RID = 2106469733
SET @OpPath = 'C:\Users\BinaryFile.bin'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @File
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @OpPath, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
GO
Any help would be appreciated.
Thanks.
April 14, 2014 at 8:08 pm
Confusing Queries (4/14/2014)
Hi All,I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected.
The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
@OpPath VARCHAR(MAX),
@ObjectToken INT
select @File = BinFile FROM Test WHERE RID = 2106469733
SET @OpPath = 'C:\Users\BinaryFile.bin'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @File
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @OpPath, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
GO
Any help would be appreciated.
Thanks.
There's no loop in that. It run's once and that's it.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2014 at 9:09 pm
Jeff Moden (4/14/2014)
There's no loop in that. It run's once and that's it.
I know that it runs once but, once it runs it should dump file into the folder that is been specified, but it is not dumping any file. Is there any mistake in the query?
April 22, 2014 at 2:45 pm
This worked for me. I did change 'SP_CONFIGURE 'Ole Automation Procedures', 1' to 'EXEC SP_CONFIGURE 'Ole Automation Procedures', 1' and changed the SELECT statement to 'select top 1 @File = BinFile FROM Test'. I am running @@Version SQL 2008 R2.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply