An explicit value for the identity column

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 2008 Forums

Transact-SQL (2008)

An explicit value for the identity column

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-08 : 05:44:47

Dear All

I have Skeleton SP as below, but it's raised me error

An explicit value for the identity column in table 'DB.dbo.xxx' can only be specified when a column list is used and IDENTITY_INSERT is ON.

My SP allow flexible TableName. I can't defined column list at select * from ...

Please advise.

SET @SQLString = N'SET IDENTITY_INSERT '+@DatabaseName+'.dbo.'+@tblName+' ON '
SET @SQLString = @SQLString +'INSERT INTO '+@DatabaseName+'.dbo.'+@tblName+' '
SET @SQLString = @SQLString +'SELECT * FROM '+@DatabaseName+'.dbo.'+@tblName+'Delete where '+@PK+' in ('+@OldValue+') '
SET @SQLString = @SQLString +'SET IDENTITY_INSERT '+@DatabaseName+'.dbo.'+@tblName+' OFF'
EXEC (@SQLString);

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-08 : 07:57:47

Thank you.

I solved it.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71157%2C

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:31:29

make sure you do sufficient validations to avoid sql injection attacks using below type of logic. I hope its not passed as such from some front end app

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources