ERROR [HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Juan M. Servera
any database to a SQLServer2000 database sometimes I get this message:
ERROR [HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with
results for another hstmt
I've found an explanation from the MSDN that tells that's a problem with
thread, but I'm not using threading.
What I'm doing is something like this:
Open a connection to a source
Open a connectin to a destination
iterate through all the tables
create a source adapter
fill the source dataset
create a destination adapter
fill the destination dataset with the schema
iterate through source dataset
iterate every 100 rows
start transaction
fill destination dataset with a maximum of 100 rows
update destination adapter with destination dataset
commit transaction
clear dataset
end iterate rows
end iterate source
end iterate tables
close all
I've just noticed this bug in sqlserver2000, copying to sqlserver7 or
Sybase7 works well, I'm having other problems with Oracle, but not this one.
It can happen when creating the destination dataset or when updating data.
I've tried to do it without transations but I've had the same problem.
I cannot use ado.net or oledb drivers
Any ideas?
Thanks!
Bob Beauchemin
Sybase), you can only have one open resultset per connection at a time. ADO
auto-opened secondary connections to compensate for this, when using this
mode. Since this is the mode that .NET uses (when supported by the
database), the SqlConnection, OleDbConnection, and OdbcConnection act this
way. If you need multiple resultsets active at a time, you need to open
multiple connections.
I actually prefer explicit to auto-open.
Bob Beauchemin
bo...@develop.com
"Juan M. Servera" <soy...@hotmail.com> wrote in message
news:OKD69KLCCHA.2552@tkmsftngp05...
Juan M. Servera
connection that I just use it for update.
The source one has a different connection and a different DSN.
So, what I've done is a loop retrying 3 times in the case I've catched this
exception. This workaround seems to work, but it's not very pretty.
Thank you
"Bob Beauchemin" <no_bob...@develop.com> escribió en el mensaje
news:ugnOOSLCCHA.2052@tkmsftngp02...
Bob Beauchemin
error message. Bad assumption.
Just call Close on your OdbcDataReader, then you can use the same connection
again.
Bob Beauchemin
bo...@develop.com
"Juan M. Servera" <soy...@hotmail.com> wrote in message
news:OGU2xEMCCHA.1432@tkmsftngp04...
Juan M. Servera
pseudocode. I'm not used to find help in newsgroups.
As you will see in the code I'm not using a DataReader, I just copy to the
destination DataTable about 200 rows at a time, do an update from the
DataAdapter.Update(DataTable) then I clear the destination DataTable and
loop again.
Many many thanks.
My code for the copy method is that one:
********************* Copy a table between two different connections
public int CopyTable(string TableName, IProvider source, IProvider
destination)
{
DataSet dsSource, dsDestination; //the two datasets I will receive from the
IProviders
int rowcount;
int count=-1;
dsSource=source.CreateReadDataSet(TableName);
if (dsSource!=null)
{
try
{
dsDestination=destination.CreateWriteDataSet(TableName,dsSource);
if(dsDestination!=null)
{
rowcount=dsSource.Tables[0].Rows.Count;
count=0;
try
{
for(int i=0; i<(rowcount/iBatchSize)+1;i++) //batchsize is defined in
the constructor, usually about 200 rows.
{
for (int j=0; (j<(rowcount)-(iBatchSize*i)) && j<iBatchSize; j++)
{
count++;
dsDestination.Tables[0].Rows.Add(dsSource.Tables[0].Rows[(i*iBatchSize)+j].I
temArray);
}
destination.BeginTran();
try
{
destination.Update(dsDestination.Tables[0]); //do
OdbcDataAdapter.Update(DataTable)
destination.CommitTran();
OnPercent(this,(double) count/ (double) rowcount,count);
catch(Exception ex)
{
Debug.WriteLine(" ***************Transaction rolled
back!*******\r\n\t"+ex.Message);
Debug.WriteLine(ex.GetType());
destination.RollBackTran();
throw(ex);
}
dsDestination.Tables[0].Clear();
}
}//end try 1
finally
{
dsDestination.Dispose();
}
}// end if
}//end try source
catch(Exception e)
{
sMessage=e.Message;
Debug.WriteLine("Copy failed "+e.Message);
count=-1;
}
finally
{
dsSource.Dispose();
}
} //end if
return count;
}
********************************+
and now the important code for the IProvider, every where you see a 'while
loop' I'm doing retries.
public class ODBC:IProvider
{
private const int RETRIES=3;
private OdbcConnection oConn;
private OdbcDataAdapter oAdp;
private OdbcCommandBuilder oCb;
private DataSet oDs;
private string sDsn;
private OdbcTransaction oTran;
public ODBC(string dsn)
{
string connString;
sDsn=dsn;
connString="DSN="+sDsn+";UID=MYUID;PWD=MYPWD"; //connect to a DSN
oConn=new OdbcConnection(connString);
oConn.Open();
}
~ODBC()
{
if (oCb!=null)
oCb.Dispose();
if (oAdp!=null)
oAdp.Dispose();
if (oDs!=null)
oDs.Dispose();
oConn.Close();
oConn.Dispose();
}
//this method creates a DataSet and fills it with all the data from the
table
public DataSet CreateReadDataSet(string TableName)
{
oAdp=null;
oAdp=new OdbcDataAdapter("select * FROM "+TableName,oConn);
oCb= new OdbcCommandBuilder(oAdp);
try
{
oDs=new DataSet();
oAdp.MissingSchemaAction=MissingSchemaAction.AddWithKey;
oAdp.Fill(oDs);
catch(Exception e)
{
oDs=null;
System.Diagnostics.Debug.WriteLine("Read "+e.Message);
}
return oDs;
}
// this other one creates a DataSet but just gets an Schema similar to the
one in the source
public DataSet CreateWriteDataSet(string TableName, DataSet source)
{
string fields="";
oAdp=null;
foreach( DataColumn col in source.Tables[0].Columns)
{
if (fields.Length>0) fields+=", ";
fields+=col.ColumnName;
}
if (fields.Length==0) fields="*";
oAdp=new OdbcDataAdapter("select "+fields+" FROM "+TableName,oConn);
oCb= new OdbcCommandBuilder(oAdp);
oDs=new DataSet();
oDs.EnforceConstraints=true;
try
{
int retry=RETRIES; //number of retries
while(retry>0)
{
try
{
oAdp.FillSchema(oDs,System.Data.SchemaType.Mapped);
retry=0;
}
catch(OdbcException ex)
{
if (ex.Errors[0].SQLState=="HY000") //catch sqlserver exceptions and
try again
{
Debug.WriteLine(" Create Write");
Debug.WriteLine(ex.Message + "\r\n\tRetrying");
retry--;
if(retry==0)
throw(ex);
}
else
{
throw(ex);
}
}
}
}
catch(Exception e)
{
oDs=null;
System.Diagnostics.Debug.WriteLine("Write "+e.Message);
}
return oDs;
}
// update procedure for a datatable
// retries 2 times if the update was not successful on sqlserver2000
public bool Update(DataTable table)
{
int retry=RETRIES; //number of retries
while(retry>0)
{
try
{
oAdp.Update(table);
retry=0;
}
catch(OdbcException ex)
{
if (ex.Errors[0].SQLState=="HY000") //catch sqlserver exceptions and try
again
{
Debug.WriteLine(" Update");
Debug.WriteLine(ex.Message + "\r\n\tRetrying");
retry--;
if(retry==0)
throw(ex);
}
else
{
throw(ex);
}
}
}
return true;
}
public void BeginTran()
{
int retry=RETRIES;
while(retry>0)
{
try
{
oAdp.InsertCommand=oCb.GetInsertCommand();
retry=0;
}
catch(OdbcException ex)
{
if (ex.Errors[0].SQLState=="HY000") //catch sqlserver exceptions and try
again
{
Debug.WriteLine(" Begin Transaction");
Debug.WriteLine(ex.Message + "\r\n\tRetrying");
retry--;
if(retry==0)
throw(ex);
}
else
{
throw(ex);
}
}
}
oTran= oConn.BeginTransaction();
oAdp.InsertCommand.Transaction = oTran;
}
public void CommitTran()
{
oTran.Commit();
}
public void RollBackTran()
{
oTran.Rollback();
}
}
"Bob Beauchemin" <no_bob...@develop.com> escribió en el mensaje
news:ercpLlNCCHA.1724@tkmsftngp02...
Bob Beauchemin
Sorry for the long delay, I've been on the road all week.
So, it does fail on the Update, this may be related to a thread problem in
ODBC, as the OdbcCommandBuilder will issue one insert command per row, and
could, somehow be "getting ahead of" the transaction log write. If you catch
the insert (in the OdbcDataAdapter's RowUpdating event) and put a sleep call
in at that point, does it help, at all. But that's just as ugly of a
workaround, I guess.
Bob Beauchemin
bo...@develop.com
"Juan M. Servera" <soy...@hotmail.com> wrote in message
news:Of44GXtCCHA.2576@tkmsftngp04...