0

I'm working on a project to create a table that pull information from my local server and 2 online servers. The 2 online servers are both linked with my local server. I only have ability to read from the online servers and the data is to large for me to create a duplicate.

I built some code that would work on Management Studio, however when I place those code into SSRS, I got message that says one of my table already exists. I tried to put a drop table clause in front of that, but then I got message for the next table down the line already existed. And if I pull drop statement for every table, I got a error message for Timeout expired when refresh the fields.

SELECT s.SiteID, s.[StoreName], cf.CustomerID, , cf.AccountNumber, cf.AccountStatus,
 cf.Store_ID, cf.InstitutionID, cf.TransactionTime, cf.Comment
INTO #Report_Table1
FROM dbo.View_GetCustomerInfo cf
LEFT JOIN dbo.Store_Table s ON cf.Store_ID = s.Store_ID
;

SELECT t.*, cl.SaleAmount
INTO #Report_Table2
FROM #Report_Table1 t
LEFT JOIN OnlineServe01.Views.dbo.SaleUpdate su
    ON t.CustomerID = cl.CustomerID AND t.Store_ID = cl.Store_ID AND [Status] = 'A'
;

SELECT InstitutionID, Source_ID, BankName
INTO #Report_BankName
FROM OnlineServe01.Views.dbo.InstitutionInfo bn 
WHERE InstitutionID IN (
    SELECT InstitutionID FROM #Report_Table2)
;

SELECT df.*, bn.BankName
INTO #Report_Table3 
FROM #Report_Table2 t
LEFT JOIN #Report_BankName bn ON df.InstitutionID = bn.InstitutionID AND df.Store_ID = bn.Store_ID
;

SELECT StoreName, SiteID, CustomerID, SaleAmount
, BankName, AccountNumber, AccountStatus, TransactionTime, Comment
INTO #Report_Table4
FROM #Report_Table3 t
;

SELECT *
INTO #Report_PlayerName
FROM (
    SELECT DISTINCT CustomerID, FirstName, LastName,
      Dense_Rank () OVER (Partition by CustomerID ORDER BY FirstName) AS Rnk
    FROM OnlineServe02.CustomerManagement.dbo.CustomerName
    WHERE PreferredName = 0 
    AND CustomerID IN (SELECT DISTINCT CustomerID FROM #Report_Table4)
) a
WHERE Rnk = 1
;

SELECT t.*, pn.LastName, pn.FirstName, ca.Deposited, ca.Used, ca.InTransit, ca.Available
FROM #Report_Table4 t
LEFT JOIN OnlineServe02.CustomerManagement.dbo.AccountActivity ca 
ON t.CustomerID = ca.CustomerID AND t.SiteID = ca.SiteID
LEFT JOIN #Report_PlayerName pn ON t.CustomerID = pn.CustomerID 
;
Sean Wu
  • 3
  • 2
  • Why all the temp tables? These could be inline queries or ctes which would save you lots of processing time and aggravation. Also, you need to upgrade from sql 2008. End of life is less than 2 weeks away. – Sean Lange Jun 18 '19 at 16:33
  • @SeanLange Cross server joins actually slower than one on one joins. Thanks for the tip on ctes, it actually solved the problem initially. I think SSRS needs to recreate the table every time it change server. With cte it actually stay in the same server and run the rest of the query. And unfortunately it's not my decision to change the tech in the corporation, so I just have to work around it. – Sean Wu Jun 18 '19 at 19:30
  • Have you tried adding an IF/DROP TABLE at the beginning for each table? `IF OBJECT_ID('tempdb..#Report_Table1') IS NOT NULL DROP TABLE #Report_Table1` – Hannover Fist Jun 18 '19 at 20:12
  • @HannoverFist That's what I did and get the error for timeout expired. – Sean Wu Jun 18 '19 at 22:07
  • Try to figure out which part is causing the slowness - the second query where your JOINing the temp table with another server. You may need to get the other server's data into another temp table with OPENQUERY and then JOIN to the current temp table. There's a `cl.SaleAmount` reference but not a CL table. – Hannover Fist Jun 18 '19 at 23:19

0 Answers0