topic Re: SQL Server queries with 9.1 and 9.2 in SAS Programming

SQL Server queries with 9.1 and 9.2

anandbillava — Thu, 12 May 2011 23:11:33 GMT

I have one unusual situation where in my sql query works fine under 9.1. But under 9.2 the program never finishes execution. At the end I have to abruptly exit the program.
I am using Unix SAS. Let me know if any one has answer.

Here is my query

connect to sqlsvr(user=aaaa pwd='xxxxxxxxxxx' datasrc=pppp);
** All Edit checks **;
Execute(select dbo.fnlds(projectname, 'eng') as Project, CRFDrafts.ProjectId, CRFDrafts.CrfdraftId
into #tmpDrafts
from projects
inner join CRFdrafts on CrfDrafts.projectId = projects.projectid
) by sqlsvr;

create table mmnecdl2 as select *
from connection to sqlsvr
(
Select Draftchecks.CRFDraftId as CRFDraftId, ProjectId, Isvalid, Draftchecks.DraftCheckName as DraftCheckName, Project, created
from #tmpDrafts
inner Join Draftchecks on Draftchecks.crfdraftid = #tmpDrafts.crfdraftid
and isvalid = 1
and created >= "&startdt"

order by Project
);

Re: SQL Server queries with 9.1 and 9.2

Doc_Duke — Fri, 13 May 2011 00:20:30 GMT

I don't know the answer, but I have suspicions. Under Unix, SAS/Access to SQL/Server actually runs against an ODBC third-party add-on. Your support group may have changed the ODBC product that they are using in Unix. Are the 9.1 and 9.2 versions of SAS on the same physical box? There are a lot of moving parts to get from Unix to SQL/Server, so it could be something else has changed. From the 9.2 documentation,

"SAS/ACCESS Interface to Microsoft SQL Server has been tested and certified against Data Direct Technologies Connect ODBC and Data Direct SequeLink ODBC products"

It could also be that the new environment does not support all of the features that the old one did.

My first effort at debugging the problem would be to see if I could run any query against the database, as you are using fairly advanced features in the first query.

Doc Muhlbaier
Duke