identity or no identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

identity or no identity

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-26 : 20:25:48

in running some dynamic SQL to set identity insert on/off, I came across this error

Server: Msg 8106, Level 16, State 1, Line 3
Table 'TMS.dbo.tINACTIVE_PROJECTS' does not have the identity property. Cannot perform SET operation.

While this is undoubtedly true (and not strictly causing me a problem), it would be nice to make my script sensitive to only try and set identity for tables which have the identity.

Any ideas?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 20:31:07

I am not sure how you are using the dynamic SQL but maybe SCOPE_IDENTITY() can help your cause.

EDIT: How about IDENT_CURRENT?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-26 : 20:32:41

Thanks but I think that just returns the last id value for a given scope...I want to know wether the table contains the identity.

I'll try it out anyway...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-26 : 20:41:59

Look at autoval in syscolumns

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 20:48:22

Nice nr!


select autoval 
from syscolumns 
where id in 
	(
	select id 
	from sysobjects so
	where name like <tablename>
	)
and autoval is not null

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-26 : 20:55:43

[code]
IF OBJECTPROPERTY(OBJECT_ID(<tablename>),'TableHasIdentity') =1
PRINT 'Has Identity'
ELSE
PRINT 'No Identity'
[/code]

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 20:57:59

Ooooohhhhhhh.....

OBJECTPROPERTY()

Me likey!!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-26 : 22:03:44

You are (as I have often said - and feel no apprehension in restating) beautiful and your mothers (collectively) love you (respectively).

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-26 : 23:04:56

Hey Dave

Sorry to show my ignorance once again, but when I try
IF OBJECTPROPERTY(OBJECT_ID(tALL_ACCOUNT_SELECTIONS),'TableHasIdentity') =1 SET IDENTITY_INSERT TMS.dbo.tALL_ACCOUNT_SELECTIONS ON

I get
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'tALL_ACCOUNT_SELECTIONS'.

What am I doing wrong?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-26 : 23:06:57

Ah - sorry - got it. Put quotes around the tablename. woops.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources