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;
So what were the results of your testing?
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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.