Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    27

    Question Unanswered: problem with linked servers and INSERT

    Hello

    I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.

    Here's the code:

    ---------------------

    create table #b (type int,label varchar(100),x int,y int,so int)

    declare @arg nvarchar(100)
    set @arg = 'BLANKET.MaalGrupper.ID38'

    declare @tsql varchar(1000)
    select @tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( ''''' + @arg + ''''' )'' )'
    insert #b exec (@tsql)

    drop table #b

    -------------------------

    I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.

    Can anybody help with this very irritating problem?

    Thanks
    MNJ

  2. #2
    Join Date
    Aug 2003
    Posts
    27
    doesn't anybody have an idea of might be wrong?

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Below is working for me (SQL2000)

    -- on linked server
    CREATE FUNCTION getit (@id int=null)
    RETURNS TABLE
    AS
    RETURN (SELECT *
    FROM sysobjects WHERE id = coalesce(@id,id))
    go
    select * from getit(null)
    ------------------------------------------
    create table #b (label varchar(100))
    insert #b
    select * from openquery(linked,'select name from testDB.dbo.getit(null)')

    declare @tsql varchar(1000)
    select @tsql = 'select * from openquery(linked,''select name from testDB.dbo.getit(null)'')'
    insert #b exec (@tsql)

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Do you have Distributed Transaction Coordinator running on both machines?

  5. #5
    Join Date
    Aug 2003
    Posts
    27
    Hm, the first select works for me, the second doesn't. And I need to be able to specify a parameter.

    DTC is running on both machines.

    MNJ

  6. #6
    Join Date
    Aug 2003
    Posts
    27
    Have you set any special environment variables or anything like that?

    MNJ

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Quote Originally Posted by kilobyte
    Have you set any special environment variables or anything like that?

    MNJ
    Nothing special...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •