遇到了SQL 2005与2000的兼容性问题

Question

各位：
我们软件是通过ADO的方式连接数据库，以前使用SQL SERVER 2000，一切都好好的。现有个客户要求使用SQL 2005，目前已发现若干兼容性问题：
1、LEFT JOIN不再支持，必须使用LEFT OUTER JOIN
   例如，这样的SQL语句是通不过的，报错“数据提供者或其他服务返回 E_FAIL 状态”：
   Select a.*,b.ChartType from ReportList a LEFT JOIN ReportChart b on ...
   必须这样写： 
   Select a.*,b.ChartType from ReportList a LEFT OUTER JOIN ReportChart b on ...
2、嵌套查询的别名容易冲突
   例如，这样的SQL语句是通不过的，报错“数据提供者或其他服务返回 E_FAIL 状态”：
   select a.Name from (select b.Name from UserRole as a LEFT OUTER JOIN [User] as b on ...) as a LEFT OUTER JOIN Option217 as b on ...
   因为子查询中已经用到了别名a，外面就不能再使用a。必须这样写： 
   select c.Name from (select b.Name from UserRole as a LEFT OUTER JOIN [User] as b on ...) as c LEFT OUTER JOIN Option217 as d on ...   我已经在SQL Server Management studio中将该数据库的兼容级别设置成80（即兼容SQL Server 2000），但还是不行。程序非常大，难道需要调整所有的sql语句的写法吗？还有哪些不兼容的地方？有谁知道吗？   谢谢！

Answer 1

不是安装的问题，已经试过3台机器了，客户的一台，我们的2台，都出这样的错。这个不兼容性在SQL Server 2005的Visual Studio下也能看出来，在那里，运行旧查询语句，Visual Studio会将SQL语句自动转换成新的，即Left Join自动换成了Left Outer Join ... 等，所以也就能正常执行了。但通过 ADO 时，没有机制做这样的转换，自然就出错了。现在的问题是，既然已经将数据库的兼容级别设置成了80（即兼容SQL Server 2000），怎么还是不行呢？

Answer 2

照理说指定兼容级别为80的话，就不应该出现SQL语法兼容问题的。LZ可以考虑一下是不是驱动程序（比如ODBC驱动？）的问题？
要确保是能够与2005工作的驱动。

Answer 3

同一台机XP系统\2003系统都装过
VS2003\VS2005\SQL2000开发版\SQL2005开发版..4套软件没遇到过楼主的情况

Answer 4

w2jc：我估计你说到问题所在了，我现在的数据连接的写法是：
PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=myServer;DATABASE=myDB;UID=myUID;PWD=myPWD;我看了一下ODBC Driver for SQL SERVER所用的版本为2000.80，是不是版本太低？从哪里可以获得高版本的ODBC Driver for SQL SERVER？

Answer 5

经过反复试验，问题终于解决，总结出来，给大家分享：'连接方式：SQL Server ODBC Driver,
'连接字串：ConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=" & DBServerName & ";DATABASE=" & DATABASE_NAME & ";UID=" & DBUID & ";PWD=" & DBPWD & ";"
'结果：某些SQL语句等不支持，如：LEFT JOIN不支持, 嵌套子查询的别名冲突。报错“数据提供者或其他服务返回 E_FAIL 状态。”
    
'连接方式：SQL Server OLEDB Provider
'连接字串：ConnStr = "PROVIDER=SQLOLEDB.1;Data Source=" & DBServerName & ";Initial Catalog=" & DATABASE_NAME & ";User ID=" & DBUID & ";Password=" & DBPWD & ";"
'结果：连接不上，报错“SQL Server 不存在或访问被拒绝”'连接方式：SQL Native Client ODBC Driver
'连接字串：ConnStr = "DRIVER={SQL Native Client};SERVER=" & DBServerName & ";DATABASE=" & DATABASE_NAME & ";UID=" & DBUID & ";PWD=" & DBPWD & ";"
'结果：执行UPDATE语句时，报错“在事务中，不能存在此光标类型的多记录集。或更改光标类型、提交事务，或关闭其中一记录集。”'连接方式：SQL Native Client OLEDB Provider
'连接字串：ConnStr = "PROVIDER=SQLNCLI;DataTypeCompatibility=80;SERVER=" & DBServerName & ";DATABASE=" & DATABASE_NAME & ";UID=" & DBUID & ";PWD=" & DBPWD & ";"
'结果：ok!
'例如：    ConnStr = "PROVIDER=SQLNCLI;DataTypeCompatibility=80;SERVER=(local);DATABASE=Wexcel;UID=sa;PWD=123456;"

Answer 6

我看了一下ODBC   Driver   for   SQL   SERVER所用的版本为2000.80，是不是版本太低？从哪里可以获得高版本的ODBC   Driver   for   SQL   SERVER？ 
--------------------------------------------
2000.80是SQL2000的ODBC版本。
安装SQL2005时，选择安装客户端组件就包括了最新的ODBC，
独立安装的SQL2005 ODBC组件倒是没有见过。