Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unhappy Unanswered: Stored Procs and linked servers

    /****** 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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It falls over? You mean you need a stronger rack for your servers?

    Got an error message?

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    I guess you are using different password for servers.

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •