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 |
|
neo_6053
Starting Member
24 Posts |
 Posted - 2008-11-23 : 21:34:34
|
How can i delete the auto increment index with query? i want to do it cause i want to import data from ORACLE. bur SQL server set the value of the index column by auto increment.i tried this code:quote:
Declare @Index varchar(128)Declare @Table varchar(128)SelectSysIndexes.Name As 'Index1',SysObjects.Name As 'Table1'Into#IndexesFromSysIndexesInner Join SysObjects On SysObjects.id = SysIndexes.idWhereSysIndexes.Name Is Not Nulland SysIndexes.Name like '_WA_Sys_%'and SysObjects.XType = 'U'and SysObjects.name not in ('sysdiagrams','dtproperties')Order BySysIndexes.Name,SysObjects.NameWhile (Select Count(*) From #Indexes) > 0Begin Set @Index = (Select Top 1 [Index1] From #Indexes) Set @Table = (Select Top 1 [Table1] From #Indexes) Exec ('Drop Index [' + @Index + '] On [' + @Table + ']') Delete From #Indexes Where [Index1] = @Index and [Table1] = @TableEndDrop Table #Indexes
but if give me error Cannot drop the index 'Revenue._WA_Sys_00000001_09F52113', because it does not exist or you do not have permission. i wanted to delete all the indexes with prefix "_WA_Sys_" because when i delete the index manually from management studio, i found that the index with prefix "_WA_Sys_" is deleted from sysindexes. So, i guess it's the index for the identity auto increment.i login as SA but it still says i do not have permission. What's the problem? However, there is 1 condition though. I created the database and restore it with backup from other server. Does it matter? The user is different for both server. However, i did tried to add in the user but it still fail. How to check who created the indexes? How to check and add authorization for it? |
|
|
neo_6053
Starting Member
24 Posts |
Posted - 2008-11-23 : 22:47:48
|
| i think i might have gone the wrong way from the very beginning. Should i actually do like this ?http://www.mssqltips.com/tip.asp?tip=1397however, he also mention that "There are some other approaches that you can find on the internet that modify values in the system tables. These approaches do work, but if you make a mistake you could totally mess up your data, so make sure you understand what is in store before modifying system tables"i have about 400+ tables ... what can i do? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-24 : 00:45:04
|
If its just for one time export you dont have drop the auto increment property. you just need to set identity insert property before import and off it after. some thing likeSET IDENTITY_INSERT yourtable ON--your import codeSET IDENTITY_INSERT yourtable OFF |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-24 : 00:45:47
|
| and if you're using export import wizard you've a property called enable identity insert. just select it while configuring properties.Also dont use bigger & bolder fonts while posting questions |
|
|
|
neo_6053
Starting Member
24 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
How can i delete the auto increment index?