odbc proxy, connect through a middle point

Posted on 2009-02-24
Last Modified: 2013-11-09
I need to query a database on  remote Server-C  and bring it to a warehouse on Server-A.  Server-C will only allow connections from Server-B due to network constraints.  Server-B has a system DSN pointing to the database on Server-C over ODBC.

The network constraints are being addressed but I need an interim solution.  I can not make network updates at this time.

I'm using Cognos Datamanager on server A.  My query request will originate on ServerA.  Ideally, I would issue an odbc request from Server-A and Server-B would echo this request to Server-C.  I have no control over Server-C.  I could potentially install freeware on Servers A,B but I will not be able to run Cognos DataManager on server B.

Server A is running MS SQL Server 2005.  Server B might be as well, I don't know this yet.

Any ideas?
Question by:lovingatx
    LVL 38

    Accepted Solution

    On Server B create a linked server to Server C.

    Then have a stored procedure of
    CREATE CP_Query_Server_C
    SELECT *
    FROM openquery( Server_C_Link, 'Select * from MyDB.dbo.MyTable');
    Then on Server A create a link to the Server B and
    CREATE VIEW Server_C_View
    SELECT *
    FROM openquery( Server_B_Link, 'EXEC CP_Query_Server_C');

    This is a guess but it might work.

    Author Closing Comment

    Turned out that MS SQL was not installed so I couldn't try your solution.  Thanks for the input though.
    LVL 38

    Expert Comment

    by:Jim P.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Let’s list some of the technologies that enable smooth teleworking. 
    Don’t let your business fall victim to the coming apocalypse – use our Survival Guide for the Fax Apocalypse to identify the risks and signs of zombie fax activities at your business.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now