Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: Using Remote Stored Procedure Locally

    Hello All,

    I need some help in how to make things work. Here is some sample data.

    Code:
    Historical Database:
    ID#    | Test Date    | Department   | Test       | Result
    =============================================================
    1234     04/01/2009     Chemistry      Glucose       75
    1234     01/01/2009     Chemistry      Sodium      130
    1234     04/01/2009     Hematology    WBC          7.50
    1234     01/01/2009     Hematology    HGB          10.0
    5678     05/01/2009     Chemistry      Glucose       82    
    5678     02/01/2009     Chemistry      AST             25
    5678     05/01/2009     Hematology    HCT           33.0
    
    Current Database:
    ID #   | Test Date    | Department   | Test       | Result
    =============================================================
    1234     01/01/2009     Chemistry      Glucose       75
    1234     04/01/2009     Chemistry      Sodium      130
    1234     01/01/2009     Hematology    WBC          7.50
    1234     04/01/2009     Hematology    HGB          10.0
    5678     02/01/2009     Chemistry      Glucose       82    
    5678     05/01/2009     Chemistry      AST             25
    5678     02/01/2009     Hematology    HCT           33.0
    I have a Calling Program that will execute whatever Stored Proc I instruct it to and pass an 'ID' parameter.

    I have a SQL 2000 Database (Historical Database) on a remote server that has a Stored Proc that I can't modify but I have to use. I can't post all of it because of legal crap but I can post a snippet.

    Code:
    CREATE PROCEDURE [dbo].[HistoricalLabData] 
    ( 
     @ID varchar(20) 
    ) 
    AS 
    create table #HistoricalLabData
    ( LabID varchar(20), TestDate datetime, Dept varchar(50), Test varchar(100), Result varchar(20) ) 
    
    Declare @LabID varchar(20), 
    Declare @LabTestDate datetime, 
    Declare @LabDepartment varchar(50), 
    Declare @LabTest varchar(100), 
    Declare @LabResult varchar(20)
    
    --------------Inserting Values Code------------
    <Not displayed...Select query and just inserting data into the #HistoricalLabData table>
    --------------End Inserting Vaues Code--------
    
    --------------Remove Temporary Table--------
    Delete from #HistoricalLabData where TestDate is null 
    select * from #HistoricalLabData (nolock)  
    Go
    As you can see, the remote stored proc creates a temporary table called 'HistoricalLabData'

    I have a Local server running SQL Server 2005. I have to create a Stored Proc on it to query the Current Database for the same data as the Historical Database.

    I have to compare the 'TestDates' of the data from the Current DB and the Historical Database using two Stored Procs. Unfortunately I have to use the Stored Proc in the remote database and they want a Stored Proc created locally.

    After my Calling Program calls the Local Stored Proc....

    can the Local Stored Proc put the results of its query into a View, execute the Remote Stored Proc, get the results from the #HistoricalLabData table that is created and insert into the same View on the Local database, finish executing the Remote Stored Proc so the #HistoricalLabData table is removed?

    If I can get the results of both stored procs into a Local View, I can write an ad-hoc query from my Calling Program to get the results that I need. I just need to know how to get the results from the remote table created from the remote stored proc into the Local View, let the remote stored proc finish executing so it will delete the #HistoricalLabData table, get the results from a local stored proc, that will need to be written, into the Local View and how to delete the results in the View or delete the View after my Calling Program gets the results.

    I have to use two stored procs and I have control over the local one.

    Below is some test data to play with:
    ------------------------------------------
    --===== "safe" place to "play"
    USE TempDB
    GO
    --===== Test tables
    ---Please note that this example table is created on the remote server via the remote stored proc---
    CREATE TABLE dbo.HistoricalLabData
    (
    ID INT,
    TestDate DATETIME,
    Department VARCHAR(50),
    Test VARCHAR(100),
    Result VARCHAR(20)
    )

    --------This is a local table--------
    CREATE TABLE dbo.LabData
    (
    ID INT,
    TestDate DATETIME,
    Department VARCHAR(50),
    Test VARCHAR(100),
    Result VARCHAR(20)
    )
    --===== Populate the test tables
    INSERT INTO HistoricalLabData
    (ID,TestDate,Department,Test,Result)
    SELECT '1234','04/01/2009','Chemistry' ,'Glucose','75' UNION ALL
    SELECT '1234','01/01/2009','Chemistry' ,'Sodium' ,'130' UNION ALL
    SELECT '1234','04/01/2009','Hematology','WBC' ,'7.50' UNION ALL
    SELECT '1234','01/01/2009','Hematology','HGB' ,'10.0' UNION ALL
    SELECT '5678','05/01/2009','Chemistry' ,'Glucose','82' UNION ALL
    SELECT '5678','02/01/2009','Chemistry' ,'AST' ,'25' UNION ALL
    SELECT '5678','05/01/2009','Hematology','HCT' ,'33.0'
    INSERT INTO LabData
    (ID,TestDate,Department,Test,Result)
    SELECT '1234','01/01/2009','Chemistry' ,'Glucose','75' UNION ALL
    SELECT '1234','04/01/2009','Chemistry' ,'Sodium' ,'130' UNION ALL
    SELECT '1234','01/01/2009','Hematology','WBC' ,'7.50' UNION ALL
    SELECT '1234','04/01/2009','Hematology','HGB' ,'10.0' UNION ALL
    SELECT '5678','02/01/2009','Chemistry' ,'Glucose','82' UNION ALL
    SELECT '5678','05/01/2009','Chemistry' ,'AST' ,'25' UNION ALL
    SELECT '5678','02/01/2009','Hematology','HCT' ,'33.0'
    GO
    ---------------------

    Thank you..
    Last edited by eusanpe; 04-17-09 at 14:22.

Posting Permissions

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