Date:         Mon, 19 May 2008 17:09:45 -0400
Reply-To:     Santhosh Shanmugam <sshanm@EXPEDIA.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Santhosh Shanmugam <sshanm@EXPEDIA.COM>
Subject:      Using Proc SQL to Execute SPROC

Hi,

I am trying to call a SQL Server SPROC using Proc SQL pass-through query.
SAS is supposed to call the SPROC only once, but the SQL server tracer
shows two consecutive SPROC calls in rapid succession, Which causes an
error because the SQL sproc is designed to NOT execute the same instance
of a SPROC that is already active and currently running for a given input
parameter. Can someone give me suggestions that I could try and see where
I might be wrong. The SAS snippet and log is attached.

Thanks
Santhosh



/*PROC SQL calling a SPROC */
     PROC SQL;
                      CONNECT TO OLEDB as DataReq (properties=
                ("data
source"=sandbox.lab.net
    "initial catalog"=SASSandbox
                          "user id"=sas
                          "password"='sas') provider=sqloledb);
                          Create table taskreqst as
                          Select * from Connection to DataReq
                          (dbo.GetDataRequest @pModelName ='MV101');
                      DISCONNECT FROM Datareq;
                      QUIT;
RUN;


proc print data=taskreqst;
run;


/*QUERY1: Log for Hard Coding Macro Variables */

12827  PROC SQL;
12828                        CONNECT TO OLEDB as DataReq (properties=
12829                          ("data source"=sandbox.lab.net
12830                            "initial catalog"=SASSandbox
12831                            "user id"=sas
12832                            "password"='sas') provider=sqloledb);
12833                            Create table taskreqst as
12834                            Select * from Connection to DataReq
12835                            (dbo.GetDataRequest @pModelName ='MV101');
NOTE: Compression was disabled for data set WORK.TASKREQST because
compression overhead would increase the size of the data
      set.
ERROR: Open cursor error: ICommand::Execute failed. : SP: GetDataRequest.
Unexpected error: 200101-Create Logging
       Batch: SP: LoggingBatchAdd_. Duplicate record for table:
LoggingBatch; key: ModelMetadataID = 1. A batch for this
       model is already in progress.
NOTE: Table WORK.TASKREQST created, with 0 rows and 1 columns.

12836                        DISCONNECT FROM Datareq;
12837                        QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      cpu time            0.03 seconds

12838  RUN;

12839
12840  proc print data=taskreqst;
12841  run;

NOTE: No observations in data set WORK.TASKREQST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds