Hi QuickNote, Actually, according to your error message IDENTITY_INSERT is already off, I think you were missing the "ON" command.
The IDENTITY_INSERT command has the following characteristics: "At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2005 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for." (your error message didn't look like this one) A session loosely refers to the current connection, once your connection closes IDENTITY_INSERT is reset for the given table. Or simply, the next time you need to do an Identity insert, issue "ON" at the beginning of the statement and "OFF" at the end of it, then you don't have to wait for the connection to recycle. Hope that helps. Scott Cadillac, Email: [EMAIL PROTECTED] http://scott.cadillac.bz > -----Original Message----- > From: Quicknote [mailto:[EMAIL PROTECTED] > Sent: Sunday, May 21, 2006 6:01 AM > To: WiTango List > Subject: Witango-Talk: Problem with IDENTITY_INSERT > > There should be a way to > SET IDENTITY_INSERT "all" OFF ?? > > I inserted a row directly into table "B" using Microsoft SQL. > MSQL of course SET IDENTITY_INSERT ON for table "B" for the INSERT. > I forgot to turn it off. > So the next time I used Witango I got an error message. > > 544 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot > insert explicit > value for identity column in table 'rp350' when > IDENTITY_INSERT is set to > OFF. > > I have code in Witango to manage the SET IDENTITY_INSERT(s) > but it failed > becauase I neglected to include table "B". > > Does anybody have code that would: SET IDENTITY_INSERT "all" OFF > Then Witango SQL would be ready for the SET IDENTITY_INSERT ON > > ************************************** > here is a code sample I use > [Direct DBMS Action] [36] GetNewProfileID_rp350 : ADEDEVDS1_TEST > [Query] [36] > SET IDENTITY_INSERT physio.rp250 OFF > SET IDENTITY_INSERT physio.rp350 OFF > SET IDENTITY_INSERT physio.rp250RxPlan OFF > SET IDENTITY_INSERT physio.rp250_icd9 OFF > SET IDENTITY_INSERT physio.rp350 > ON SELECT max( ID_RP350PROFILEID ) + 1 from physio.RP350 > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
