Monday, September 12, 2011

SQL 2008 - Linked Server: Msg 7391, Level 16, State 2, Line 5

Trying to leverage a linked Server and received the following error:

OLE DB provider "SQLNCLI10" for linked server "PANEL_REMOTE" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "PANEL_REMOTE" was unable to begin a distributed transaction.

-------------------
Verify RPC and RPC out is set to True in the Linked Server properties.

Code Snippet was:

I had declared the table variable @temp2 (yada)

declare @dynamic_sql NVARCHAR(MAX)
declare @prefix NVARCHAR(MAX)
SET @prefix = N'LinkedServerName.'
SET @dynamic_sql = N'' + CHAR(13) +
' INSERT INTO @temp2 EXEC ' + @prefix + 'DBName.dbo.StoredProcName '
exec (@dynamic_sql)

Still received the error message Msg 7391, Level 16, State 2, Line 5

-------------
Changed the call to use OpenQuery...
declare @dynamic_sql NVARCHAR(MAX)
SET @dynamic_sql = N'' + CHAR(13) +
' INSERT INTO @temp ' + CHAR(13) +
' SELECT * FROM OPENQUERY(LinkedServerName, ' + '''' + 'DBName.dbo.StoredProcName ' + '''' + ')'

And it worked successfully...

No comments:

Post a Comment