using the Sql Server 2000 odbc driver. I am getting an
error S1000: [Microsoft][ODBC SQL Server Driver]
Connection is busy with results for another hstmt.
When I look at the dsn settings, there is a check box to
turn off SQLPrepare. The problem is it is checked and the
box is greyed out and will not let me turn this off. Does
anyone know why this is like this, and how can I turn it
off?
Will I have to write my own driver?This has nothing to do with SQLPrepare. By default MS SQL Server and
Sybase allow only one statement per connection at a time. It requires
that you close one statement before you open another one. If you try to
open another statement, you get this error. You can change this default
behavior by using Server Side Cursors. Here is some sample code.
nResult = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
if(nResult != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO){
// Handle error
}
nResult = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)10, 0);
if(nResult != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO){
//Handle error
}
Hope this helps.
Pete.
anonymous@.discussions.microsoft.com wrote:
quote:
>I could really use some help, I have a system dsn and am
>using the Sql Server 2000 odbc driver. I am getting an
>error S1000: [Microsoft][ODBC SQL Server Driver]
>Connection is busy with results for another hstmt.
>When I look at the dsn settings, there is a check box to
>turn off SQLPrepare. The problem is it is checked and the
>box is greyed out and will not let me turn this off. Does
>anyone know why this is like this, and how can I turn it
>off?
>Will I have to write my own driver?
>
You don't pay to get spam, why pay to clean it?
Visit http://www.spammarshall.com to create an account for free
<http://www.spammarshall.com>|||This does not answer the question. I am not programming a
sql statement. I am setting up a DNS and I am unable to
uncheck the Prepare box when setting up the data source.
But thanks, anyway
quote:
>--Original Message--
>This has nothing to do with SQLPrepare. By default MS SQL
Server and
quote:
>Sybase allow only one statement per connection at a time.
It requires
quote:
>that you close one statement before you open another one.
If you try to
quote:
>open another statement, you get this error. You can
change this default
quote:
>behavior by using Server Side Cursors. Here is some
sample code.
quote:
>
> nResult = SQLSetStmtAttr(hstmt,
SQL_ATTR_CURSOR_TYPE,
quote:
ed">
>(SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
> if(nResult != SQL_SUCCESS && nResult !=
SQL_SUCCESS_WITH_INFO){
quote:
kred">
> // Handle error
> }
> nResult = SQLSetStmtAttr(hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
quote:
rkred">
>(SQLPOINTER)10, 0);
> if(nResult != SQL_SUCCESS && nResult !=
SQL_SUCCESS_WITH_INFO){
quote:
kred">
> //Handle error
> }
>Hope this helps.
>Pete.
>
>
>anonymous@.discussions.microsoft.com wrote:
>
the[QUOTE]
Does[QUOTE]
>--
>You don't pay to get spam, why pay to clean it?
>Visit http://www.spammarshall.com to create an account
for free
quote:|||Exactly what option are you referring to? There is an option with the text:
><http://www.spammarshall.com>
>
>
"Create temporary stored procedures for prepared SQL statements and drop the
stored procedures: ...". Why do you want to uncheck this option?
If you are not programming a SQL statement, then how are you getting that
error you reported? ("S1000: [Microsoft][ODBC SQL Server Driver]Connection
is busy with results for another hstmt.")
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:080a01c3be5f$e8847030$a001280a@.phx.gbl...[QUOTE]
> This does not answer the question. I am not programming a
> sql statement. I am setting up a DNS and I am unable to
> uncheck the Prepare box when setting up the data source.
> But thanks, anyway
> Server and
> It requires
> If you try to
> change this default
> sample code.
> SQL_ATTR_CURSOR_TYPE,
> SQL_SUCCESS_WITH_INFO){
> SQL_ATTR_ROW_ARRAY_SIZE,
> SQL_SUCCESS_WITH_INFO){
> the
> Does
> for free|||This is the option I am speaking of, if this option is
unchecked then it will execute directly and not use
prepare and execute. I am getting the error when I use the
dsn connection.
quote:
>--Original Message--
>Exactly what option are you referring to? There is an
option with the text:
quote:
>"Create temporary stored procedures for prepared SQL
statements and drop the
quote:
>stored procedures: ...". Why do you want to uncheck this
option?
quote:
>If you are not programming a SQL statement, then how are
you getting that
quote:
>error you reported? ("S1000: [Microsoft][ODBC SQL Server
Driver]Connection
quote:
>is busy with results for another hstmt.")
>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and
confers no rights.
quote:|||If you use SQLExecDirect() then the driver will not prepare the statement,
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:080a01c3be5f$e8847030$a001280a@.phx.gbl...
a[QUOTE]
SQL[QUOTE]
time.[QUOTE]
one.[QUOTE]
am[QUOTE]
to[QUOTE]
it[QUOTE]
>
>.
>
the driver will send the query directly to the server.
That option only applies when you are calling SQLPrepare() and SQLExecute().
As for the error, you are getting the error because you are not consuming
all of the results from an execution, before trying to perform another
execution. The easiest way to ensure you have consumed all of the results
is to call SQLMoreResults() until it returns SQL_NO_DATA. You can create as
many statements per connection as you want, but you need to make sure you
are completely done with a statement, before using another statement that
shares the same connection. If you need to use multiple statements on a
connection at the same time, then you need to use server-side cursors.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:066801c3bf2e$7d1e6290$a001280a@.phx.gbl...[QUOTE]
> This is the option I am speaking of, if this option is
> unchecked then it will execute directly and not use
> prepare and execute. I am getting the error when I use the
> dsn connection.
>
> option with the text:
> statements and drop the
> option?
> you getting that
> Driver]Connection
> confers no rights.
> a
> SQL
> time.
> one.
> am
> to
> it|||I know why I'm getting the error, I cannot get it to go
away. I am using a System DSN connection. This is my only
option, I am a developer stuck in the middle. Lotus LEI
tells me it's an MS problem. I do not have control over
the statements being executed. The only control I have is
with the ODBC Data Source. That option I am refering, if I
uncheck the box then it will use SQLExecDirect and my
problem will go away from what I am told.
I suppose I found a bug with ODBC Data Sources.
Thanks any way
quote:
>--Original Message--
>If you use SQLExecDirect() then the driver will not
prepare the statement,
quote:
>the driver will send the query directly to the server.
>That option only applies when you are calling SQLPrepare
() and SQLExecute().
quote:
>As for the error, you are getting the error because you
are not consuming
quote:
>all of the results from an execution, before trying to
perform another
quote:
>execution. The easiest way to ensure you have consumed
all of the results
quote:
>is to call SQLMoreResults() until it returns
SQL_NO_DATA. You can create as
quote:
>many statements per connection as you want, but you need
to make sure you
quote:
>are completely done with a statement, before using
another statement that
quote:
>shares the same connection. If you need to use multiple
statements on a
quote:
>connection at the same time, then you need to use server-
side cursors.
quote:
>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and
confers no rights.
quote:|||That option is for SQL 6.5 servers and older. If you are connecting to a
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:066801c3bf2e$7d1e6290$a001280a@.phx.gbl...
the[QUOTE]
this[QUOTE]
are[QUOTE]
Server[QUOTE]
programming[QUOTE]
to[QUOTE]
source.[QUOTE]
MS[QUOTE]
and[QUOTE]
getting an[QUOTE]
box[QUOTE]
and[QUOTE]
off.[QUOTE]
turn[QUOTE]
account[QUOTE]
>
>.
>
SQL 6.5 server, then you should have the option to disable it. With SQL 7.0
and newer we use a completely different way to prepare your statements. The
option is disabled, because it is not applicable to newer SQL Servers.
I want to help you, but I need to understand exactly what the problem is
that you are reporting. If you are getting an error that the connection is
busy, then this is a problem with the application that is using the driver.
If you still believe this is a problem with the driver, then you can contact
Microsoft Product Support and they will try to reproduce your problem and
figure out what is going on.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:1531501c3c41c$ac765370$a601280a@.phx
.gbl...[QUOTE]
> I know why I'm getting the error, I cannot get it to go
> away. I am using a System DSN connection. This is my only
> option, I am a developer stuck in the middle. Lotus LEI
> tells me it's an MS problem. I do not have control over
> the statements being executed. The only control I have is
> with the ODBC Data Source. That option I am refering, if I
> uncheck the box then it will use SQLExecDirect and my
> problem will go away from what I am told.
> I suppose I found a bug with ODBC Data Sources.
>
> Thanks any way
>
>
> prepare the statement,
> () and SQLExecute().
> are not consuming
> perform another
> all of the results
> SQL_NO_DATA. You can create as
> to make sure you
> another statement that
> statements on a
> side cursors.
> confers no rights.
> the
> this
> are
> Server
> programming
> to
> source.
> MS
> and
> getting an
> box
> and
> off.
> turn
> account
