Solved

LInked Server Problem

Posted on 2011-09-30
1
159 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
[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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

624 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