Post reply

INSERT INTO... EXEC() against linked server

  • 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

  • July 13, 2005 at 6:17 pm

    #574210

    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.

  • July 13, 2005 at 6:22 pm

    #574211

    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

  • July 13, 2005 at 8:53 pm

    #574224

    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

  • July 14, 2005 at 11:52 am

    #574505

    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

  • July 15, 2005 at 8:14 am

    #574852

    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.

  • July 15, 2005 at 11:29 am

    #574976

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

    Igor

     

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

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