BookmarkSubscribeRSS Feed
RC_BLAZER
Calcite | Level 5

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.

2 REPLIES 2
SASKiwi
PROC Star

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. 

Patrick
Opal | Level 21

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.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1165 views
  • 0 likes
  • 3 in conversation