topic Re: SAS query to SQL Server DB returns duplicate ID in SAS Data Management

SAS query to SQL Server DB returns duplicate ID

max00d — Tue, 07 Feb 2017 11:04:09 GMT

We have a number of SAS DI jobs that query MS SQL Server (2008) via OLE DB. I recently noticed that the result was returning duplicates from a field with a primary key contraint - so this shouldn't be possible.

On further inspection I can recreate the error from the ETL job that is returning two fields from the source table. However, these fields are being processed in SAS (number >> character and a data extraction) - so no 'raw' data is returned from the SQL query. It appears that the duplicate primary keys are coming through because the source table is being modified by the source system during the SAS query execution (as shown by a record change datetime field). I can recreate this error over and over again in a simple macro.

When I change the macro to first - Select * from sourceTable - and then perfrom the SAS conversions to return the two fields (see above) no duplicate primary keys come through.

So it would appear that running a SQL statement that does SAS specific conversions vs. running a SQL statement that (as far I know) uses SQL pass-thru - ends up returning duplicates even through there is a contraint on the source table. So SAS must not be locking the table (or getting a lock from the DMBS).

Is anyone able to point me at some technical details that cover this issue and why it occurs. The issue is a little perplexing in that I have had to investigate something that I've been told cannot occur. Furthermore, this now appears to be a SAS specific issue when we have blamed the source system (MS SQL) because of being told that SAS would get a lock on the table and the cause cannot be a SAS issue.

Hope this makes sense but happy to elaborate further if more information is needed.

Re: SAS query to SQL Server DB returns duplicate ID

Kurt_Bremser — Tue, 07 Feb 2017 11:57:04 GMT

We're using a completely different RDBMS (UDB or DB/2), but we run our extracts from snapshots, so we always get consistent states.

Freezing a snapshot is necessary to prevent not only inconsistencies in tables, but also between them. Consider running critical extracts during a time window where the DB is stopped, if you can't create snapshots.

Re: SAS query to SQL Server DB returns duplicate ID

LinusH — Tue, 07 Feb 2017 12:33:44 GMT

Agree that you have a basic integrity issue in your extract logic.
But regardless it sounds odd that a record will duplicate during a select because of update...Perhaps you should involve SAS tech support to dig into this...?

Re: SAS query to SQL Server DB returns duplicate ID

BrunoMueller — Tue, 07 Feb 2017 15:04:34 GMT

Just a very wild guess, have a look at READ_ISOLATION_LEVEL option http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0xth6czn6guxrn13z0b2vyer8dt.htm

Bruno