Stored Procedure with Dynamic Sql

Reply
Super Contributor
Posts: 418

Stored Procedure with Dynamic Sql

Hello everyone. I have run into an interesting problem running stored procedures from within SAS, and I do not know where to go from this point.

I know that you can use the sql pass through facility to run stored procedures within SAS. I regurally run several stored procedures using this method, including ones that have input paramaters. I will give an example of code that works to run these stored procedures below.

The issue is: I have a stored procedure that does run in SQL server 2005 (and 2008), however when running the stored procedure through the sql pass through facility, the stored procedure does not appear to run. Sas throws no errors, and it waits for about 5 seconds while running the proc sql before the operation says it is finished. However this stored procedure should take approximately 40-60 minutes.

The only difference between this stored procedure and others is that this procedure contains a significant amount of Dynamic SQL. Does SAS have a problem running stored procedures that store dynamic sql, maybe for security reasons?

 

proc sql;

connect to odbc(dsn='MY_DSN');

execute (Execute MyStoredProc 'Input1' , 'Input 2) by odbc;

disconnect from odbc;

quit;

run;

If anyone has any ideas on why one given stored procedure would work while another would not, please let me know!

Thanks!

Brandon

Super User
Posts: 5,260

Re: Stored Procedure with Dynamic Sql

I don't think SAS itself have restrictions on what is going on within an execute block, so if your user has the appropriate authorities in SQL Server, that shouldn't be a problem.

My experience is that it can be hard to log whatever is going on using execute. Have you looked at SQLRC, SQLXRC and SQLXMSG macro variables?

By your description, it sounds like that there could be some kind of time out issue. No idea how to track that. Maybe you should open a track to SAS tech support on this.

Data never sleeps
Super Contributor
Posts: 418

Re: Stored Procedure with Dynamic Sql

I was also under the impression that SAS did not have any restrictions on an execute block.

I have not looked into SQLRC, SQLXRC, etc... so I will spend some time and look into them now.

As for the time out issue, the thing that concerns me is that the program always run within 5 seconds, so its almost like the program just isn't running at all. I wonder why that would be.

Thanks for your response!

Ask a Question
Discussion stats
  • 2 replies
  • 231 views
  • 0 likes
  • 2 in conversation