Post reply

Change database connection

  • April 6, 2005 at 6:14 am

    #61850

    Guys,

    I'm fairly new new to this so please excuse me if this appears to be fairly simple! I'm coding a stored procedure which needs to get data from SQL database tables on two different servers (on the same network). My question is; in the stored procedure how do I get it to change the SQL Server it's operating on?

    Thanks in advance

    Sean

  • April 6, 2005 at 6:30 am

    #550754

    You can create a linked server. Seach on books on line.

    When you create the linked server make sure that you give the user access only to the tables required and nothing else.


    Andy.

  • April 7, 2005 at 2:07 am

    #551059

    You can also use OPENROWSET.

    Syntax

    OPENROWSET ( 'provider_name'

        , { 'datasource' ; 'user_id' ; 'password'

            | 'provider_string' }

        , { [ catalog. ] [ schema. ] object

            | 'query' }

        )

    Example

    SELECT a.*

    FROM OPENROWSET('MSDASQL',

       'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',

       pubs.dbo.authors) AS a

    ORDER BY a.au_lname, a.au_fname

    GO

     

    Regards,

    Anders Dæmroen
    epsilon.no

  • April 7, 2005 at 2:19 am

    #551063

    Thanks for all the help guys.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply