-
December 20th, 2012, 09:56 AM
#1
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
-
December 20th, 2012, 12:25 PM
#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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|