Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    47

    Question Unanswered: Accessing a Different Server From within Stored proc.

    Hi,

    I have a query in Stored procedures...See if u could help me...

    I am using two Databases Cobank and BR0001

    which are on 2 different machines....

    I am writing stored procedures in Cobank

    In that stored procedure I m writing a query to access a table in BR0001

    So do u know how to proceed?

    I tried by passing servername as a parameter to stored proc.



    CREATE PROCEDURE ViewBranchDetail
    @localDBName as nvarchar(20)
    @servername as nvarchar(200)
    AS
    exec('Select * from opendatasource ( '''+@servername+''' ) . '''+@localDBName+'''..BranchDetail' )


    exec('Select * from opendatasource (''SQLOLEDB'''+','+''' Data Source=localhost;Initial Catalog=BR0001;Integrated Security=True )'''+ '..BranchDetail' )
    GO

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    select <column1, column2, column...n>
    from [Servername\Instancename].<database_name>.<schema_owner>.<table>

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    the easiest thing to do is create a serverlink on Cobank SQL Server to BR0001 Server, and use 4 part qualifier in query

    select * from
    BR0001SERVER.BR0001.owner.table

    Not sure if you want to head that way though.

Posting Permissions

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