drop identity of column

[Naqibuquo]

my problem i'm facing is relevant to a closed thread (thread183-1149286). The script provided works fine and table access is also fine untill... the SQL service is restarted. From then on, the object seems not to be accesible anymore and the following error message appears when a SELECT statement like the following is issued:

SELECT [c1], [c2] FROM [Northwind].[dbo].[a]
"
Server: Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 1141579105 in database 'a'. Run DBCC CHECKTABLE on sysobjects.

Connection Broken
"

Any idea why this happens and how can it be resolved?

really appreciated
thnx

 

[SQLDenis]

that is what happens when you do stuff which is not documented. Next time insert into a new table, drop the old table and rename the new table with the name of the old table


>>Could not find row in sysobjects for object ID 1141579105 in database 'a'.

SELECT [c1], [c2] FROM [Northwind].[dbo].[a]

this doesn't look like it references database a are you sure you connected to the correct DB?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

What exactly are you doing? Why are you dropping the identity field and recreating it? This is generally not a good practice.

Did you run DBCC CHECKTABLE? Did it fix the issue?

"NOTHING is more important in a database than integrity." ESquared

 

[Naqibuquo]

SQLDenis: I know it's undocumented but it's the best thing I've come across that deals with the issue I need to resolve, so, drop, create, rename etc. won't do the trick (pleaz read below). I'm sure the statement is correct, something else must be wrong.

SQLSister: Well, I'm not interested in recreating the identity column, only dropping it. I think... DBCC CHECKTABLE found nothing to fix.

So, the story has as follows:

I have in place a proc that for a given table (TABLEx) creates dynamically an identical empty one (TABLEx_LOG) and also appends the relevant trigger to (TABLEx), so as to populate (TABLEx_LOG) on every insert, update, delete. The statement I use for table creation is the following:

SELECT top 0 * into TABLEx_LOG from TABLEx;

the trigger inside uses something like:

INSERT into TABLEx_LOG SELECT * from inserted;

All fine till the point when (TABLEx) has an identity column. The SELECT statement above will create (TABLEx_LOG) with the same identity column as the source but... I don't need a new identity value in my LOG, only the original one that the trigger provides me with.

SET IDENTITY_INSERT ON/OFF would be solution but it seems not to be working inside a trigger. The way I see it, I need ?somehow? not to have the identity in (TABLEx_LOG).

How could I get rid of the identity?

 

[Naqibuquo]

after some diggin' around I came across to this thread

http://www.sqlservercentral.com/Forums/Topic13346-8-1.aspx?Highlight=drop+identity.

There is a suggestion there to use a dynamic procedure of this sort:

Create Procedure CreateTable  (@Table sysname, @Target sysname, @Condition varchar(1000))
As
Declare @SQL varchar(8000)
Select @SQL =''
Select @SQL = @SQL +', '+
Case 
when AutoVal is NOT NULL then 'cONVERT(int, ' + NAME+') as '+ Name 
else name end
from SYScOLUMNS where id = object_id(@Table) order by ColId
Select @SQL = 'Select '+ SUBSTRING(@SQL, 3, LEN(@SQL)-2)
Select @SQL =@SQL + ' into '+ @Target +' From '+@Table
IF @Condition<>'' 
     Select @SQL = @SQL + ' Where '+@Condition
Execute(@SQL)
--Print @SQL
...
GO

A small and ingenious approach that will do the trick. Many thnx to anyone who may have tried to resolve the issue.

regards

 

[ESquared]

An easy way to remove the identity aspect of a column when doing a SELECT INTO is to multiply it by 1 or add 0 to it. Converting to int also works.

SELECT IdentityColumn * 1, OtherColumn, OtherColumn2 INTO Table2 FROM Table1

Do you realize that the question you asked in your original post is not really the problem you were trying to solve? The answers you got didn't answer your problem for that reason.