CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Stored Procedure 'Search through 2 different servers'

    I am having difficulty writing the SQl for my stored procedure. The stored procedure consists of "Dynamic stored procedure to allow barcode lookup across all lines and databases. Accepts only a barcode, then searches a lookup table for all barcode types that match, then requests information from the appropriate databases". I need to add to the stored procedure " Search through ServerA for the information and if not found, Search through ServerB for the information". Here is the Code that I have already created.

    USE [Database_1]
    GO
    /****** Object: StoredProcedure [dbo].[spBarcodeLookup] Script Date: 12/17/2012 08:06:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[spBarcodeLookup]
    @Barcode VARCHAR(30)
    AS
    DECLARE @DatabaseName VARCHAR(50),
    @BarcodeCode VARCHAR(10),
    @PrefixSuffix VARCHAR(10),
    @Return INT,
    @Query VARCHAR(500)

    BEGIN
    SET NOCOUNT ON;

    DECLARE DatabaseCursor CURSOR FOR
    SELECT fdBarcodeCode, fdPrefixSuffix, fdDatabase
    FROM [Database_1].dbo.tbBarcodeLookup

    CREATE TABLE #tmpBarcodeLookup
    (Barcode VARCHAR(30),
    ContainerSN VARCHAR(10),
    ShortNumber VARCHAR(10),
    ClockNumber VARCHAR(10),
    EnterDate DATETIME)

    OPEN DatabaseCursor
    FETCH NEXT FROM DatabaseCursor INTO
    @BarcodeCode, @PrefixSuffix, @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @PrefixSuffix = 'Prefix'
    BEGIN
    IF LEFT(@Barcode, LEN(@BarcodeCode)) = @BarcodeCode
    BEGIN
    SET @Query = '[' + @DatabaseName + '].dbo.spBarcodeLookup ''' + @Barcode + ''''
    --SELECT @Query

    INSERT INTO #tmpBarcodeLookup
    EXEC (@Query)
    END
    END
    ELSE
    BEGIN
    IF RIGHT(@Barcode, LEN(@BarcodeCode)) = @BarcodeCode
    BEGIN
    SET @Query = '[' + @DatabaseName + '].dbo.spBarcodeLookup ''' + @Barcode + ''''
    --SELECT @Query

    INSERT INTO #tmpBarcodeLookup
    EXEC (@Query)
    END

    END

    FETCH NEXT FROM DatabaseCursor INTO
    @BarcodeCode, @PrefixSuffix, @DatabaseName
    END


    CLOSE DatabaseCursor
    DEALLOCATE DatabaseCursor

    SELECT Tmp.Barcode,
    Tmp.ContainerSN,
    Tmp.ShortNumber,
    Tmp.ClockNumber,
    Tmp.EnterDate,
    Ship.fdShipper AS Shipper,
    Ship.fdScannedTime AS ScannedDate,
    Ship.fdShipDate AS ShipDate
    FROM #tmpBarcodeLookup Tmp LEFT OUTER JOIN
    [AS400].dbo.tbShipperInfo Ship
    ON Ship.fdSerial LIKE '%Tmp.ContainerSN%'

    DROP TABLE #tmpBarcodeLookup

    END

  2. #2
    Join Date
    Dec 2012
    Posts
    2

    Re: Stored Procedure 'Search through 2 different servers'

    The stored procedure is in SQL Server 2008. The 2 servers within SQL Server 2008 are ServerA and ServerB. Right now, the stored procedure is only looking through ServerA. Right now the procedure is looking through ServerA to get the information needed, if it does not find the information needed, I need it to look through ServerB. Please, some one help me with this stored procedure!

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured