I have two sql servers involved in this problem. [sdwinerp01] (erp) is our ERP sql server. [sdwineng01] (eng) is our production server. On each server, there is a linked server set up to the other one. When my primary data is from eng and I run queries/reports directly from that server, they run fairly quickly. I have several views set up on erp that reference tables/views on eng. They run really slowly. So this morning I tried to create a report based on eng instead of on erp to see if it would run faster. I set up a view on eng from a table on erp and the view executes correctly. However, when I try to join that view with another view actually ON eng, it blows up and gives this error:
Could not locate entry in sysdatabases for database 'fin_prod'. No entry found with that name. Make sure that the name is entered correctly.
"fin_prod" is on the erp server. The collations are different, but we've been able to do this successfully in the past with another database on the erp server.
I'm not sure what I'm missing here.
I'm attaching the view that is generated from the erp data, as well as the sp that joins that view with the one pulling data from the eng server.
FROM SDWINSQL03V.fin_prod.dbo.VW_PRODUCT_DATA AS VW_PRODUCT_DATA_1
/****** Object: StoredProcedure [dbo].[AER_RMINTOEXT] Script Date: 09/30/2011 11:08:12 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: CARLA M ROMERE
-- Create date: 09-30-2011
-- Description: RAW MATERIALS INTO EXTRUSION BY TYPE
ALTER PROCEDURE [dbo].[AER_RMINTOEXT]
@STARTDATE AS DATETIME,
@ENDDATE AS DATETIME
SET NOCOUNT ON;
WHEN 'PC' THEN 'POST CONSUMER'
WHEN 'PI' THEN 'POST INDUSTRIAL'
WHEN 'VIRGIN' THEN 'VIRGIN'
END AS PCPIV,
SUM(M.INGR_ACTUAL) AS INPUT_TOTAL
FROM dbo.VW_BATCH_DETAILS_ALL M
RIGHT OUTER JOIN VW_PRODUCT_DATA P
ON M.PART_CODE = P.PART_CODE COLLATE Latin1_General_BIN
WHERE DATETIME_DUMP BETWEEN @STARTDATE AND @ENDDATE
GROUP BY M.FACTORY,