Solved

LInked Server Problem

Posted on 2011-09-30
1
153 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 39

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query - which index being used? 2 47
Sql Permission 6 52
SQL Trigger selecting another database 4 34
get most recent and second most recent date in SQL Server 24 78
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 …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now