0

I need to update a table in the linked server in the procedure, but I could not succeed.

The SELECT statement succeeds, but I cannot UPDATE or INSERT.

This procedure worked in the web application(ASP) and in SQL Server Management Studio:

ALTER PROCEDURE sp_SELECT 
    (@ID BIGINT)
AS
    SELECT FIRM_NAME 
    FROM [8.0.0.11\SQL2].MyDatabase.dbo.SYST_FIRM 
    WHERE FIRM_ID = @ID
GO

This procedure did not work in the web application(ASP), but it did work in SQL Server Management Studio:

ALTER PROCEDURE sp_UPDATE 
    (@ID BIGINT,
     @NAME_NEW NVARCHAR(50))
AS
    UPDATE [8.0.0.11\SQL2].MyDatabase.dbo.SYST_FIRM 
    SET FIRM_NAME = @NAME_NEW 
    WHERE FIRM_ID = @ID
GO

My connection string format in App:

string _cs = "Data Source={0};Initial Catalog={1};User Id={2};Password={3};Connection Timeout=300;";

Local firewall is off.

My linked server properties:

My linked server properties;

Note: the web application and SQL Server Management Studio connect to SQL Server with the same username.

Error:

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction.

I tried all messages in:

stackoverflow.com/questions/24014718/the-operation-could-not-be-performed-because-ole-db-provider-sqlncli11-for-lin

I'm sorry for my bad English.

I used Google Translate.

Thanks all.

VolkanCetinkaya
  • 645
  • 7
  • 13
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Apr 08 '22 at 21:04

1 Answers1

0

I couldn't find any answer. I give up.

Thanks to everyone who took the time to read.

VolkanCetinkaya
  • 645
  • 7
  • 13