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
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.
/*PROC SQL calling a SPROC */
CONNECT TO OLEDB as DataReq (properties=
Create table taskreqst as
Select * from Connection to DataReq
(dbo.GetDataRequest @pModelName ='MV101');
DISCONNECT FROM Datareq;
proc print data=taskreqst;
/*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
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;
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
12840 proc print data=taskreqst;
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