Post reply

Insert into problem with linked server

  • July 7, 2005 at 4:39 am

    #66722

    Hi.

    I have a stored procedure on a remote server that performs a single select. When I execute the following in the query analyzer, everything works, and the result can be viewed.

                       exec linkedserver.dbname.dbo.procname

    But when I create a table with columns matching that of the stored procedure select, and then execute the following

                       insert into tablename exec linkedserver.dbname.dbo.procname

    the query analyzer hangs. I have tried to run it in a distributed query, but the result is the same.

    Any suggestions?

    Thanks in advance.

     

     

  • July 7, 2005 at 5:42 am

    #571711

    I'm not sure I understand exactly what you want to do but exec stored procedure and insert into are 2 separate statements....

    Your stored procedure only selects....can you modify it so that it selects the rows and inserts it into the table that you have created ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 7, 2005 at 6:16 am

    #571717

    My first reply disappeared - maybe it will be back later...

    It is possible to combine "insert into" and "exec" as follows (a simple example):

    create table testtable (id int)

    go

    create proc testproc

    as begin

            select 1

    end

    go

    insert into testtable exec testproc

    select * from testtable        /* result 1, i.e the output of testproc */

     

    My problem is that when the stored procedure is accessed via linked server, the corresponding "insert into table exec linkedserver.dbname.dbo.procname" query never times out.

    To answer your second question, I would like to avoid changing the stored procedure, since I am not responsible for the remote server.

  • July 7, 2005 at 6:25 am

    #571722

    you cannot combine the two....

    one way of doing this would be to save the results of your stored procedure query in a file (could be .txt, excel etc...) and then import the results from the file into your table.







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 7, 2005 at 6:44 am

    #571733

    Did you run my example on your server? It runs on my server...

    All I can say is that I have (mis)used the "insert into exec" feature (without the linked server) for years and that I am not the only one, so of course the two can be combined - at least on my servers

  • July 7, 2005 at 7:18 am

    #571751

    Here's one suggestion - Have you looked into Openrowset function to see if you could use that maybe ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 7, 2005 at 7:49 am

    #571776

    That might work, I haven't tried it. I was hoping that I could use linked server since it is more elegant and probably also faster.

  • July 10, 2005 at 10:06 am

    #572735

    Jesper - I'm curious - did you get this to work & if so could you please post the solution ?!

    I was just wondering if openquery was not the right solution for this ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 10, 2005 at 6:02 pm

    #572756

    Are either of the servers Win 2003 OS?  I've hit this same type of issue due to the default DTC settings on Win 2003. 

    The straight

    exec linkedserver.dbname.dbo.procname

    is a Remote Procedure Call that does not invoke a transaction, but the insert into a local table from a remote stored procedure does implicitly use a distributed transaction.  Windows 2003 modified the security settings for these (see:  http://support.microsoft.com/default.aspx?scid=kb;en-us;817064)

    You (or a Windows admin) should navigate to the following:

    • Start / Program Files / Administrative Tools / Component Services (or follow the path in the KB article above)
    • Expand the Component Services node until you reach "My Computer"
    • Right-click on My Computer and select Properties
    • Go to the MSDTC tab and select "Security Configuration"

    For Windows 2003 RTM (not SP1) , verify that "Network DTC Access" is selected.  You may also need to select "Network Administration", "Network Transactions", and "Network Clients".   For Win 2003 SP1, "Network DTC Access" must be selected.  Under Transaction Manager Communication, I've selected "Allow Inbound" and "Allow Outbound" and the radio button "No Authentication Required".  The latter was necessary for us because we have Win 2000 machines as well.  You might want to check Microsoft Knowledge base for details of DTC security on Windows 2003 to verify these settings are reasonable for your environment.

    Also verify that the DTC Logon Account has network privileges.  The account NT AUTHORITY\NetworkService has priviliges; LocalSystem does not.

    At this point, stop and start MSDTC.  I think you also need to stop/start SQL Server service as well. 

    You may need to do the above on each Win 2003 server. 

    After that retry

    insert into tablename exec linkedserver.dbname.dbo.procname

    and see if it works.  Let us know.

     

    Hope this helps,

    Scott Thornburg

  • July 11, 2005 at 6:11 am

    #572841

    Scott, Thanks for an excellent suggestion. Unfortunately both servers are Win 2000. But I have a feeling that we are getting closer...

    Sushila, Openquery works... Unfortunately I cannot use it, since my local database must be on 65 and not 80 compatibility level (right click on database in enterprise manager, choose properties, options tab to set compatibility level). I could of course add a 80 compatibility level db and execute my query there (using openquery), but I am hoping for a nicer solution.

    Thanks for the effort, guys. Other suggestions are more than welcome...

     

  • July 11, 2005 at 2:24 pm

    #573119

    Do you have any firewall in between the servers?

     


    * Noel

  • July 11, 2005 at 2:37 pm

    #573127

    what happens when you use something like:

    @declare @stm = 'exec linkedservername.dbname.dbo.procname'

    create table #T(fld1 int, fld2 ....)

    insert into #T(fld1,fld2,...)

    exec(@stm)

     


    * Noel

  • July 12, 2005 at 2:28 am

    #573265

    Same problem with dynamic execution - and there is no firewall between the servers, I've been told (I have tested that port 135 is open).

     

  • September 16, 2005 at 5:17 am

    #590497

    Hi,

    I have the same problem ..

    trying to call a SP from linked server , 

    I want to  insert select results into a local table ...

    Getting the following error:

    Server: Msg 7391, Level 16, State 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].

     Does  any one have a solution?

    (would be grateful for a suggestion...)

     

     

  • November 8, 2005 at 4:56 pm

    #602928

    Did you try to insert:

    SET XACT_ABORT ON

    just before you execute your INSERT INTO... EXEC statement?

     

     

Viewing 15 posts - 1 through 14 (of 14 total)

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