2

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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mienio
  • 99
  • 2
  • 6
  • 1
    To enable the automation procs you need to run a reconfigure first: sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘Ole Automation Procedures’, 1; GO RECONFIGURE; GO – agrath Sep 03 '15 at 23:19
  • 1
    And if you create the destination folder on the server with EXECUTE sys.xp_create_subdir @Path; -- make directory then it will have the correct permissions for the ADODB.Stream to write to it – agrath Sep 03 '15 at 23:25

1 Answers1

1

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.

Paolo
  • 2,224
  • 1
  • 15
  • 19
  • Than You for Your answer and clue :) I finally managed to save it programmatically instead of "pure" querry – Mienio Sep 23 '14 at 16:40