SQL Server Performance Forum – Threads Archive
New transaction cannot enlist in the specified tra
I get this error on a Insert Statement. I have tried to simplify this example. I am trying to insert into a temp table from a EXEC statment. Here is the SQL statement: DROP TABLE #tblReelsCREATE TABLE #tblReels( REELTAG Int,
TAPPI Char(16),
SPOOL Int
)
SET @pvc_LinkedServer = ‘LNKSERVER’
SET @vc_OpenQuery = ‘SELECT * FROM OPENQUERY(‘+ @pvc_LinkedServer + ‘,”’
SET @vc_End = ”’)’
SET @vc_FullQuery = @vc_OPENQUERY + ‘SELECT * FROM LinkCatalog.dbo.REELS WHERE REELTAG > 117876’ + @vc_End INSERT into #tblReels
EXEC (@vc_FullQuery) As you can see @vc_FullQuery is a linked server. When I remove the Insert statement the query returns all of the rows from the linked server, so I know the select statement works. I can also get this to work by hard coding the Select statement after the Insert. Another interesting thing is that this code did work the first time I tried it and has worked when I switch to a different network (i.e. Office, Home and Client Network) the query worked the first time but now I can’t get it to work anywhere. The one variable that may help is that I am using a VPN connection the clients site where the production database resides. Any suggestions? Gerry Whitworth P.S. I found an interesting post herehttp://blogs.msdn.com/nihitk/archive/2004/02/14/72904.aspx they talk about opening up some ports but do not tell which ports. I tried guessing and it did not make a difference.
Be sure that the DTC service is enabled for network access. Depending on the OS version and the OS installation the DTC could be running but without network access. Review information from this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;329332 article.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>