September 26, 2008 at 9:01 am
I want to execute a stored procedure on a linked to sql server and insert the results into a table.
Example:
INSERT INTO LocalTableName
EXEC [LinkedToServer].[DatabaseName].[dbo].[StoredProcedureName]
This does not work. The error I get is:
OLE DB provider "SQLNCLI" for linked server "LinkedToServer" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedToServer" was unable to begin a distributed transaction.
Strangely I can execute the stored procedure just fine and get back the correct results:
EXEC [LinkedToServer].[DatabaseName].[dbo].[StoredProcedureName]
I can also insert whatever I want into any table in my database. However, I cannot insert the results of the linked to servers stored procedure.
I've found some others with problems like this on the web but they are saying I have to allow inbound connections in the Microsoft Transaction Coordinator. I checked the production server and this is already enabled.
Any ideas?
September 26, 2008 at 11:49 am
Check the RPC settings on both machines.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2008 at 12:06 pm
Check MSDTC settings on both computers.
When you are running INSERT from one server to another this is considered as DTC (distributed transactions).
You can use this tool: http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&displaylang=en
September 26, 2008 at 12:46 pm
Thanks to both of you, that was the solution to my problem. I failed to enable outbound transactions on the server I am pulling the data into.
September 26, 2008 at 2:31 pm
Glad you resolved it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply