CODEWITHSUNDEEP
sql-serverimagepdfclientvarbinary
Mienio
Mienio

Reputation: 99

SQL Server : exporting varbinary PDF to file on client

I'm using SQL client connected to a SQL Server, and after executing following code, a .pdf file is saved on server HDD not on mine (client).

Is there a way to export file to my local harddisk using a query?

DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT CONVERT(varbinary(max), pdfcolumn, 1) 
                     FROM Scans 
                     WHERE id = 1);

DECLARE @Path nvarchar(1024);
SELECT @Path = 'c:\scans';

DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = 'scan.pdf';

DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Filename;

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, @ImageData;
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
    EXEC sp_OAMethod @ObjectToken, 'Close';
    EXEC sp_OADestroy @ObjectToken;

Upvotes: 2

Views: 5919

Answers (1)

Paolo
Paolo

Reputation: 2254

that's correct and expected: commands are executed by the server on the server under a dedicated account with limited access to external resources.

the technical requirement mandatory to save a file on your (client) hdd is that the user account used by sql server process must have access to that remote disk (and that is not good); you must also specify the destination folder as a UNC path and not as a local path.

if you have a dba between you and the rdbms that person will be very unhappy should you ask to enable such a behaviour.

the required steps:
- run the server process under a user account that can access the resources on server and on remote client changing the service configuration
- set the @Path variable with the UNC path of the destination folder

let me stress once again: this is bad and you do not want to do that in a production environment.

Upvotes: 1

Related Questions