Hi Scott My problem is which table has it's identity insert SET ON? I get the error message that "identity insert SET ON" has failed, but no info on what other table is on. My idea of SET all OFF was to just turn that one table to the OFF state - when I dont know waht table to set to OFF. With a data base with over one hundred & fifty tables and many INSERT actions it could happen that "identity insert SET OFF" might fail then all my INSERT ACTIONS that use identity inserts fail and I have to find which table is causing the trouble. So maybe someone knows an a bit of script that would find that table that is in the ON state and then set it to OFF?
Thanks, Janet -----Original Message----- From: Scott Cadillac [mailto:[EMAIL PROTECTED] Sent: Sunday, May 21, 2006 7:01 PM To: '[email protected]' Subject: RE: Witango-Talk: Problem with IDENTITY_INSERT Hi Janet, "OFF" is the default state, therefore it won't throw an error when you issue an "OFF" command. As the documentation points out, an error is only thrown when issuing "ON" - when "ON" is already on for another table (only one table can have the "ON" state at a time). So, when you need to do an identity insert, simply issue "ON" before the insert, then "OFF" right after the insert and then you won't have to do all this other business. If you follow the above suggestion then you don't need an "ALL OFF" script, which is pointless given that only one table can be "ON" anyway. Hope this helps. Scott Cadillac, Email: [EMAIL PROTECTED] http://scott.cadillac.bz > -----Original Message----- > From: Quicknote [mailto:[EMAIL PROTECTED] > Sent: Sunday, May 21, 2006 9:33 AM > To: [email protected] > Subject: RE: Witango-Talk: Problem with IDENTITY_INSERT > > 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 > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
