1

I have renamed a table in a SQL Server 2008 database, from eL_CourseStepUserNotes to StepUserNotes. I renamed the table using the SSMA.

The table is used in a ASP.NET 4.0 app. I use LINQ to SQL for all CRUD. The problem is that the following code:

    dbDataContext db = new dbDataContext();
    var k = (from c in db.StepUserNotes
             where ((c.CourseStepFK == q.CourseStepFK) && (c.UserFK == q.UserFK))
             select c).FirstOrDefault();
    try
    {
        db.StepUserNotes.InsertOnSubmit(q);
        db.SubmitChanges();
    }
    catch
    {

    }

Fails on the db.SubmitChanges line, saying:

SqlException was caught. Invalid object name 'eL_CourseStepUserNotes'.

Ie, the old name for the table has come back to haunt me.

I have deleted the old LINQ to SQL dbml file and created a new one. I have searched through all the source code for strings that contain the old table name. Nothing. The code compiles...

Where else can I look?

The error is coming back from SQL Server, and using the utility for listing all foreign keys in a sql server database shown in SO question: sql:need to change constraint on rename table?

reveals no sign of the old table name in FKs either.

I am at a complete loss as to where to look or what to try next. Any suggestions?

Community
  • 1
  • 1
awrigley
  • 13,481
  • 10
  • 83
  • 129
  • 1
    Any chance a trigger or something in the SQL is referencing the old table? – Sparky Nov 15 '10 at 18:08
  • 2
    Just for fun, run a Select Object_Name(ID) From SysComments Where Text Like '%el_CourseStepUserNotes%' – Stu Nov 15 '10 at 18:18
  • @Stu + @Sparky: Thanks for that!!! Nailed it: the problem was a trigger to set a field called "DateAmended". Why the trigger wasn't updated after the name change defeats me. – awrigley Nov 15 '10 at 20:13
  • Triggers, stored procedures and functions aren't automatically updated. Think it through: how would you write such functionality? – Stu Nov 16 '10 at 16:01
  • @Stu: Thanks, a lesson well learnt. – awrigley Nov 16 '10 at 16:22

5 Answers5

3

Answer:

The problem, as stated by Stu and Stark was a trigger. Stu gave me the SQL to run that nailed the problem. It is documented here in case anyone else runs into this:

Select Object_Name(ID) From SysComments 
       Where Text Like '%el_CourseStepUserNotes%'

This revealed a trigger with the following name:

tr_eL_CourseStepUserNotes

The trigger referenced the old name as follows: 

SET DateAmended = CURRENT_TIMESTAMP
     FROM eL_CourseStepUserNotes PP  
            INNER JOIN inserted i  ON PP.UserNoteId = i.UserNoteId

Everything is now working again.

Silly me, I should have realised that a trigger was the problem as the first error I got was related to the DateAmended field.

I have no idea why a trigger would update when a table name changed. I had checked all Keys and relationships, but forgot this trigger.

Live and learn.

awrigley
  • 13,481
  • 10
  • 83
  • 129
0

When you recreated your linq to sql file, did you remember to refresh the server browser to reflect the table name change first?

Edit - I mean the visual studio server browser.

asawyer
  • 17,642
  • 8
  • 59
  • 87
0

you need to rebind your LINQ To SQL class, drop all the tables, then add them again from the Server Explorer

bevacqua
  • 47,502
  • 56
  • 171
  • 285
0
  1. open your project in visual studio.
  2. open your dbml file.
  3. find the table you just have renamed.
  4. select and delete it.
  5. Save.
  6. Open server Explorer.
  7. Connect to your database.
  8. Find the table which you have renamed.
  9. Drag and drop on the designer of your dbml file.
  10. Save the file again.
  11. Compile your project.
0

It is not necessary to drop the tables from your DBML file. You can simply open the designer, click on the class diagram representing your table and type the new name of the table.

However, if you're recreating from scratch it is important to refresh in Server Explorer otherwise it will still pull the old name from the schema.

jason
  • 236,483
  • 35
  • 423
  • 525
  • Did that too, as implied in the question. Having searched for the string, the old name would come up. – awrigley Nov 15 '10 at 20:07