topic Re: how to insert a binary pdf into a sql database? in SAS Data Management

how to insert a binary pdf into a sql database?

RC_BLAZER — Tue, 02 May 2017 16:16:28 GMT

Hi,

I am trying to insert a pdf into a sql database but having some trouble.

The pdf is a report created in SAS 9.4 using ods.

I am connecting to the sql database using odbc connection. On the sql server there is a sql stored procedure that is expecting the pdf as a binary object. However, I don't know how to deliver a binary pdf in SAS.

Any suggestions? Or information I need to add to make my question more clear?

Here is the sas code I am using.

proc sql;
    connect to odbc ("driver={&driver};server=tcp:&server,1433;database=&database;uid=&userid@&server_name;pwd={&pwd};encrypt=yes;trustservercertificate=no;connection timeout=30;");
    execute (
        declare @result bit
        declare @statusmessage varchar(100)
        exec [dbo].[report_transfer_deliverfile]
                      1,
                      "&filepathf\test.pdf", --how do we pass binary input here?
                      'test.pdf',
        @result output,
        @statusmessage output
    ) by odbc ;
    %put &sqlxrc. &sqlxmsg.;
    disconnect from odbc;
quit;

I get the following error:

ERROR: CLI execute error: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'C:'.

I am connecting to database fine. And the sql stored process is running. But it needs the binary file of the pdf, not the file path I included.

Thanks for your help.

Re: how to insert a binary pdf into a sql database?

SASKiwi — Tue, 02 May 2017 23:42:37 GMT

This is really an SQL Server issue not a SAS problem. Where does SQL Server expect the PDF to be so it can be loaded? A quick Google search suggests a directory location can be included but it would need to be one accessible by SQL Server. Maybe you can FTP the PDF to the SQL Server load folder using SAS, then run your load step referencing the folder.

Re: how to insert a binary pdf into a sql database?

Patrick — Wed, 03 May 2017 00:00:27 GMT

Hi @RC_BLAZER

For debugging pass-through SQL I always try and make things work running code without any SAS involvement (=using some database client like SQL Server Management Studio to connect and run code directly on the DB). Only once that works I paste the code back into a SAS SQL pass-through block.

As your SQL executes in-database I believe your PDF must reside in a location accessible to the DB and the path must be "as seen" by the database. You probably need to talk to your DBA or the person who implemented the stored procedure in order to make this work.