June 25, 2008 at 9:53 am
I'm running the following script against a linked server connecting 2 SQL-2005 instances and getting the error shown below:
CREATE TABLE #tbl
(
...
)
INSERT INTO #tbl
(
...
)
EXEC[LinkedServer].DB.dbo.sprocName
select * from #tbl
drop table #tbl
OLE DB provider "SQLNCLI" for linked server "server\instance" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 20
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedServerName" was unable to begin a distributed transaction.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 25, 2008 at 10:39 am
We figured it out.
The server name of the server on which the query was running was added to the hosts file of the target server (the server pointed to by the linked server), and now the query is running with no errors.
The 2 servers are not on the same domain, and the originating server was not pingable by name from the target server, hence the problem.
This link was very useful in troubleshooting this:
How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server
http://support.microsoft.com/kb/306212#appliesto
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 25, 2008 at 1:32 pm
LinkedServer properties:
ServerOption -> RCP = True?
June 25, 2008 at 2:03 pm
Federico del Blanco (6/25/2008)
LinkedServer properties:ServerOption -> RCP = True?
Yes, that was already in place.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply