I am attempting to change an ASP application we've got to use SQL Native Client instead of MDAC and running into a problem.
If I use ODBC driver our application runs without problem, but if I try to use the OLEDB provider certain pages are throwing the following error: Item cannot be found in the collection corresponding to the requested name or ordinal.
The pages that are not working are using ADODB.Command object to execute a Stored Procedure and populate ADODB.Recordset object. The error is thrown when I try to access a field (by name or index) in the recordset. I am using recordsetname(0) or recordsetname("fieldname") to access the data in the recordset.
I have other pages that are working that use this same technique, so I must be missing something else that is different about the two ASP's.
Has anyone else run into this problem switching to the OLEDB provider?
Thanks,
Kelsey
Are you accessing columns in any of the new SQL Server 2005 data types? With ADO you must use DataTypeCompatibility=80 in your connection string. For example
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(local);" _
& "Database=AdventureWorks;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
See http://msdn2.microsoft.com/en-us/library/ms130978.aspx for more details about using SQL Native Client from ADO applications
If this doesn't help please send more details (preferably source code)
|||
Hi Chris -
We are not accessing any of the new SQL Server 2005 data types yet. We do have DataTypeCompatibility=80 in the connection string. Here is an example of the code that produces the error. "oursp" is a stored procedure that has "url" as a field in the resultset. When I run this page I get the "item can not be found in collection" error. If I change this code to use "Driver={SQL Native Client}" instead of the "Provider=SQLNCLI;" it works without any problem.
Any ideas?
Thanks,
Kelsey
--
<%
'Dim some local variables.
Dim lid, llist, rsGen, Cn, p, cmd
'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")
'Set local variables for Stored Procedure Call
lid = 3
llist = "36,37,38"
'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")
'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250
'Open connection with SQL Native Client Provider
Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=ourserver;"_
&"Database=ourdb;"_
&"UID=ouruser;"_
&"PWD=ourpass;"
'Create Command Object
set cmd = server.CreateObject("ADODB.Command")
'Set active connection
cmd.activeconnection = Cn
'Set command text to Stored Procedure name
'and command type to stored procedure
cmd.Commandtext = "oursp"
cmd.Commandtype = 4
'Create Stored Procedure parameters
set p = cmd.Parameters
p.Append cmd.CreateParameter("@.id", 3, 1)
p.Append cmd.CreateParameter("@.list", 200, 1, 255)
cmd("@.id") = lid
cmd("@.list") = llist
'Create the recordset with results of Stored Procedure call
set rsGen = cmd.Execute
'Trap and display any errors thrown when
'trying to access field in recordset
ON ERROR RESUME NEXT
response.write rsGen("url")
If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If
cn.Close
Set cn = Nothing
%>
So this works when you use SQL Native Client ODBC via Kagera (aka MSDASQL the OLE DB to OBC bride which is now deprecated) - which isn't a supported scenario (see http://msdn2.microsoft.com/en-us/library/ms131035.aspx) but not when you use SQL Native Client OLE DB directly (which is supported).
The next step for me is to know if MDAC OLE DB worked as you expected or if this has only ever worked via Kagera.
One possibility is that your stored procedure is generating multiple results and this is handled differently in the two cases. If this is so, then executing
set rsGen = rsGen.NextRecordset
until you settle on the right result set would solve the problem
It might also be that for some reason no rows are being returned, so a check for BOF to detect this would help.
If you call the stored proc from SQL Management Studio you should be able to see if multiple results are being returned.
I don't think I can get much further without knowing if SQLOLEDB works OK, knowing that a row is definitely being returned and knowing more about what the stored procedure does.
|||
You are correct... there was multiple resultsets coming back from the Stored Procedure call. Doing the rsGen.NextRecordset made the error go away. When I execute the Stored Procedure in SMS it looks like a count is being returned first, then the actual result set. If I set NOCOUNT on in SMS it goes away. Is there a way I can set NOCOUNT on in my connection string, or is this a database setting I need to change?
Thank you very much for the help.
Kelsey
|||Hi Kelsey,
When SQL Server executes a stored procedure it will send a row count to the client for each non-query statement executed. Setting NOCOUNT ON stop this.
Connection string keywords for SQL NAtive Client are here http://msdn2.microsoft.com/en-us/library/ms130822.aspx
You can't set NOCOUNT ON via the connection string, but you can execute the statement 'SET NOCOUNT ON' on the connection before calling the stored procedure. If you execute the SET statement in the stored procedure itself, its effect will be limited to execution of the stored procedure. See http://msdn2.microsoft.com/en-us/library/ms189837.aspx for reference
|||Hi Chris,
Thanks for the info. The only thing that is hanging me up now is the fact that we have 3000 stored procedures and many more ASP's calling them. The connection string is in an include, but I don't want to call "SET NOCOUNT ON" for each connection open (page load). I could probably justify spending the time required to go through and add it to each of our Stored Procedures if there was going to be performance gain and/or would enable us to use the latest technology available.
Do you know why SQL Native Client OLE DB treats the count as a result set when MDAC and even SQL Native Client ODBC do not? Is it possible that SQL Native Client OLE DB will be updated to function the same way in the future preventing me from needing to go through our Stored Procedures and adding "SET NOCOUNT ON"?
The main reason we are trying to upgrade is that we've noticed through Profiler Traces that using SQL Native Client OLE DB (or ODBC for that matter) eliminates a large number of sp_cursor type calls that are occuring when our application is using MDAC. With SQL Native Client we've noticed that these calls are handled with a single sp_executesql instead of the many sp_cursor calls.
Thanks again for your time and help.
Kelsey
|||Hi Kelsey,
I'd like to investigate this further, could you send me sample code that is representative of one of your stored procs that is giving problems and how it is called. If you can do this in VB6 that would be even better without being in an ASP page. If not just code fragments will be enough to get me started.
If you're not seeing sp_cursor calls where you were before, then this indicates that a default result set is being returned where previously a server cursor was used. This would be consistent with the stored proc not being eligable for use with a server cursor (to be used with a server cursor a proc has to be a single select statement or a call to another proc that is a single select statement). If a proc can't be used with a server cursor, then it may return one or more counts/default result sets.
I'd expect OLE DB in MDAC and SQL Native Client to behave consistently, and ODBC in MDAC and SQL Native Client to behave consistently with regard to handling of results. ODBC and OLE DB may not necessarily behave the same though. To get a complete picture I'll need to compare each API running the same proc on both SQL Server 2000 and SQL Server 2005. Are you comparing MDAC to SQL Native Client with the same version of SQL Server?
|||Chris,
It appears like the sp_cursor calls are not happening in places where we are executing Stored Procedures, but rather only in places where we are opening multiple AD-HOC Queries on the ASP. Here is a sample I have created that uses the PUBS DB. If run this page using old MDAC way the second AD-HOC query on the page is opened using a cursor. If I run this page using SQL Native Client (ODBC or OLEDB) it uses sp_executesql for the second AD-HOC instead of sp_cursoropen.
I have also noticed that if I change the first AD-HOC query to open the recordset using "rsGen.Open CMD.Execute" instead of "Set rsGen = cmd.Execute" the second AD-HOC will no longer be opened using sp_cursoropen.
I'm not sure if this behavior is directly related to the problem I am having with some Stored Procedures returning the count as a seperate resultset and others not, or if it is completely seperate.
I've got a large number of ASP's that have multiple AD-HOCs on them so I really would like to switch to SQL Native Client to prevent these cursor calls from happening.
Here is the sample code for recreating the multi AD-HOC problem. I am going to see if I can work up a seperate sample that shows the Stored Procedures that are returning count as a resultset and those that are not. I will post later on today.
Thanks for all the help.
Kelsey
--
<%
'Dim some local variables.
Dim rsGen, Cn, p, cmd
Const adCmdText = &H0001
Const adOpenForwardOnly = 0
Const adUseServer = 2
'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")
'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")
'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250
'--
'New SQL Native Client way of connecting
'--
'Open connection with SQL Native Client Provider
'Driver={SQL Native Client}
Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=SPILLWAY\DEV;"_
&"Database=pubs;"_
&"UID=test_user;"_
&"MARS Connection=False;"_
&"PWD=test;"
'--
'--
'Old MDAC Way of connecting
'using System DSN
'--
'Cn.Open "pubs", "test_user", "test"
'--
DIM lzip, lfname
lzip = 84152
lfname = "Gary"
'Create Command Object
set cmd = server.CreateObject("ADODB.Command")
'Set active connection
cmd.activeconnection = Cn
'
'Test AD HOC Query CAll #1
'
qryGen = "SELECT lname, fname FROM employee WHERE fname = ?"
cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lfname", 202, 1, 20)
cmd("lfname") = lfname
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
Set rsGen = cmd.Execute
'rsGen.Open cmd.Execute
response.write rsGen("lname")
rsGen.Close
'
'Test AD HOC Query Call #2
'
qryGen = "SELECT au_lname, au_fname FROM authors WHERE zip = ?"
set cmd = server.CreateObject("ADODB.Command")
cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lzip", 129, 1, 5)
cmd("lzip") = lzip
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
set rsGen = cmd.Execute
'
ON ERROR RESUME NEXT
if not rsGen.EOF then
response.write rsGen("au_lname")
end if
If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If
cn.Close
Set cn = Nothing
%>
Hi Kelsey,
In your include, where you configure the conection, you can add this:
oConn.Execute "Set NOCOUNT ON"
You don't need change all stored. In my case it works!
Regards
Jaime
PD: This post help me to be in the right way, thanks Chris and Kelsey
|||Hi Jamie,
I had tried this as well.
What I didn't like about this solution is that "SET NOCOUNT ON" is good for the entire DB Session, so it seemed like this would create a situation where it was being called more times then nessecary.
I am not sure exactly how the scope of a DB Session is determined, so it is possible that it doesn't last beyond the life of single page load anyway, but I wasn't sure.
Perhaps Chris could shed some more light on this.
I'm still trying to fight my way back to sample stored procedures... just haven't had the time yet.
Thanks,
Kelsey
SET NOCOUNT ON will last until the connection is closed, or you execute a SET NOCOUNT OFF
I tried your earlier sample code with MDAC and SQL Native Client against SQL Server 2000 and SQL Server 2005 using OLE DB and ODBC. MDAC + ODBC uses a server cursor for the second query, in all other cases you get a default result set. A default result set will generally give the best performance, so this is what you want in most cases. I don't see much point in exploring MDAC+ODBC further since OLE DB is the preferred choice for ADO (and ODBC+ADO isn't a supported scenario for SQL Native Client, since MSDASQL is now deprecated).
No comments:
Post a Comment