• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

SQL DMO - deleting indexes - cant delete DRI Generated

Hi sql heads - I'm itterating through tables using sqldmo (in vba) and then itterating through indexes on that table and issueing a remove. I have an indexes it refuses to delete, instead giving an error (I didnt write number  down, but can get on request) saying that it can delete a "DRI generated" unique contraint or primary key. An interesting thing to note is that this index is of type 18450, where as most of the other indexes are of a type that can be found on the SQLDMO_INDEX_TYPE index constants page in the books online.

anyhow, is there any way I can delete these with sqldmo?
thanks
0
valhallatech
Asked:
valhallatech
  • 4
  • 4
1 Solution
 
imran_fastCommented:
These indexes are constraint primary keys. You cannot use same satement which you are using to delete indexes in these case.
0
 
imran_fastCommented:
if you are using .Indexes property of the object use .keys property.
0
 
valhallatechAuthor Commented:
hmm... I thought of that, but the .keys collection was empty, so there was nothing to delete. Though I'll go and look at this again and double check.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
valhallatechAuthor Commented:
hi imran_fast
you're right - at least partly. there is a key - so I used .keys.remove, but this still didnt stop the error (-2147201005) from occuring, and so I still cant delete that index. I also executed a .primarykey.remove, but that still doesnt allow me to delete the index wiht out getting the error:
-2147201005
"[SQL-DMO] This index was generated by a DRI 'PRIMARY KEY' or 'UNIQUE KEY' CONSTRAINT and cannot be modified or droped" when I try the .index(x).remove (having already done .key(x).remove)

where to from here?
0
 
imran_fastCommented:
Create a Key object.
Set the Type property to SQLDMOKey_Primary.
Add the Key object to the Keys collection of a Table object that exposes the attributes of the SQL Server table.
then use the remove property to delete it.


0
 
valhallatechAuthor Commented:
just to confirm - when you say 'then use the remove property to delete it.' - you mean the index, not the new key?
0
 
imran_fastCommented:
yes to remove  the primary key index
0
 
valhallatechAuthor Commented:
bingo - thankyou
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now