none
Accessing temp table across servers RRS feed

  • Question

  • I have stored proc - [Server1].[SourceDB].[ReportingProc] which is used to display the data on the report

    1. This procedure creates the local temp table #temp1

    2. Selects some data into temp table

    3. Calls the procedure of different database [Server1].[DestinationDB].SomeProcedure

    4. [Server1].[DestinationDB].SomeProcedure accesses the #temp1 table created by [Server1].[SourceDB].[ReportingProc]

    5. After [Server1].[DestinationDB].SomeProcedure finishes its execution, it return to [Server1].[SourceDB].[ReportingProc] and finishes executing the remainder of procedure  

    5. This is working as [SourceDb] and [DestinationDB] are in the same sql server instance.

    Now we are moving [SourceDB] and [DestinationDB] to different instances of physical servers [Server1].[SourceDB] and [Server2].[DestinationDB]

    How can we achieve the same functionality without using linked server option.

    Any help would be appreciated.

    Friday, July 10, 2020 5:15 PM

All replies

  • Hi Puja Rathi,

    Please check if this article is you want:

    SSRS: Merge data from different datasources into one dataset inside SSRS report

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 13, 2020 7:07 AM
  • Hello friend,
    Have you solved the question?
    If you have resolved your issue, could you please mark the useful reply as answer? This can be beneficial to other community members finding and reading the thread easily. 
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 15, 2020 1:56 AM