Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

LInked Server Problem

Posted on 2011-09-30
1
Medium Priority
?
162 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:Carla Romere
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

927 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