Solved

LInked Server Problem

Posted on 2011-09-30
1
156 Views
Last Modified: 2012-05-12
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.


SELECT  COMPANY_CODE,
            PART_CODE,
            PART_DESC_1,
            PRODUCT_GROUP,
            PRODUCT_CLASS,
            PRODUCT_TYPE,
            SYS_USER_DEFINED_1
    FROM    SDWINSQL03V.fin_prod.dbo.VW_PRODUCT_DATA AS VW_PRODUCT_DATA_1

Open in new window

USE [fin_prod]
GO
/****** Object:  StoredProcedure [dbo].[AER_RMINTOEXT]    Script Date: 09/30/2011 11:08:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- 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
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @STARTDATE,
		  @ENDDATE, 
		  M.FACTORY,
            M.PART_CODE,
            P.PART_DESC_1,
            CASE P.SYS_USER_DEFINED_1
			 WHEN 'PC' THEN 'POST CONSUMER'
			 WHEN 'PI' THEN 'POST INDUSTRIAL'
			 WHEN 'VIRGIN' THEN 'VIRGIN'
			 ELSE 'UNKNOWN'
		  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,
            P.SYS_USER_DEFINED_1,
            M.PART_CODE,
            P.PART_DESC_1
END

Open in new window

0
Comment
Question by:Hers2keep
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 36893440
Full name qualifier in SQL is server_name.db_name.schema_name.table_name and looks like you have something else in code above.

Distribured queries against different database even on the same server are (very) slow sometimes - see below:

http://www.sql-server-performance.com/2007/linked-server/
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question