LINQ 2 SQL Nested Table Insert Error makes NO SENSE

Question

I have the below LINQ method that I use to create the empty EmploymentPLan. After that I simply UPDATE. For some reason this works perfectly for myself but for my users they are getting the following error -->

The target table 'dbo.tblEmploymentPrevocServices' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The strangest part of this is that you can see the the below Generated SQL that they DO, in fact, contain INTO clauses. WTH???

This application is a WinForm app that connects to a local SQL 2005 Express database that is a part of a Merge Replication topology. This is an INTERNAL App only installed through ClickOnce.

---

public static Guid InsertEmptyEmploymentPlan(int planID, string user)
    {
        using (var context = MatrixDataContext.Create())
        {                               
            var empPlan = new tblEmploymentQuestionnaire
                                  {
                                      PlanID = planID,
                                      InsertDate = DateTime.Now,
                                      InsertUser = user,
                                      tblEmploymentJobDevelopmetService = new tblEmploymentJobDevelopmetService(),
                                      tblEmploymentPrevocService = new tblEmploymentPrevocService()
                                  };

                context.tblEmploymentQuestionnaires.InsertOnSubmit(empPlan);

                context.SubmitChanges();

            return empPlan.EmploymentQuestionnaireID;
        }
    }

---

After further review I noticed that the, previously, accepted solution did not truly apply as Auto-Sync was ALREADY set to never for all ID Columns.

Anyone have any other ideas? Thanks

---

He is the generated SQL. I know it is long but I am just at a loss. This is ALL generated by the single above Method.

DECLARE @output TABLE([EmploymentPrevocID] UniqueIdentifier)
INSERT INTO [dbo].[tblEmploymentPrevocServices]([AvgRatePay], [AvgHoursWeek], [SettingID], [PrevocGoal], [SkillsTaught], [SkillsLearned], [AnticipatedTransitionPlans], [AnticipatedEndDate], [RatioID])
OUTPUT INSERTED.[EmploymentPrevocID] INTO @output
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)
SELECT [EmploymentPrevocID] FROM @output
-- @p0: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p1: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p3: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p4: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p5: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p6: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p8: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.4926

DECLARE @output TABLE([JobDevelopmentServicesID] UniqueIdentifier)
INSERT INTO [dbo].[tblEmploymentJobDevelopmetServices]([TypeWorkDesired], [PreferredWorkHoursID], [NeedEmploymentServices], [DVRProvidingServices], [DVRCurrentReferral], [PaidCoachingHoursID], [PlanSegregatedToIntegrated], [RoleResponseJobDeveloper], [RoleResponseMember], [RoleResponseWWCTeam], [PlanDVRToWWCFund], [PlanReducePaidSupports])
OUTPUT INSERTED.[JobDevelopmentServicesID] INTO @output
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)
SELECT [JobDevelopmentServicesID] FROM @output
-- @p0: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p2: Input Bit (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p3: Input Bit (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p4: Input Bit (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p6: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p7: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p8: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p9: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p10: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p11: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.4926

DECLARE @output TABLE([EmploymentQuestionnaireID] UniqueIdentifier)
INSERT INTO [dbo].[tblEmploymentQuestionnaire]([PlanID], [CommunityJob], [PrevocServices], [JobDevelopmentServices], [PrevocServicesID], [JobDevelopmentServicesID], [InsertUser], [InsertDate], [UpdateUser], [UpdateDate], [TransitionedPrevocToIntegrated], [EmploymentServiceMatchPref])
OUTPUT INSERTED.[EmploymentQuestionnaireID] INTO @output
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)
SELECT [EmploymentQuestionnaireID] FROM @output
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [37017]
-- @p1: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
-- @p2: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
-- @p3: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
-- @p4: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [a3674e69-9b78-df11-b74e-001e0bd023bc]
-- @p5: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [a4674e69-9b78-df11-b74e-001e0bd023bc]
-- @p6: Input VarChar (Size = 9; Prec = 0; Scale = 0) [pschaller]
-- @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [6/15/2010 11:31:13 AM]
-- @p8: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p9: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p10: Input Bit (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p11: Input Bit (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.4926

Answer 1

Well, the error seems pretty clear, and makes sense. Some thoughts:

• are your users using your db, or is it installed locally? Maybe they're using SQL Server 2008 (or R2), and maybe something changed?
• are you sure your db setup is the same as theirs? maybe you have your own copy of the tables (SomeName\MyTable instead of dbo.MyTable), and your copy doesn't have the triggers?
• have they got replication enabled? (which can work via triggers) Indexed views? Partitioning? etc

Answer 2

Your database has a trigger that interferes with the OUTPUT clause of the generated sql.

Since the trigger is probably not something that can be removed, you need to instead stop the generated sql from having an OUTPUT clause.

To do that, set the AutoSync property of each column in the table to Never. This can be done in the LinqToSql designer. http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.autosync(v=VS.100).aspx

After doing that, there is a new problem. Your method wants to return the generated ID, but the generated ID is not longer automatically fetched for you. You must alter the method to query for the newly inserted record.

Answer 3

Does this apply to you?

http://support.microsoft.com/kb/961073

FIX: Error message when you run a DML statement in a LINQ to SQL application in SQL Server 2008: "The target table '' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause"