| Groups | Blog | Home |
sql server odbc : OleDB not returning a empty cursor.
the application accessing the database are also on multiple Windows OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and Windows XP. The issue is only noticed when the database resides on windows 2003 box. The application uses a single connection to call 4 stored procedures sequentially. The first 3 stored procedures do not return any cursor (just parameter info). But the 4th stored proc is expected to return a cursor with 0 or more records. When this app is executed against a SQL server residing on a 2000 server, it returns a rowset with no rows but all the metadata information is available (cursor field names). But in case of Windows 2003 box, when the stored proc has no records to return (empty cursor), the rowset is set to nil. Basically as part of the OleDb interface I am calling ICommand HRESULT Execute ( IUnknown *pUnkOuter, REFIID riid, DBPARAMS *pParams, DBROWCOUNT *pcRowsAffected, IUnknown **ppRowset);\ In the 2000 server, the IUnknown is a pointer after the execution, but in 2003, the value is nil. Does anyone know if the SQLServer provider has been modified to return a nil in case of a empty cursor or is this a bug? Also if anyone knows of any work arounds or fixes, I would greatly appreciate it if you could share it with me. The 2003 box has MDAC 2.8 RTM, while the rest of the boxes have MDAC 2.7x. Thanks,
Subra (subramanyan.ramanathan@gmail.com) writes:
[quoted text, click to view] > I am using SQLServer 2000 on multiple Windows operating systems and > the application accessing the database are also on multiple Windows > OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and > Windows XP. The issue is only noticed when the database resides on > windows 2003 box. The application uses a single connection to call 4 > stored procedures sequentially. The first 3 stored procedures do not > return any cursor (just parameter info). But the 4th stored proc is > expected to return a cursor with 0 or more records. When this app is > executed against a SQL server residing on a 2000 server, it returns a > rowset with no rows but all the metadata information is available > (cursor field names). > > But in case of Windows 2003 box, when the stored proc has no records > to return (empty cursor), the rowset is set to nil. Basically as part > of the OleDb interface I am calling ICommand > HRESULT Execute ( > IUnknown *pUnkOuter, > REFIID riid, > DBPARAMS *pParams, > DBROWCOUNT *pcRowsAffected, > IUnknown **ppRowset);\ > > In the 2000 server, the IUnknown is a pointer after the execution, but > in 2003, the value is nil. Does anyone know if the SQLServer provider > has been modified to return a nil in case of a empty cursor or is this > a bug? Also if anyone knows of any work arounds or fixes, I would > greatly appreciate it if you could share it with me. It is not really clear to me. Do you get this problem when you connect to the SQL Server residing on the Windows 2003 box, no matter which operating system the client is on? If that is the case, I can't see that MDAC versions has anything to do with it, but there is something on the server, that is causing the NULL pointer. A few more questions: o If the stored procedure returns data, do you get a pointer in this case? o What is the return code of ICommand::Execute? o What do you pass for REFIID? o You talk about cursor. Is that a really true server-side cursor, or is it just a result set that you get back? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at
Thank you for the response.
Yes, the issue happens when I connect to a SQL server residing on a Windows 2003 box, regardless of the O/s the app is on (have tried running the app on Windows 2003 and Windows XP). o If the stored procedure returns data, do you get a pointer in this case? - Yes, I do get a pointer when a recordset is present. o What is the return code of ICommand::Execute? - The return code is 0, which means successful o What do you pass for REFIID? - IID_IUnknown: TGUID = '{00000000-0000-0000-C000-000000000046}'; o You talk about cursor. Is that a really true server-side cursor, or is it just a result set that you get back? - It is just a resultset. Thank you. Subra. [quoted text, click to view] Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns9599EFD3E715FYazorman@127.0.0.1>...
> Subra (subramanyan.ramanathan@gmail.com) writes: > > I am using SQLServer 2000 on multiple Windows operating systems and > > the application accessing the database are also on multiple Windows > > OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and > > Windows XP. The issue is only noticed when the database resides on > > windows 2003 box. The application uses a single connection to call 4 > > stored procedures sequentially. The first 3 stored procedures do not > > return any cursor (just parameter info). But the 4th stored proc is > > expected to return a cursor with 0 or more records. When this app is > > executed against a SQL server residing on a 2000 server, it returns a > > rowset with no rows but all the metadata information is available > > (cursor field names). > > > > But in case of Windows 2003 box, when the stored proc has no records > > to return (empty cursor), the rowset is set to nil. Basically as part > > of the OleDb interface I am calling ICommand > > HRESULT Execute ( > > IUnknown *pUnkOuter, > > REFIID riid, > > DBPARAMS *pParams, > > DBROWCOUNT *pcRowsAffected, > > IUnknown **ppRowset);\ > > > > In the 2000 server, the IUnknown is a pointer after the execution, but > > in 2003, the value is nil. Does anyone know if the SQLServer provider > > has been modified to return a nil in case of a empty cursor or is this > > a bug? Also if anyone knows of any work arounds or fixes, I would > > greatly appreciate it if you could share it with me. > > It is not really clear to me. Do you get this problem when you connect > to the SQL Server residing on the Windows 2003 box, no matter which > operating system the client is on? > > If that is the case, I can't see that MDAC versions has anything to > do with it, but there is something on the server, that is causing the > NULL pointer. > > A few more questions: > > o If the stored procedure returns data, do you get a pointer in this > case? > o What is the return code of ICommand::Execute? > o What do you pass for REFIID? > o You talk about cursor. Is that a really true server-side cursor, or
Subra (subramanyan.ramanathan@gmail.com) writes:
[quoted text, click to view] > Thank you for the response. > Yes, the issue happens when I connect to a SQL server residing on a > Windows 2003 box, regardless of the O/s the app is on (have tried > running the app on Windows 2003 and Windows XP). > o If the stored procedure returns data, do you get a pointer in this > case? - Yes, I do get a pointer when a recordset is present. > o What is the return code of ICommand::Execute? - The return code is > 0, which means successful > o What do you pass for REFIID? - IID_IUnknown: TGUID = > '{00000000-0000-0000-C000-000000000046}'; > o You talk about cursor. Is that a really true server-side cursor, > or > is it just a result set that you get back? - It is just a > resultset. What strikes me as odd is the use if IID_IUnknown. Normally you would use IID_IRowset or IID_IMultipleResults. I don't know if this has anything to do with it. But since the error is independent of which OS the client is on, I am more inclined to think that there is a difference between the code running on the two SQL Servers, or the their configuration. I can't see that the MDAC version should matter. Could you post the code for the stored procedure? Preferably take it from the Win2003 database. Also the C++ code from where you create the command up to the point of execution helps. There are a few variations with prepared statements, calling syntax etc. It is difficult to recreate you scenario, without knowing what you are doing. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at
Erland,
I managed to figure out the issue just recently. The reason for the issue is that in the stored proc that had the issue, I am using some temp tables to return data. Supposedly in the newer version of SQL Server OLEDB provider (SQLOLEDB) they return a result of each statement that gets executed in the stored proc. This and the combination of having temp tables in the stored proc results in some errors and hence no cursor is returned. So the recommended fix for this issue is to have the setting "SET NOCOUNT ON". I am attaching a link to the article on the microsoft website. http://support.microsoft.com/default.aspx?scid=kb;en-us;235340 Thank you for your assistance. Regards, Subra. [quoted text, click to view] Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns959CD7F71B5EFYazorman@127.0.0.1>...
> Subra (subramanyan.ramanathan@gmail.com) writes: > > Thank you for the response. > > Yes, the issue happens when I connect to a SQL server residing on a > > Windows 2003 box, regardless of the O/s the app is on (have tried > > running the app on Windows 2003 and Windows XP). > > o If the stored procedure returns data, do you get a pointer in this > > case? - Yes, I do get a pointer when a recordset is present. > > o What is the return code of ICommand::Execute? - The return code is > > 0, which means successful > > o What do you pass for REFIID? - IID_IUnknown: TGUID = > > '{00000000-0000-0000-C000-000000000046}'; > > o You talk about cursor. Is that a really true server-side cursor, > > or > > is it just a result set that you get back? - It is just a > > resultset. > > What strikes me as odd is the use if IID_IUnknown. Normally you would > use IID_IRowset or IID_IMultipleResults. I don't know if this has anything > to do with it. > > But since the error is independent of which OS the client is on, I am > more inclined to think that there is a difference between the code > running on the two SQL Servers, or the their configuration. I can't > see that the MDAC version should matter. > > Could you post the code for the stored procedure? Preferably take it from > the Win2003 database. Also the C++ code from where you create the > command up to the point of execution helps. There are a few variations > with prepared statements, calling syntax etc. It is difficult to recreate
Subra (subramanyan.ramanathan@gmail.com) writes:
[quoted text, click to view] > I managed to figure out the issue just recently. The reason for the > issue is that in the stored proc that had the issue, I am using some > temp tables to return data. Supposedly in the newer version of SQL > Server OLEDB provider (SQLOLEDB) they return a result of each > statement that gets executed in the stored proc. This and the > combination of having temp tables in the stored proc results in some > errors and hence no cursor is returned. So the recommended fix for > this issue is to have the setting "SET NOCOUNT ON". I am attaching a > link to the article on the microsoft website. > http://support.microsoft.com/default.aspx?scid=kb;en-us;235340 Glad to hear that you were able to resovle the issue! There is not really any change in the basic behaviour. By default SQL Server returns a "rows affected" message for each INSERT, DELETE and UPDATE statement. With most client libraries you get this as count without a result set. If your code does not handle this, and only looks for the first result set, all you get is the first "rows affected" message, but no rowset pointer or the equivalent. In many cases, these rowcounts are of little interest, so submitting SET NOCOUNT ON, kills two birds with two stones: you get the data you are looking for in the first result set, and you improve performance, since you reduce network traffic. My recommendation, though, is to use IMultipleResults and get all result sets, anyway. This makes the code more robust, not the least with regards to catching errors and PRINT messages. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |