Management Studio + Generate Script = Funny Error

I just tried to script a database that I am working on, when Management Studio gave me this fine error message:

- Determining objects in database 'mydatabase' that will be scripted. (Error)
An item with the same key has already been added. (System.Windows.Forms)

The Progress window lists all database objects as Stopped, and it is impossible to figure out exactly which object causes to error message.

Checking the database for duplicate names (unlikely, but just to be sure)

select name, count(object_id) from sys.objects
group by name
order by count(object_id) desc

did not show any name occurring more than once, of course.

The most annoying point is, that the error is not raised because of technical problems (meaning, invalid information in the database (hopefully!)), but by an exception raised by System.Windows.Forms because a ListBoxItem (or whatever) violated some fancy UI constraint that has nothing to do with the task to be performed.

The internet knows nothing about this.

Update: Here’s the solution

In the Generate Script Progress dialog, click the Report button and select Copy Report to Clipboard. Open your favorite editor, paste the report, and replace every “- ” (minus space) by empty string.

Copy the whole thing, and paste into Excel. (You cannot paste directly, because the leading minus in every line makes Excel think it’s a number, and gives you a funny #error in every cell).

Sort the values, and find the duplicate value.

Locate the object in the Object Explorer, right-click to generate a CREATE script of the object in a new window. Then delete the object. (In my case, the offending object was a synonym, which is not so painful to delete and recreate, as might be other object types).

Try to generate the database script again. This should now succeed.

Run the object’s CREATE script.

Generate the database script again, this should also work now. (well, it worked for me)

3 thoughts on “Management Studio + Generate Script = Funny Error

  1. Seems to be a bug with sql server? I had the same problem with a database of around 4000 tables. The list taken from the error report didnt return any duplicates. One work around I tried was to generate the script again, deselecting the last table found in Stopped list. This time it stopped with the same error message but for a different table. I deselected this too, tried again but every time the error came up with new tables and finally I gave up. I couldnt find any thing wrong with those stopped tables and the problem still remains a mistery.

  2. Thnx. for me synonym might be the problem, so I selected every objects except synonym from my schema and exported (successfully). Then exported the synonyms alone (successfully).

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.