Post reply

multiple instance of SQL Server

  • March 9, 2005 at 10:58 am

    #67080

    I've tried searching here and BOL, but probably don't know the correct term to search for. A vendor talked someone into creating what I'll call a second instance of SQL Server on our server (SQL 2000). In Enterprise Manager, I see 2 items under "SQL Server Group"; Local containing all our other databases, and a specifically named instance with this company's name.

    I need to be able to get data from a database on the "local" instance into a stored procedure in the new instance. I know how to get data from one database to another within the local instance:

    SELECT Blah

    FROM DatabaseName.dbo.TableName

    but I can't get that to work across instances (if that's the right term). I'd appreciate any ideas or help getting me pointed in the right direction.

    TIA

    Paul

  • March 9, 2005 at 11:08 am

    #546205

    Search BOL on "Linked Servers".

    Once linked, you reference object names using 4 parts:

    YourLinkName.DBName.Owner.Object

  • March 9, 2005 at 4:40 pm

    #546259

    Thanks PW, I think I have the link set up, and tried a simple SP:

    CREATE PROCEDURE [procTest]

    AS

    SELECT Shift_num

    FROM [bell-sql].CabTS.dbo.Shift

    GO

    The Syntax Check says it's fine, but when I try to save it I get error 7405 "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options be set for the connection..." I've poked around but haven't found the solution yet. It should be noted I'm primarily an Access developer, so my knowledge of the inner workings of SQL Server is limited, though slowly expanding. Thanks for any ideas.

  • March 10, 2005 at 12:40 am

    #546297

    SET ANSI_NULLS ON

    GO

    SET ANSI_WARNINGS ON

    GO

    CREATE PROCEDURE [procTest]

    AS

    SELECT Shift_num

    FROM [bell-sql].CabTS.dbo.Shift

    GO

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 10, 2005 at 9:50 am

    #546419

    Thanks GilaMonster. I had actually searched on the problem and tried something similar to that, but didn't include the "GO" lines. Fundamental lack of understanding of how SQL Server works, I suppose. Thanks again.

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

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