LINQ 2 SQL Nested table insert error gives no answer

Question

I have a LINQ method that I use to create an empty EmploymentPLan . After that I just UPDATE . For some reason this works fine for me, but for my users they get 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 Generated SQL below, which they have, in fact, contain INTO clauses. Wth ???

This is a WinForm application that connects to the local SQL 2005 Express database, which is part of the merge replication topology. This is an INTERNAL application that is installed only 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 consideration, I noticed that the previous decision was not really applied, since Auto-Sync was ALREADY installed for all identifier columns .

Does anyone have any other ideas? Thanks

---

It is generated SQL. I know this is a long time, but I'm just at a loss. This ALL is generated by one of the methods described above.

 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

Does this apply to you?

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

FIX: error message when starting DML in a LINQ to SQL application in SQL Server 2008: "Target table '' DML expressions cannot have triggers activated if the statement contains OUTPUT without an INTO clause"

Answer 2

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

• Are your users using your db, or is it installed locally? Perhaps they are using SQL Server 2008 (or R2), and maybe something has 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 has no triggers?
• Do they have replication enabled? (which can work through triggers) Indexed views? Markup? etc.

Answer 3

There is a trigger in your database that prevents the OUTPUT from generated sql from being offered.

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

To do this, set the AutoSync property for each table column 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 this, a new problem arises. Your method wants to return the generated identifier, but the generated identifier will no longer be automatically selected for you. You must change the method to the query for the newly inserted record.