SQLMetal fails to create DBML from SQL Server 2005 database

Question

As the title already says, am I trying to create an dbml-file by using SQLMetal. The background for this action is to create a C#-Codefile which contains all tables, function, views, stored procedures and so on for using it in an MVC-Application.

Now to the problem. When i am starting SQLMetal from command it takes so time, shows some minor warnings and breaks in an Error (DBML1057). As I read the error-message, SQLMetal is trying to extract a stored procedure, that is not existing. I looked at the database a dozen times but could definetly not find such a procedure. Maybe this procedure was called so long before. i.e. you create a SP named "GetGameDatesDesc" and later you rename it to "GetGameDates". SQLMetal (in my case) is looking for "GetGameDateDesc" I think. But where has SQLMetal this information from? I can't find a word in database. I looked over all views, functions,... bot no avail. This function isn't part of the database anymore.

Edit -------------- 02/14/2011 --------------

Here are the warnings from SQLMetal:

Error DBML1042: The member attribute 'CCode' in the Column element of the Type element 'GetGameDatesDesc' has been used already. Error DBML1057: The storage attribute or its default value '_CCode' in the Column element of the Type 'GetGameDatesDesc' has been used already.

Answer 1

It looks that there is some error in one of your existing stored procedures. Maybe you delete or rename some stored procedure, but some another stored procedure still have a call of that deleted or renamed. Try to generate SQL script that creates all the stored procedures using SQL Management Studio (Right mouse click on database -> Tasks -> Generate Scripts) and search in generated script stored procedure name that causes error.

Answer 2

This is a unfortunate limitation of SqlMetal. As the SQL Server information_schema has a limit of 4000 characters on the SP code, SqlMetal is using the sys.sql_modules view to look for SPs. But that view isn't updated by the sp_rename function, so if you rename a SP, SqlMetal will not work correctly. The recommendation found in sp_rename documentation is to drop and recreate these kind of objects:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

The solution would be to drop that SP and recreate it as SQL Server will use the object_id to delete the corresponding sys.sql_modules row.