INSERT INTO... EXEC() against linked server

Question

INSERT INTO... EXEC() against linked server

imarchenko-236376

SSCommitted

Points: 1734
More actions
July 13, 2005 at 5:17 pm

#167877

Hello!

I am having problem executing dynamic queries against linked server. Below is a code sample example I trying to execute:

create table #tmp
(name varchar(255)
)
insert into #tmp (name) select top 9 name from [LinkedServer].master.dbo.sysobjects -- works

insert into #tmp (name) exec('select top 9 name from [SAVVIS-Reporter].master.dbo.sysobjects') -- doesn't work

I am getting following error message:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Linked server name is supposed to be supplied through parameter.

I am not sure why EXEC() wouldn't work against linked server while executing statement itself works.
Any help would be greatly appreciated.

Igor

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ND-215452 SSCrazy Points: 2037 More actions · Answer 1

I've never tried insert and exec() in one statement before. But I think the problem is you're missing a 'select' statement.

insert into #tmp (name) exec('select top 9 name from [SAVVIS-Reporter].master.dbo.sysobjects') -- doesn't work

which looks something like this:

insert into #tmp

'sysobjects'

'syscolumns'

.......

cheers.

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 2

I figured out this problem. I had to enable MSDTC network access ,open port 135 and program msdtc.exe in Firewall on linked SQL Server (Windows 2003 SP1/SQL Server 2000 SP3a). Once I have done that, everything worked fine.

Igor

ND-215452 SSCrazy Points: 2037 More actions · Answer 3

Igor, my apology the query below should work. It was my bad I didn't drill deep enough.

insert into #tmp (name) exec('select top 9 name from [SAVVIS-Reporter].master.dbo.sysobjects')

Just one question, why don't you want to use this query (without the exec()) ? It would be much more simpler. Unless you want to put a string variable inside the exec().

insert into #tmp (name) select top 9 name from [LinkedServer].master.dbo.sysobjects -- works

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 4

You are right,the reason why I am using dynamic string is because name of linked server supplied as a parameter to the stored procedure.

Regards,

Igor

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

Assuming Distributed Transaction Controller service is running on both machines, there still may be configuration issues. Permissions may be denied at various stages, such as a firewall blocking port 135 or MSDTC service is not allowed to do network transactions.

Look up Microsoft articles 873160 and/or 839279 for more details.

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 6

Thanks,Scott. Firewall was the reason. I have already posted my reply above

Igor