multiple instance of SQL Server

Question

Post reply

multiple instance of SQL Server

pbaldy

SSC Eights!

Points: 956
More actions
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

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Search BOL on "Linked Servers".

Once linked, you reference object names using 4 parts:

YourLinkName.DBName.Owner.Object

pbaldy SSC Eights! Points: 956 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

pbaldy SSC Eights! Points: 956 More actions · Answer 4

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.