B
Bill Long
Hi all,
I'm running into what I can only assume is a bug in the .Net framework. I'm
building a sort of DB pipeline application(to copy data from one DB to
another). When i'm copying data into a SqlServer table with an identity
column, I must first issue a
set identity_insert <tablename> on. Then I do my inserts. Then I issue a
set identity_insert <tablename> off and continue about my business.
If I just issue the set statements one right after the other, it works okay.
ie
set identity_insert <tablename> on
set identity_insert <tablename> off
set identity_insert <otherTablename> on
however, if I try to do some other operations on the SqlCommand object
between the time I set my identity_insert on and when I set
it off, then the thing blows up when I try to issue the next set
identity_insert otherTablename on.
I have tried all manner of ways to get it to work, including using different
transaction names for each set of inserts, closing and reopening my
SqlServer connection and using an entirely different sql server connection.
They all come back with the same error each time.
Anybody got any ideas on this one? I've been working with it a couple days
now, and can't get past it. I have provided some examples to show what I'm
talking about.
thanks in advance,
bill
----------------+
Examples |
----------------+------------------------------------
private void test() {
SqlCommand cciCmd = new SqlCommand("", _connCCI);
_connCCI.Open();
try {
cciCmd.CommandText = "set identity_insert eomCompany on";
cciCmd.ExecuteNonQuery();
cciCmd.CommandText = "set identity_insert eomCurrencyConversion on";
cciCmd.ExecuteNonQuery();
} catch (Exception ex) {
fdebug(ex.Message);
} finally {
_connCCI.Close();
}
}
Generates this error...
IDENTITY_INSERT is already ON for table 'CCI.dbo.eomCompany'. Cannot perform
SET operation for table 'eomCurrencyConversion'.
The above behavior is expected.
-----------------------------------------------------
private void test() {
SqlCommand cciCmd = new SqlCommand("", _connCCI);
_connCCI.Open();
try {
cciCmd.CommandText = "set identity_insert eomCompany on";
cciCmd.ExecuteNonQuery();
cciCmd.CommandText = "set identity_insert eomCompany off";
cciCmd.ExecuteNonQuery();
cciCmd.CommandText = "set identity_insert eomCurrencyConversion on";
cciCmd.ExecuteNonQuery();
} catch (Exception ex) {
fdebug(ex.Message);
} finally {
_connCCI.Close();
}
}
Generates no error...
The above behavior is expected.
------------------------------------------------------
private void copyEomCompany() {
this.st1Table.Text = "EOM.Company -> CCI->eomCompany";
SqlCommand cciCmd = new SqlCommand("",_connCCI);
OdbcCommand eomCmd = new OdbcCommand("",_connEOM);
string eomSql =
"select id, name,
addr1,addr2,city,state,postalcode,country,contactname," +
"contacttitle,contactphone,contactemail,contactfax,numsites,totalvalves," +
"createddate,createdby,modifieddate,modifiedby from company"; //19
fields
string cciSql =
"insert into eomCompany
(id,name,addr1,addr2,city,state,postalCode,country,contactName," +
"contactTitle,contactPhone,contactEmail,contactFax,
numSites,totalValves,insertedDate," +
"insertedBy, modifiedDate,modifiedBy) values ( " + //19 inserts
"@id,
@name,@addr1,@addr2,@city,@state,@postalCode,@country,@contactName," +
"@contactTitle,@contactPhone,@contactEmail,@contactFax,@numSites,@totalValve
s,@insertedDate," +
"@insertedBy, @modifiedDate, @modifiedBy)"; //19 values
try {
_connEOM.Open();
_connCCI.Open();
//lets see how many rows we need to copy, so we can keep track.
eomCmd.CommandText = "select count(*) from company";
int numCompanies = System.Convert.ToInt32(eomCmd.ExecuteScalar());
this.st1RowsToCopy.Text = "Total Rows: " + numCompanies.ToString();
Application.DoEvents();
eomCmd.CommandText = eomSql;
//some NOT NULL mysql insertedDate and modifiedDate values are actually
NULL, so we need to
//do something about that. Let's get a datetime from the server and use
that for the ones that are
//NULL
cciCmd.CommandText = "select getdate()";
string svrDate = System.Convert.ToString(cciCmd.ExecuteScalar());
//eomCompany has an identity column, so we need to allow identity
inserts. While we are at it,
//lets also blow the existing data away.(mostly for testing)
//--- HERE IS WHERE I SET MY IDENTITY_INSERT ON ----------//
cciCmd.CommandText = "delete from eomCompany;set identity_insert
eomCompany on";
cciCmd.ExecuteNonQuery();
//---SOMETHING FROM HERE DOWN IS CAUSING IT TO BLOW UP
//---IT HAPPENS AFTER THE FIRST ROW IS COPIED
cciCmd.Parameters.Add("@id", SqlDbType.Int);
cciCmd.Parameters.Add("@name", SqlDbType.VarChar);
cciCmd.Parameters.Add("@addr1", SqlDbType.VarChar);
cciCmd.Parameters.Add("@addr2", SqlDbType.VarChar);
cciCmd.Parameters.Add("@city", SqlDbType.VarChar);
cciCmd.Parameters.Add("@state", SqlDbType.VarChar);
cciCmd.Parameters.Add("@postalCode", SqlDbType.VarChar);
cciCmd.Parameters.Add("@country", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactName", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactTitle", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactPhone", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactEmail", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactFax", SqlDbType.VarChar);
cciCmd.Parameters.Add("@numSites", SqlDbType.Int);
cciCmd.Parameters.Add("@totalValves", SqlDbType.Int);
cciCmd.Parameters.Add("@insertedDate", SqlDbType.DateTime);
cciCmd.Parameters.Add("@insertedBy", SqlDbType.VarChar);
cciCmd.Parameters.Add("@modifiedDate", SqlDbType.DateTime);
cciCmd.Parameters.Add("@modifiedBy", SqlDbType.VarChar); //19 parms
cciCmd.CommandText = cciSql;
OdbcDataReader odr = eomCmd.ExecuteReader(CommandBehavior.SingleResult);
int rowsCopied = 0;
while(odr.Read()) {
cciCmd.Parameters[0].Value = odr.GetValue(0);
cciCmd.Parameters[1].Value = odr.IsDBNull(1) ? "Unknown" :
odr.GetValue(1);
cciCmd.Parameters[2].Value = odr.GetValue(2);
cciCmd.Parameters[3].Value = odr.GetValue(3);
cciCmd.Parameters[4].Value = odr.GetValue(4);
cciCmd.Parameters[5].Value = odr.GetValue(5);
cciCmd.Parameters[6].Value = odr.GetValue(6);
cciCmd.Parameters[7].Value = odr.GetValue(7);
cciCmd.Parameters[8].Value = odr.GetValue(8);
cciCmd.Parameters[9].Value = odr.GetValue(9);
cciCmd.Parameters[10].Value = odr.GetValue(10);
cciCmd.Parameters[11].Value = odr.GetValue(11);
cciCmd.Parameters[12].Value = odr.GetValue(12);
cciCmd.Parameters[13].Value = odr.GetValue(13);
cciCmd.Parameters[14].Value = odr.GetValue(14);
cciCmd.Parameters[15].Value = odr.IsDBNull(15) ? svrDate :
odr.GetValue(15);
cciCmd.Parameters[16].Value = odr.IsDBNull(16) ? "Unknown" :
odr.GetValue(16);
cciCmd.Parameters[17].Value = odr.IsDBNull(17) ? svrDate :
odr.GetValue(17);
cciCmd.Parameters[18].Value = odr.IsDBNull(18) ? "Unknown" :
odr.GetValue(18); //19 vals
cciCmd.ExecuteNonQuery();
fdebug("Copying Row " + cciCmd.Parameters["@id"].Value.ToString());
this.st1RowsCopied.Text = "Rows Copied: " + ((int)(rowsCopied++) +
1).ToString();
this.st1RowsLeft.Text = "Rows Left: " + ((int)numCompanies -
rowsCopied).ToString();
Application.DoEvents();
}
//----------------- THIS IS WHERE IT GETS WIERD ------------------------//
odr.Close();
cciCmd.CommandText = "set identity_insert eomCompany off";
cciCmd.ExecuteNonQuery();
//debugging stuff
try {
cciCmd.CommandText = "set identity_insert eomCurrencyConversion on";
cciCmd.ExecuteNonQuery();
} catch (Exception ex) {
fdebug(ex.Message);
throw(ex);
}
//----------------- END WIERDNESS --------------//
} catch (Exception ex) {
lblStatus.Text = "Error Encountered: " + ex.Message + ", Inner Exception
= " +
ex.InnerException + ", Sql = " + cciCmd.CommandText + ", and " +
eomCmd.CommandText +
", Stack Trace = " + ex.StackTrace;
Exception ex1 = new Exception("Error in Copy Table");
throw ex1;
} finally {
_connEOM.Close();
_connCCI.Close();
}
}
THIS Generates this Error..
IDENTITY_INSERT is already ON for table 'CCI.dbo.eomCompany'. Cannot perform
SET operation for table 'eomCurrencyConversion'.
The above behavior is most definititely NOT expected.
I'm running into what I can only assume is a bug in the .Net framework. I'm
building a sort of DB pipeline application(to copy data from one DB to
another). When i'm copying data into a SqlServer table with an identity
column, I must first issue a
set identity_insert <tablename> on. Then I do my inserts. Then I issue a
set identity_insert <tablename> off and continue about my business.
If I just issue the set statements one right after the other, it works okay.
ie
set identity_insert <tablename> on
set identity_insert <tablename> off
set identity_insert <otherTablename> on
however, if I try to do some other operations on the SqlCommand object
between the time I set my identity_insert on and when I set
it off, then the thing blows up when I try to issue the next set
identity_insert otherTablename on.
I have tried all manner of ways to get it to work, including using different
transaction names for each set of inserts, closing and reopening my
SqlServer connection and using an entirely different sql server connection.
They all come back with the same error each time.
Anybody got any ideas on this one? I've been working with it a couple days
now, and can't get past it. I have provided some examples to show what I'm
talking about.
thanks in advance,
bill
----------------+
Examples |
----------------+------------------------------------
private void test() {
SqlCommand cciCmd = new SqlCommand("", _connCCI);
_connCCI.Open();
try {
cciCmd.CommandText = "set identity_insert eomCompany on";
cciCmd.ExecuteNonQuery();
cciCmd.CommandText = "set identity_insert eomCurrencyConversion on";
cciCmd.ExecuteNonQuery();
} catch (Exception ex) {
fdebug(ex.Message);
} finally {
_connCCI.Close();
}
}
Generates this error...
IDENTITY_INSERT is already ON for table 'CCI.dbo.eomCompany'. Cannot perform
SET operation for table 'eomCurrencyConversion'.
The above behavior is expected.
-----------------------------------------------------
private void test() {
SqlCommand cciCmd = new SqlCommand("", _connCCI);
_connCCI.Open();
try {
cciCmd.CommandText = "set identity_insert eomCompany on";
cciCmd.ExecuteNonQuery();
cciCmd.CommandText = "set identity_insert eomCompany off";
cciCmd.ExecuteNonQuery();
cciCmd.CommandText = "set identity_insert eomCurrencyConversion on";
cciCmd.ExecuteNonQuery();
} catch (Exception ex) {
fdebug(ex.Message);
} finally {
_connCCI.Close();
}
}
Generates no error...
The above behavior is expected.
------------------------------------------------------
private void copyEomCompany() {
this.st1Table.Text = "EOM.Company -> CCI->eomCompany";
SqlCommand cciCmd = new SqlCommand("",_connCCI);
OdbcCommand eomCmd = new OdbcCommand("",_connEOM);
string eomSql =
"select id, name,
addr1,addr2,city,state,postalcode,country,contactname," +
"contacttitle,contactphone,contactemail,contactfax,numsites,totalvalves," +
"createddate,createdby,modifieddate,modifiedby from company"; //19
fields
string cciSql =
"insert into eomCompany
(id,name,addr1,addr2,city,state,postalCode,country,contactName," +
"contactTitle,contactPhone,contactEmail,contactFax,
numSites,totalValves,insertedDate," +
"insertedBy, modifiedDate,modifiedBy) values ( " + //19 inserts
"@id,
@name,@addr1,@addr2,@city,@state,@postalCode,@country,@contactName," +
"@contactTitle,@contactPhone,@contactEmail,@contactFax,@numSites,@totalValve
s,@insertedDate," +
"@insertedBy, @modifiedDate, @modifiedBy)"; //19 values
try {
_connEOM.Open();
_connCCI.Open();
//lets see how many rows we need to copy, so we can keep track.
eomCmd.CommandText = "select count(*) from company";
int numCompanies = System.Convert.ToInt32(eomCmd.ExecuteScalar());
this.st1RowsToCopy.Text = "Total Rows: " + numCompanies.ToString();
Application.DoEvents();
eomCmd.CommandText = eomSql;
//some NOT NULL mysql insertedDate and modifiedDate values are actually
NULL, so we need to
//do something about that. Let's get a datetime from the server and use
that for the ones that are
//NULL
cciCmd.CommandText = "select getdate()";
string svrDate = System.Convert.ToString(cciCmd.ExecuteScalar());
//eomCompany has an identity column, so we need to allow identity
inserts. While we are at it,
//lets also blow the existing data away.(mostly for testing)
//--- HERE IS WHERE I SET MY IDENTITY_INSERT ON ----------//
cciCmd.CommandText = "delete from eomCompany;set identity_insert
eomCompany on";
cciCmd.ExecuteNonQuery();
//---SOMETHING FROM HERE DOWN IS CAUSING IT TO BLOW UP
//---IT HAPPENS AFTER THE FIRST ROW IS COPIED
cciCmd.Parameters.Add("@id", SqlDbType.Int);
cciCmd.Parameters.Add("@name", SqlDbType.VarChar);
cciCmd.Parameters.Add("@addr1", SqlDbType.VarChar);
cciCmd.Parameters.Add("@addr2", SqlDbType.VarChar);
cciCmd.Parameters.Add("@city", SqlDbType.VarChar);
cciCmd.Parameters.Add("@state", SqlDbType.VarChar);
cciCmd.Parameters.Add("@postalCode", SqlDbType.VarChar);
cciCmd.Parameters.Add("@country", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactName", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactTitle", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactPhone", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactEmail", SqlDbType.VarChar);
cciCmd.Parameters.Add("@contactFax", SqlDbType.VarChar);
cciCmd.Parameters.Add("@numSites", SqlDbType.Int);
cciCmd.Parameters.Add("@totalValves", SqlDbType.Int);
cciCmd.Parameters.Add("@insertedDate", SqlDbType.DateTime);
cciCmd.Parameters.Add("@insertedBy", SqlDbType.VarChar);
cciCmd.Parameters.Add("@modifiedDate", SqlDbType.DateTime);
cciCmd.Parameters.Add("@modifiedBy", SqlDbType.VarChar); //19 parms
cciCmd.CommandText = cciSql;
OdbcDataReader odr = eomCmd.ExecuteReader(CommandBehavior.SingleResult);
int rowsCopied = 0;
while(odr.Read()) {
cciCmd.Parameters[0].Value = odr.GetValue(0);
cciCmd.Parameters[1].Value = odr.IsDBNull(1) ? "Unknown" :
odr.GetValue(1);
cciCmd.Parameters[2].Value = odr.GetValue(2);
cciCmd.Parameters[3].Value = odr.GetValue(3);
cciCmd.Parameters[4].Value = odr.GetValue(4);
cciCmd.Parameters[5].Value = odr.GetValue(5);
cciCmd.Parameters[6].Value = odr.GetValue(6);
cciCmd.Parameters[7].Value = odr.GetValue(7);
cciCmd.Parameters[8].Value = odr.GetValue(8);
cciCmd.Parameters[9].Value = odr.GetValue(9);
cciCmd.Parameters[10].Value = odr.GetValue(10);
cciCmd.Parameters[11].Value = odr.GetValue(11);
cciCmd.Parameters[12].Value = odr.GetValue(12);
cciCmd.Parameters[13].Value = odr.GetValue(13);
cciCmd.Parameters[14].Value = odr.GetValue(14);
cciCmd.Parameters[15].Value = odr.IsDBNull(15) ? svrDate :
odr.GetValue(15);
cciCmd.Parameters[16].Value = odr.IsDBNull(16) ? "Unknown" :
odr.GetValue(16);
cciCmd.Parameters[17].Value = odr.IsDBNull(17) ? svrDate :
odr.GetValue(17);
cciCmd.Parameters[18].Value = odr.IsDBNull(18) ? "Unknown" :
odr.GetValue(18); //19 vals
cciCmd.ExecuteNonQuery();
fdebug("Copying Row " + cciCmd.Parameters["@id"].Value.ToString());
this.st1RowsCopied.Text = "Rows Copied: " + ((int)(rowsCopied++) +
1).ToString();
this.st1RowsLeft.Text = "Rows Left: " + ((int)numCompanies -
rowsCopied).ToString();
Application.DoEvents();
}
//----------------- THIS IS WHERE IT GETS WIERD ------------------------//
odr.Close();
cciCmd.CommandText = "set identity_insert eomCompany off";
cciCmd.ExecuteNonQuery();
//debugging stuff
try {
cciCmd.CommandText = "set identity_insert eomCurrencyConversion on";
cciCmd.ExecuteNonQuery();
} catch (Exception ex) {
fdebug(ex.Message);
throw(ex);
}
//----------------- END WIERDNESS --------------//
} catch (Exception ex) {
lblStatus.Text = "Error Encountered: " + ex.Message + ", Inner Exception
= " +
ex.InnerException + ", Sql = " + cciCmd.CommandText + ", and " +
eomCmd.CommandText +
", Stack Trace = " + ex.StackTrace;
Exception ex1 = new Exception("Error in Copy Table");
throw ex1;
} finally {
_connEOM.Close();
_connCCI.Close();
}
}
THIS Generates this Error..
IDENTITY_INSERT is already ON for table 'CCI.dbo.eomCompany'. Cannot perform
SET operation for table 'eomCurrencyConversion'.
The above behavior is most definititely NOT expected.