topic Re: SQL Pass Through Options in SAS Data Management

SQL Pass Through Options

DMD_23 — Fri, 21 Sep 2018 15:59:40 GMT

Hello,

I'm trying to find some options for a pass through query to SQL which would allow the table to still have new records appended while the query is running. Here's a version of what I've tried so far:

rsubmit;
proc sql;
%odbc_connect(alias=CRGMT,dsn=NeuAnalytics,user=&sqlpw.,password=&EIW_pwd.);
	/* Connection=sharedread ?? */
	execute(SET NOCOUNT ON     
			OPEN SYMMETRIC KEY AccountAudit_Key11     
			DECRYPTION BY CERTIFICATE AccountAudit09) by CRGMT;
	execute(SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) by CRGMT;
   	create table R1Remed as 
		select * from connection to CRGMT
   			(
			SELECT distinct
				WorkItemId
				, StartDate
				, QTemplateVersionWorkItemId
				, QTemplateInfoId
				, QTemplateVersionId
				, QuestionAnswerOptionId
				, AnswerOption
				, cast(AnswerText as varchar(1000)) as AnswerText
				, AccountImportDetailId
				,[FileName]
			FROM WorkItem.QTemplateVersionWorkItem TVWI
				LEFT JOIN workitem.[WorkItemQTemplateAnswer] WTA ON TVWI.QTemplateVersionWorkItemId = WTA.QTemplateVersionWorkItemId
			WHERE StartDate > '9/1/2016'
				AND StatusId <> 6 
			);
disconnect from CRGMT;
quit;
endrsubmit;

Re: SQL Pass Through Options

SASKiwi — Fri, 21 Sep 2018 22:39:31 GMT

So what were the results of your testing?

Re: SQL Pass Through Options

DMD_23 — Mon, 24 Sep 2018 12:54:17 GMT

When I used the options that ran on the server as part of my posted query, the SQL table was still locking when the query was running long. I'm wondering if there are some options I could put on the SAS side of the query that would work.

Re: SQL Pass Through Options

SASKiwi — Sun, 30 Sep 2018 00:46:08 GMT

I suggest you talk to the database DBA for advice. Possibly using views of the tables you are accessing may help.

I would also suggest checking forums for the database you are using since this isn't really a SAS question.