Bob Beauchemin
6/9/2002 4:30:00 AM
Hi Juan,
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
bobb@develop.com
"Juan M. Servera" <soylan@hotmail.com> wrote in message
news:Of44GXtCCHA.2576@tkmsftngp04...
> Ok, sorry, I thougth it would be easier to understand my problem in
> 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_bobb_spam@develop.com> escribió en el mensaje
> news:ercpLlNCCHA.1724@tkmsftngp02...
> > Oh, since you wrote pseudocode, I assumed what the problem was from the
> > error message. Bad assumption.
> >
> > Just call Close on your OdbcDataReader, then you can use the same
> connection
> > again.
> >
> > Bob Beauchemin
> > bobb@develop.com
> >
> >
> > "Juan M. Servera" <soylan@hotmail.com> wrote in message
> > news:OGU2xEMCCHA.1432@tkmsftngp04...
> > > I just have one destination recordset at a time in the destination
> > > 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_bobb_spam@develop.com> escribió en el mensaje
> > > news:ugnOOSLCCHA.2052@tkmsftngp02...
> > > > In forward-only read-only ("cursorless") mode, in SQL Server (and
> > perhaps
> > > > 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
> > > > bobb@develop.com
> > > >
> > > >
> > > > "Juan M. Servera" <soylan@hotmail.com> wrote in message
> > > > news:OKD69KLCCHA.2552@tkmsftngp05...
> > > > > I'm developing a tool that copies data between two DSN. When
copying
> > > from
> > > > > 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!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>