1

I have a stored procedure that I often execute within SQL Server. Without copying hundreds of lines of code into here, it basically does the following:

  • You enter a database and table name as parameters.
  • The procedure then calculates summary statistics on all fields in the table (average, sum etc.) and inserts them into a temporary table.
  • The summary statistics are then inserted into an existing meta table.
  • The temporary table of stats is then dropped.

The procedure itself works perfectly when executing through SQL Server.

However, executing the same procedure with the same parameters through SAS code (via WPS) does not return the same results. It kicks off the procedure and calculates the statistics, but then fails to insert the statistics into the existing table and fails to drop the temporary table. Despite this, no errors are returned in the SAS log. The SAS code to execute the procedure is here:

proc sql;
   connect to odbcold(required="dsn=&SQLServer; database=_Repository;");
   execute (SP_Meta_Stats
              @DATABASE = &vintage.,
              @TABLE_NAME = &table_name.) by odbcold;
disconnect from odbcold;
quit;

I can assure you the macro variables have been setup correctly because the procedure is kicking off correctly with the correct parameters. It's just not completing the procedure as it does directly in SQL.

Are there are any known limitations to executing SQL stored procedures through a proc sql statement in SAS? Any known reasons why this not be computing the same as it does in SQL, despite just executing the same procedure?

EDIT: This seems to be some sort of connection issue. As if after a certain time lapses, WPS disconnects from the SQL connection. Because sometimes the temporary table has only computed stats for a handful of variables.

al_sweets
  • 136
  • 10
  • *"However, executing the same procedure with the same parameters through SAS code (via WPS) does not return the same results."* Then it isn't the same; that much is a fact. – Thom A Nov 18 '19 at 09:46
  • 1
    When executing through SQL Server (I assume you mean SSMS) you are probably connected using a login with the sysadmin role and have access to everything. Could it be that the login used with SAS has lesser privileges? – AlwaysLearning Nov 18 '19 at 10:01
  • Ask your SQL Server admin to trace the activity of your connection. Your login identity probably needs more grants. – Richard Nov 18 '19 at 14:56
  • You'll need to connect with WPS if the issue is happening with their product. – Reeza Nov 18 '19 at 17:22
  • @Larnu They are identical. I think this is a connection issue, that it's dropping half way through or something. – al_sweets Nov 18 '19 at 17:23
  • @Reeza Yep, think this is definitely a timeout issue with WPS. – al_sweets Nov 20 '19 at 14:40
  • @AlwaysLearning Good suggestion but this is being run by system admins on the SQL Server. Think this is a WPS timeout issue. – al_sweets Nov 20 '19 at 14:41

0 Answers0