Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

When you use SAS/ACCESS Interface to OLE/DB to update Microsoft SQL
Server, Version 7 data through WHERE clause processing might result in
unreliable updates. The problem only occurs when using Microsoft SQL
Server with Service Pack 1.

The following code demonstrates how the problem could occur.
The code will not update consistently, it will not update where X=5 or
X=4, but will update for other X values:

libname test oledb provider=sqloledb properties=("user id"=xxxxx
    password=xxxxxx "data source"=xxxx "inital catalog"=oledb);
data test.up;
x=1; y='new table1';z=1;output;
x=2; y='new table2';z=2;output;
x=4; y='new table4';z=4;output;
x=5; y='new table5';z=5;output;
run;
proc sql;
  update test.up set y='updated' where x=4;
quit;

These update problems were introduced with Microsoft SQL Server 7
with Service Pack 1 (Version 7.00.699).

Be advised that basic WHERE clause processing for updates might be
unreliable when using this version of Microsoft SQL Server.

To circumvent the problem, upgrade to Microsoft SQL Server 7, with
Service Pack 2.  This version does not show the problem.  Details on
Service packs can be found from the Microsoft website, located at the
following url: http://www.microsoft.com/sql/support

Operating System and Release Information