We are migrating ASP code that used ADO to connect to SQL Server 2000. For the most part the code migrated without a hitch to SQL Server 2008 after defining the connection, but one type of query is throwing an error that puzzles me.
Against SQL 2000, we would use code like this:
set oCommand = Server.CreateObject("ADODB.Command")
oCommand.ActiveConnection = oConn 'already established
oCommand.CommandType = adCmdText
'This is simplified over the real code, but the wrapper is what we use.
oCommand.CommandText = "<Root xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>"
+ Server.HTMLEncode(sql)
+ "</sql:query></Root>"
oCommand.Dialect = "{5D531CB2-E6ED-11D2-B252-00C04F681B71}" 'SQL Dialect GUID
oCommand.Properties("Output Stream").Value = oXMLDoc 'Already created
oCommand.Execute , , adExecuteStream 'The point of error
The SQL is a stored procedure call that works when called normally. This code continues to work against SQL 2000, but against SQL 2008 it returns:
ERR_OPENSQLXML XMLDB.openSQLXML failed. Error '-2147217898': 'Command dialect is not supported by this provider.'
My first guess is that SQL 2008 relies on MSXML version 6, and I need a new guid. My MSDN and Google-Fu has failed me: all the results I find use this GUID.
Is there a new GUID? Is there a better way? Is that better way less hassle than recoding in ASP.NET (the fate of many pages already).
EDIT: I'm wondering if using the SQLNCLI10 client is preventing this from working?
configConnString =
"Provider=SQLNCLI10;DataTypeCompatibility=80;Server=XYZZY;Database=ucpm;MARS Connection=True;Trusted_Connection=Yes;"
Rather than focus on the language which i haven't seen documented as changed - ASP.dll is still supported in the next version of windows server from the last check i made.
Can you check the client you are connecting from has the MSSQL Native Client 10 installed rather than the old sql 2000 odbc/oledb clients.