Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: How To Assign OPENQUERY Results from Linked Servers to local variables..

    Hi All,

    I have a problem about assigning the results of an OPENQUERY. Please check the code below:

    DECLARE @sqlString nvarchar(4000)
    DECLARE @sqlString1 nvarchar(4000)
    DECLARE @custName nvarchar(100)

    SET @custID = 2
    SET @sqlString1 = 'SELECT * FROM myTable WHERE CustID = ' + CAST( @custID as varchar(6))
    SELECT @sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
    EXECUTE(@sqlString)

    --
    The above code works fine but i need something like
    SELECT @sqlString = 'SELECT @custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
    EXECUTE(@sqlString)

    By doing this i want to assign the CustName to the local variable @custName.
    I tried to use a temp table but in that case i was not able to specify a where clause in @sqlString1. (i'd rather use linked_server.databasename.owner.tablename , but i read that this does not provide good performance )

    Any suggestions about the problem are welcomed!
    Thanks in advance,

    Bahtiyar KARANLIK

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    declare @sqlString nvarchar(1000)
    SELECT @sqlString = 'SELECT @custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
    EXEC sp_executesql @sqlString, N'@custname varchar(20) out', @custname out

    or

    SELECT @sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
    create table #a (s varchar(20))
    insert #a
    exec (@sqlstring)

    select @custname = s from #a
    drop table #a

    Using the 4 part name in the query should be just s good. If you are joining with strings then make sure the servers are collation compatible so that the filter is performed on the remote server and check the query plan.
    This sort of thing can give bad performance if used as a join to a local table but if you are just selecting using constants for a filter it should be OK.

  3. #3
    Join Date
    Feb 2011
    Posts
    1

    Post

    DECLARE @custName nvarchar(100)
    SELECT @custName = CustName FROM OPENQUERY(DEEPACCESS,'SELECT CustName FROM myTable WHERE CustID = 2')
    SELECT @custName as CustName

    Try it.

Posting Permissions

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