Sunday, March 11, 2012
Connection is busy with results for another hstmt
server 6.5.
I upgraded DB to SQL Server 2000 and now the application
give me "Connection is busy with results for another
hstmt " ODBC error after I do couple of queries or updates
through the application.
Is it anything to do with ODBC drivers or I have to change
setup of SQL Server?
Thank you
Athar
SQL Server only supports a single active statement per connection. Are you
using the MS JDBC driver to access SQL Server?
If you aren't actually trying to use multiple statements per connection,
then you might be able to workaround the error by making sure the statement
is closed before using another one. I'm not familiar with the JDBC
interface, but in ODBC, you'd call SQLMoreResults() until it returned
SQL_NO_DATA to make sure you've processed all of the results on the wire.
In ADO.NET you will get the same error if you haven't called Close() or
Dispose() on the SqlDataReader before trying to Execute another SqlCommand
on the same SqlConnection.
If you are trying to use multiple statements per connection (at the same
time), then you need to use multiple connections instead.
SQL Server 2005 has a new feature called MARS (Multiple Active Result Sets)
which will allow multiple statements per connection.
Brannon
"Athar" <aikram@.celestica.com> wrote in message
news:185a01c47bdf$ee142e80$a601280a@.phx.gbl...
> I have my java based application that works fine with SQL
> server 6.5.
> I upgraded DB to SQL Server 2000 and now the application
> give me "Connection is busy with results for another
> hstmt " ODBC error after I do couple of queries or updates
> through the application.
> Is it anything to do with ODBC drivers or I have to change
> setup of SQL Server?
> Thank you
> Athar
Connection is busy with results for another hstmt
server 6.5.
I upgraded DB to SQL Server 2000 and now the application
give me "Connection is busy with results for another
hstmt " ODBC error after I do couple of queries or updates
through the application.
Is it anything to do with ODBC drivers or I have to change
setup of SQL Server?
Thank you
AtharSQL Server only supports a single active statement per connection. Are you
using the MS JDBC driver to access SQL Server?
If you aren't actually trying to use multiple statements per connection,
then you might be able to workaround the error by making sure the statement
is closed before using another one. I'm not familiar with the JDBC
interface, but in ODBC, you'd call SQLMoreResults() until it returned
SQL_NO_DATA to make sure you've processed all of the results on the wire.
In ADO.NET you will get the same error if you haven't called Close() or
Dispose() on the SqlDataReader before trying to Execute another SqlCommand
on the same SqlConnection.
If you are trying to use multiple statements per connection (at the same
time), then you need to use multiple connections instead.
SQL Server 2005 has a new feature called MARS (Multiple Active Result Sets)
which will allow multiple statements per connection.
Brannon
"Athar" <aikram@.celestica.com> wrote in message
news:185a01c47bdf$ee142e80$a601280a@.phx.gbl...
> I have my Java based application that works fine with SQL
> server 6.5.
> I upgraded DB to SQL Server 2000 and now the application
> give me "Connection is busy with results for another
> hstmt " ODBC error after I do couple of queries or updates
> through the application.
> Is it anything to do with ODBC drivers or I have to change
> setup of SQL Server?
> Thank you
> Athar
Sunday, February 19, 2012
Connection between 2 databases in local sql server
Hi,
I have two different applications based on SQL server database.
In one of the applications I need to use the information stored in the second database, only for read purpose.
As I understand I need how some to make a link between the databases in order to share tables.
Can anybody advise me how to do that, if it is possible at all?
Thanks
Just use a fully qualified name i.e. Server.Database.Owner.Table
for example: Select * from MyServer.Northwind.dbo.Customers
Regards
Paul
|||Hi Paul,
Thanks for your replay, but maybe I'm not explained myself as well.
Because I'm accessing the database through ODBC, and when I'm using the specific DSN only the tables which are belongs to the specific database are available to me.
That’s the reason I though to make a link between the databases.
There is an option to do that?
|||The easy way would be to create a stored procedure and have it do the work for you.
The hard way would be to establish 2 connections and query the tables seperately. If you are using .NET you could them combine them in a DataSet as two DataTables and establish a DataRelation.
Regards
Paul
|||Hi Paul
Unfortunately I'm not using .NET, I'm working with VB6.
I'll appreciate it if you can be more specific about the stored procedure, how can I access the tables in the other database.
Thanks again
|||Something like:
Create Proc MyProcedure
AS
select * from Db1.dbo.Table1 as tb1
inner join Db2.dbo.OtherTable as tb2
on tb1.id = tb2.id
Set the command type in an ADO Command object to a Stored Procedure and the CommnandText to MyProcedure (the stored procedure name) and call the execute method.
connection based temp tables
I am developing a web based webshop with a ms sql back end, but I
cannot figure out how to do connection based temp tables, so that each
user gets their own temp table to hold the purchased items.
any ideas or hints would bery much appreciated
rgds
Matt<matt@.fruitsalad.org> wrote in message
news:1126360634.762880.315890@.g43g2000cwa.googlegr oups.com...
> Hello
> I am developing a web based webshop with a ms sql back end, but I
> cannot figure out how to do connection based temp tables, so that each
> user gets their own temp table to hold the purchased items.
> any ideas or hints would bery much appreciated
> rgds
> Matt
A better idea is probably to use a single table, with a customer ID or
session ID as part of the key. That way you can store items for some time
and you don't lose any information if the connection from your middle tier
is broken for some reason. You also have an easy way to view aggregate
information for all customers/sessions, and so on.
Since normal temp tables exist per connection and you probably won't have
one new client connection per customer from the middle tier to the database,
it's doubtful if temp tables would be a good solution for you.
Simon|||Dear Matt,
You'll have a tricky time with this approach to shopping carts I think.
Whilst you can create a temp table that persists for only as long as the
connection to database exists - you'd have to keep the database connected to
the webserver permanently and once for every user of the website. This would
be packed full of problems - not least of which would be that you'd run out
of connections pretty quickly.
You've got two main options.
1) Keep the shopping cart on the webserver not in the database - this has a
lot of advantages - 1) If you're choice of scripting language manages
sessions for you (like asp.net) then you're users shopping carts will
dissapear without you having to manage the process of cleaning them up. 2)
It'd be much faster - you wouldn't have to keep hitting the database to add
something into the cart, or to pull up the cart items. 3) You wouldn't hve
to clean up the database for abandoned order (your rate might be as high as
70-80% of orders that get abandoned).
2) Keep the cart in a
"pending orders" table - and keep track of the time that each item was
added, and group the items together either by a cart id or by a customer
number - then you could automate clean up.
Nick
<matt@.fruitsalad.org> wrote in message
news:1126360634.762880.315890@.g43g2000cwa.googlegr oups.com...
> Hello
> I am developing a web based webshop with a ms sql back end, but I
> cannot figure out how to do connection based temp tables, so that each
> user gets their own temp table to hold the purchased items.
> any ideas or hints would bery much appreciated
> rgds
> Matt