OleDB not returning a empty cursor.

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.
The 2003 box has MDAC 2.8 RTM, while the rest of the boxes have MDAC
2.7x.
Thanks,
Subra
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
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
http://www.microsoft.com/sql/techinf...2000/books.asp
|||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.
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9599EFD3E715FYazorman@.127.0.0.1>...
> Subra (subramanyan.ramanathan@.gmail.com) writes:
> 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?
|||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
you scenario, without knowing what you are doing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||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...b;en-us;235340
Thank you for your assistance.
Regards,
Subra.
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959CD7F71B5EFYazorman@.127.0.0.1>...
> Subra (subramanyan.ramanathan@.gmail.com) writes:
> 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.
|||Subra (subramanyan.ramanathan@.gmail.com) writes:
> 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...b;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
http://www.microsoft.com/sql/techinf...2000/books.asp