Create temp table object across linked servers

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  November 10, 2011 at 7:06 am

  #251530

  Hi,

  I am running a SSRS program that selects data across 2 linked servers.

  The process has to be completed in 2 stages otherwise I get a 'NT AUTHORITY\ANONYMOUS LOGON' message.

  SSRS server = Server001

  Linked SQL servers = Server002 & Server003

  So I have split the T-SQL between 2 separate stored Procedures. In the 1st process I am creating a temp table (#tmpMarketPrices) on Server002 which holds the selected values for the SSRS report. Next I need to update this table with information on Server003. At this stage I need to replicate this temp table on this 2nd linked server (the temp table should still be unique to the session). How do I achieve this?

  Any ideas?

  Thanks in advance.

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  November 10, 2011 at 8:14 am

  #1405798

  Do it in a stored procedure would be my first guess.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  November 11, 2011 at 1:48 am

  #1406114

  DerbyNeal (11/10/2011)

  ---

  Hi,

  I am running a SSRS program that selects data across 2 linked servers.

  The process has to be completed in 2 stages otherwise I get a 'NT AUTHORITY\ANONYMOUS LOGON' message.

  SSRS server = Server001

  Linked SQL servers = Server002 & Server003

  So I have split the T-SQL between 2 separate stored Procedures. In the 1st process I am creating a temp table (#tmpMarketPrices) on Server002 which holds the selected values for the SSRS report. Next I need to update this table with information on Server003. At this stage I need to replicate this temp table on this 2nd linked server (the temp table should still be unique to the session). How do I achieve this?

  Any ideas?

  Thanks in advance.

  Why do you have a linked server that's using windows security? It's not much good if you don't have Kerberos setup correctly.

  Options 1 and 2 are viable, take 3-6 with a grain of salt.

  1. Configure Kerberos Delegation to eliminate the NT AUTHORITY\ANONYMOUS LOGON errors (not difficult). --Recommended

  2. Configure the linked server using a sql login (or add an aliased connection using a sql login). (easy)

  3. Replicate data from Server003 to Server002 ahead of time. (more work)

  4. Use Service Broker to send the data in #tmpMarketPrices to Server003, process it and send it back (overly complex)

  5. clr stored proc that opens a connection to Server003 and retrieves the data

  6. xp_cmdshell to fire off a ssis package that moves the data over, executes the sp, and moves it back (sometimes you *have* to use xp_cmdshell I wouldn't publicly admit to using this option if I were you.)

  Kerberos is your answer if all the users running reports can connect using windows authentication to all three servers. If that's not the case, then you'll need to use a sql login on Server003.