1

I have a simple query in a stored procedure that LEFT JOINs a local temp table with several tables across a linked server (the same linked server). This query is resulting in the following error:

Msg 3989, Level 16, State 1, Line 1
New request is not allowed to start because it should come with valid transaction descriptor.

The stored procedure is called by a .NET application. The temp table is populated with values from an OPENJSON operation to deserialize a NVARCHAR(MAX) input parameter. The deserialization works just fine and the temp table is populated with the correct values at the time the problematic query begins execution.

Here is the relevant code:

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (
    Balances VARCHAR(255),
    [Cost Centers] VARCHAR(255),
    Days VARCHAR(25),
    Measures VARCHAR(255),
    Operations VARCHAR(255),
    Paycodes VARCHAR(255),
    Periods VARCHAR(255),
    Products VARCHAR(255),
    [Reason Codes] VARCHAR(255),
    Scenarios VARCHAR(255),
    Data NUMERIC(18,6),
    ID VARCHAR(500)
);

INSERT #t
(Balances, [Cost Centers], Days, Measures, Operations, Paycodes, Periods, Products, [Reason Codes], Scenarios, Data, ID)
SELECT
Balances, [Cost Centers], Days, Measures, Operations, Paycodes, Periods, Products, [Reason Codes], Scenarios, Data, ID
FROM OPENJSON(@Data)
WITH (
    Balances VARCHAR(255) N'$.Balances',
    [Cost Centers] VARCHAR(255) N'$.CostCenters',
    Days VARCHAR(25) N'$.Days',
    Measures VARCHAR(255) N'$.Measures',
    Operations VARCHAR(255) N'$.Operations',
    Paycodes VARCHAR(255) N'$.Paycodes',
    Periods VARCHAR(255) N'$.Periods',
    Products VARCHAR(255) N'$.Products',
    [Reason Codes] VARCHAR(255) N'$.ReasonCodes',
    Scenarios VARCHAR(255) N'$.Scenarios',
    Data DECIMAL(18,6) N'$.Data',
    ID VARCHAR(500) N'$.ID'
);

SELECT *
FROM #t i
LEFT JOIN dbo.syn_adp_ds_scenario s ON i.Scenarios = s.Scenario
LEFT JOIN dbo.syn_adp_ds_scenario sa ON i.Scenarios = sa.Alias
LEFT JOIN dbo.syn_adp_ds_period per ON i.Periods = per.Period
LEFT JOIN dbo.syn_adp_ds_balance b ON i.Balances = b.Balance
LEFT JOIN dbo.syn_adp_ds_product p ON i.Products = p.Product
LEFT JOIN dbo.syn_adp_ds_product pa ON i.Products = pa.Alias
LEFT JOIN dbo.syn_adp_ds_costcenter c ON i.[Cost Centers] = c.CostCenter
LEFT JOIN dbo.syn_adp_ds_costcenter ca ON i.[Cost Centers] = ca.Alias
LEFT JOIN dbo.syn_adp_ds_paycode py ON i.Paycodes = py.Paycode
LEFT JOIN dbo.syn_adp_ds_paycode pya ON i.Paycodes = pya.Alias
LEFT JOIN dbo.syn_adp_ds_reasoncode rc ON i.[Reason Codes] = rc.ReasonCode
LEFT JOIN dbo.syn_adp_ds_reasoncode rca ON i.[Reason Codes] = rca.Alias
LEFT JOIN dbo.syn_adp_ds_operation op ON i.Operations = op.Operation
LEFT JOIN dbo.syn_adp_ds_account a ON i.Measures = a.Account
LEFT JOIN dbo.syn_adp_ds_account aa ON i.Measures = aa.Alias
LEFT JOIN #day da ON i.Days = da.Day;

(Note the SELECT * is for debugging purposes, the production query specifies columns but the error does exist in either situation. Also, the JOINS as shown use synonyms to the linked server objects but the error still exists if I use the fully qualified server.database.schema.object notation.)

I have seen several posts about a similar error being resolved by updating .NET in the application or setting something additional in the connection string but I can reproduce the error in SSMS independent of the application code.

I've seen several other posts where others have run into this due to time outs on the remote side so I have tried rebuilding all indexes for all tables and updating statistics on all tables in the remote database.

I have also tried creating a temp table for one of the JOINs, populating it with a separate query across the linked server and then using the temp table in the JOIN instead of using the linked server directly. This did not help either.

Something else I found is that if I reduce the number of JOINs in the query I do not get the error. If I comment out 3 of the JOINs the query returns successfully with the correct results. It also does not matter which JOINs I comment. I can comment out any of them and as long as there are no more than 13 JOINs the query returns.

The production query will run in a transaction which I originally thought might have something to do with it. I do have XACT_ABORT set to ON, but I commented out the transaction and the error persists.

I use this same pattern in several other stored procedures and they are all working fine with as many as 20 JOINs. The only difference other than the specific tables is that all of stored procedures that work fine operate solely on local databases. The one with the error is the only one that uses a linked server and that is the only difference in the stored procedure.

Has anyone seen this before? Is there a linked server or provider limitation here that I'm not aware of? Or is there a setting I can configure or tune?

Local server is SQL Server 2017 Enterprise on Windows Server 2012 Standard
Remote server is SQL Server 2017 Standard on Windows Server 2016 Standard

Linked Server Config:

  • Product = SQL Server (Microsoft OLE DB Provider for SQL Server)
  • Collation Compatable = false
  • Data Access = true
  • Dist = false
  • Pub = false
  • RPC = false
  • RBC Out = false
  • Sub = false
  • Connect Timeout = 0
  • Collation Name = NULL
  • Lazy Schema Validation = false
  • Query Timeout = 0
  • Use Remote Collation = true
  • Remote Proc Transaction Promotion = true

EDIT
I was able to work around this for now by removing the redundancy in the JOINs (when having 2 JOINs to the same table). This reduced the number of JOINs and it's now working. But we make heavy use of linked servers so if anyone does know the answer here I'd still love to see. I am going to try and see if the issue is maybe locks on the same table from multiple JOINs to the same table or something like that.

1 Answer 1

0

I had similar problem and worked around it by using TSQL OpenQuery instead.

See also Linked Server in SQL Server, the good, the bad, and the way to make it perfect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.