/****** Object: StoredProcedure [dbo].[usp_HashQuantity_Test] Script Date: 03/23/2011 09:50:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_HashQuantity_Test]
@JobNum as varchar(8)
AS
BEGIN

DECLARE @Job as varchar(8)
DECLARE @StockCode as varchar(30)
DECLARE @QtyToMake as decimal(10,3)
DECLARE @QtyManufactured as decimal(10,3)
DECLARE @MatCostToDate1 as decimal(14,2)
DECLARE @LabCostToDate1 as decimal(14,2)
DECLARE @MatValueIssues1 as decimal(14,2)
DECLARE @LabValueIssues1 as decimal(14,2)
DECLARE @JobStartDate as DateTime
DECLARE @WIPValue as decimal(14,2)

DECLARE @TempTable table(Job varchar(8), StockCode varchar(30), QtyToMake decimal(10,3), QtyManufactured decimal(10,3), MatCostToDate1 decimal(14,2), LabCostToDate1 decimal(14,2), MatValueIssues1 decimal(14,2), LabValueIssues1 decimal(14,2), JobStartDate DateTime, WIPValue decimal(14,2))

SET NOCOUNT ON

Select @WIPValue = MatCostToDate1 - MatValueIssues1 - LabValueIssues1
FROM SysproCompanyB.dbo.WipMaster WipMaster_1
WHERE (Job = @JobNum)
ORDER BY JobStartDate DESC

SELECT @Job = Job, @StockCode = StockCode, @QtyToMake = QtyToMake, @QtyManufactured = QtyManufactured, @MatCostToDate1 = MatCostToDate1, @LabCostToDate1 = LabCostToDate1, @MatValueIssues1 = MatValueIssues1, @LabValueIssues1 = LabValueIssues1, @JobStartDate = JobStartDate
FROM SysproCompanyB.dbo.WipMaster WipMaster_1
WHERE (Job = @JobNum)
ORDER BY JobStartDate DESC

INSERT INTO @TempTable(Job, StockCode, QtyToMake, QtyManufactured, MatCostToDate1, LabCostToDate1, MatValueIssues1, LabValueIssues1, JobStartDate, WIPValue)
VALUES (@Job, @StockCode, @QtyToMake, @QtyManufactured, @MatCostToDate1, @LabCostToDate1, @MatValueIssues1, @LabValueIssues1, @JobStartDate, @WIPValue)

SET NOCOUNT OFF
Select * from @TempTable
End


This works fine on a single server but the moment i try it on a linked server, it falls over.

SELECT @Job = Job, @StockCode = StockCode, @QtyToMake = QtyToMake, @QtyManufactured = QtyManufactured, @MatCostToDate1 = MatCostToDate1, @LabCostToDate1 = LabCostToDate1, @MatValueIssues1 = MatValueIssues1, @LabValueIssues1 = LabValueIssues1, @JobStartDate = JobStartDate
FROM SysproCompanyB.dbo.WipMaster WipMaster_1
WHERE (Job = @JobNum)
ORDER BY JobStartDate DESC

SysproCompanyB is on a different server entirely. Can someone pls help me?

Thanks in advance.

It falls over? You mean you need a stronger rack for your servers?

Got an error message?

The following could be the reasons behind it :-


1) I guess you are using different passwords for different servers.

2) If you use diffrent password for different servers and when you call database of both the servers, instead of referring to your schems, it goes to "by deafult" schema..mainly master or test.

3) If different schema, hence won't fetch your query.

4) Make sure, passwords are same in both the database or else default schema should not be different.