11-08-2017 07:47 AM - edited 11-08-2017 08:13 AM
Can anyone help me with the syntax to execute a simple stored procedure within an EG process flow?
I've seen a guide here - https://amadeus.co.uk/sas-tips/executing-sql-stored-procedures-using-proc-sql/ but would rather not include the log in details in the code.
I can run procsql using syntax like:
create table SqlserverLibrary.table as
select * from desktop1.all;
But I can't seem to execute a simple stored procedure in the same way. There are no parameters, the procedure simply drops some temp tables and recreates them with updated versions.
Any help appreciated
11-08-2017 08:26 AM
You need to do it in an explicit SQL pass through, by using either select * from connection to...() or execute () by...
There have been several posts about this on the communities, your should be able to find it by doing a serach.
11-08-2017 08:40 AM
I always try searching before posting but didn't spot anything useful, guess I wasn't using right terms so will give it another go using your hints.
I've gotten it working now using :
PROC SQL; Connect to odbc (datasrc=dbdsn user = user password = password); Execute(db.stored_proc) by odbc; Disconnect from odbc; QUIT;
but I really don't like having the username and password in plain text
however I've managed to find
which gets around the issue
(I'd still prefer to use the server authentication as I do when connecting to a library though)
11-10-2017 09:21 AM
I am using the below code to execute a stored procedur but for the chanegs to show in the databse dont i need to commit them? if so can you do that from SAS?
connect to oracle (user='username' password=password path="path" );
execute (stored_proc) by oracle;
disconnect from oracle;
11-10-2017 01:38 PM
If your not using transactions you shouldn't need to commit (although may depend on settings)