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.
| Author |
Topic |
|
jlz
Starting Member
4 Posts |
 Posted - 2002-09-18 : 02:06:35
|
| Hi,I have a variable of datatype table, and want to reset the identity column so that it starts at 1 again. is this possible? I tried using DBCC CHECKIDENT (@myTable, RESEED, 1) but get the following error:"Must declare the variable '@myTable'." The variable has been declared, so i don't know why i'm getting that error.Can anyone help?thanks,JLZ |
|
|
Crespo
85 Posts |
Posted - 2002-09-18 : 06:14:26
|
| From what you say, there is no reason why you should get any errors. If the variable has been declared then it should work.I would re-run your code making sure that you execute the DECLARE section of your code. If that doesn't work, could you post that part of the code here?Good Luck!Crespo.Hewitt Bacon & WoodrowEpsomSurreyUnited Kingdom |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-18 : 12:26:19
|
| According to Books Online, DBCC CHECKIDENT accepts a parameter for a table name that must conform to the rules for identifier names. A table variable does not conform to those rules, therefore you won't be able to use it on a table variable. |
|
|
|
jlz
Starting Member
4 Posts |
Posted - 2002-09-19 : 20:20:30
|
| Thanks for your help :)JLZ |
|
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-09-20 : 08:07:09
|
| Not sure if this'll work for a table variable but the TRUNCATE TABLE command resets IDENTITY values. Of course this is assuming that you are working with a table that can be emptied/truncated.Good luck.Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-20 : 08:41:16
|
| On my system it works ok (v7sp2).Are you sure you are spelling @myTable correctly - capital letters ...You could trydeclare @cmd varchar(1000)select @cmd = 'DBCC CHECKIDENT (''' + @myTable + ''', RESEED, 1)'exec @cmd==========================================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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 08:42:51
|
| He was using a table variable, not a dynamic table name. |
|
|
|
|
|
|
|
|
reset Identity column for table variable