Hi Scott What you wrote is correct but I still get caught. I have noticed that SQL does not create an error if you give a command to SET IDENTITY_INSERT OFF even if the SET IDENTITY_INSERT is already OFF. SO I ususally include the SET IDENTITY_INSERT OFF command for all related tables prior to the single SET IDENTITY_INSERT ON command just to avoid these errors. However it is a lot to keep track of becasue I have a lot of tables. And I got caught this time becasue I did that manual INSERT ACTION. So a bit of code like: ************** Check all tables for SET IDENTITY_INSERT =ON If true set to SET IDENTITY_INSERT OFF then set single table SET IDENTITY_INSERT ON ******************* I actualy tried SET IDENTITY_INSERT * OFF didn't work I also looke all thry SQL help Is this wishfull thinking?
Thanks Janet If any table gets left with SET IDENTITY_INSERT ON -----Original Message----- From: Scott Cadillac [mailto:[EMAIL PROTECTED] Sent: Sunday, May 21, 2006 10:22 AM To: '[email protected]' Subject: RE: Witango-Talk: Problem with IDENTITY_INSERT 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 ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
