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,
SubraSubra (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/techin.../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/techin.../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/defaul...kb;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/defaul...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
http://www.microsoft.com/sql/techin.../2000/books.asp
Showing posts with label cursor. Show all posts
Showing posts with label cursor. Show all posts
Tuesday, March 20, 2012
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
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
Monday, March 19, 2012
OLEDB Cursor Questions
Howdy folks! I have some questions on cursors.
I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:
Forward-Only:
Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.
This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?
MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?
If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?
Scrollable:
"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."
Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?
Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.
Thanks in advance!
The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.
The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.
|||Thanks Joao.
So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?
Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand. 
Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).
Both rowsets use forward-only cursors.
Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?
UPDATE:
I've since learned that a command can only have one rowset open at a time...thus the above is a moot point
OLEDB Cursor Questions
Howdy folks! I have some questions on cursors.
I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:
Forward-Only:
Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.
This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?
MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?
If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?
Scrollable:
"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."
Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?
Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.
Thanks in advance!
The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.
The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.
|||Thanks Joao.
So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?
Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.
Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).
Both rowsets use forward-only cursors.
Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?
UPDATE:
I've since learned that a command can only have one rowset open at a time...thus the above is a moot point
OLEDB Cursor Questions
Howdy folks! I have some questions on cursors.
I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:
Forward-Only:
Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.
This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?
MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?
If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?
Scrollable:
"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."
Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?
Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.
Thanks in advance!
The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.
The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.
|||Thanks Joao.
So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?
Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.
Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).
Both rowsets use forward-only cursors.
Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?
UPDATE:
I've since learned that a command can only have one rowset open at a time...thus the above is a moot point
Subscribe to:
Posts (Atom)