Solved

LInked Server Problem

Posted on 2011-09-30
1
158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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