2

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.

KingKerosin
  • 3,639
  • 4
  • 38
  • 77
  • I don't know the cause - but maybe just create a dummy sproc just to make it happy? – Marc Gravell Feb 13 '11 at 18:10
  • For other sprocs it works as it should. So it's not a problem with sprocs at all. I can't figure out where SQLMetal gets the information about a sproc that isn't existing anymore. So if i can find the place where "**GetGameDatesDesc**" is written down i could delete this entry and SQLMetal will not throw an error... – KingKerosin Feb 13 '11 at 18:33

2 Answers2

0

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.

vk_muse
  • 604
  • 3
  • 6
  • 14
0

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.

Nicolas Buduroi
  • 3,565
  • 1
  • 28
  • 29
  • Am i right, that SQL Server will delete also all dependend entries in `sys.sql_modules`, or will i have to do this myself? – KingKerosin Feb 13 '11 at 19:03
  • Yes, it uses the object_id to delete the corresponding sys.sql_modules row. – Nicolas Buduroi Feb 13 '11 at 19:06
  • Kay. Thanks. I could try this tomorrow. So, if there are another errors, i will report. And, if this solves the problem, i will post it here anyway ;) – KingKerosin Feb 13 '11 at 19:07
  • I looked at this view but there is no entry for the old sproc. Any other ideas where SQLMetal is getting the information about the non-existing sproc? – KingKerosin Feb 14 '11 at 07:32