BookmarkSubscribeRSS Feed
kjarvis
Fluorite | Level 6

Hello,

 

I have a DI Studio job with a User Written transformation where I'm calling a SQL stored procedure. I need to pass today's date to the stored procedure. My code is giving me this error:

 

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'convert'.

 

Here is the code:

 

proc sql;

connect to odbc ("dsn=SQL server Xxx; Trusted_Connection=yes; database=Xxxxxx");

execute (CCR_Rpt.spUpdate_Pers_Pref_Report @RunDate = convert(date, getdate())) by odbc;

 

What do I need to do to correctly pass today's date to the stored procedure?

 

Thank you for your help!

 

DI Studio 4.9

 

 

2 REPLIES 2
LinusH
Tourmaline | Level 20
Not an SQL Server specialist, but if you are letting the stored procedure fix the date you'll probably get more accurate help in an SQL Server forum.
Have you successfully called it outside SAS?
Data never sleeps
kjarvis
Fluorite | Level 6

Linus - I have called several SPs via SAS successfully. This is the first time trying to use today's date for the parameter.

 

Thank you

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
  • 932 views
  • 0 likes
  • 2 in conversation