Cross database query (how to ?)

Question

Cross database query (how to ?)

everdyn

Newbie

Points: 8
More actions
January 26, 2004 at 2:14 pm

#65939

Hi,
I've got two databases : Runtime, DCS
I am trying to write a stored procedure that belongs to DCS database that itself executes a stored procedure belonging to the runtime database.
I don't know how to reference the foreign database since 'use runtime' is not accepted within a stored procedure

Any help would be appreciated

Best regards
Nicolas

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

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

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

Hi Everdyn,

The short answer is that the DCS procedure should have call runtime..procname or runtime.procowner.procname, where procowner is the owner of the procedure, and procname is the name of the procedure itself.

For a fuller explanation, I hope you don't mind me doing a cut-and-paste from another document I've written. Also, I'm afraid I need to dump a little copyright notice in with this one, sorry but that's the price fyou pay or my being lazy and quoting my own work here:

The information below is Copyright (c) 2003 Chris Leonard, http://www.databaseguy.com. It may be used in its original form as long as credit is given to the author.

<snip>

Name Resolution for Database Objects in SQL Server

Overview

A fully-qualified object name in SQL Server has four parts:

server.database.owner.object

For example, an object on server ITSNT143 in the Northwind database owned by dbo and named Employees has the following fully-qualified name:

ITSNT143.Northwind.dbo.Employees

Any of the first three components of a fully-qualified object name may be omitted. If the server name is omitted, the default is the server you are currently connected to. If the database name is omitted, the default is the current database context for your connection. So the following are all valid ways to name ITSNT143.Northwind.dbo.Employees, assuming that you are logged on to ITSNT143 and your current database context is Northwind:

Northwind.dbo.Employees

dbo.Employees

If you omit the owner from an object name, the default behavior is slightly more complex. SQL Server will first look for an object with the requested name that is owned by the current user. If that approach fails to find an existing object, then SQL Server will automatically look for an object with the requested name that is owned by the special user dbo.

So if you are logged in as a user named judy, the query “select * from mytable” will return rows from the table judy.mytable, if this table exists. If it does not, SQL Server will return rows from the table dbo.mytable, if this table exists. If it does not, SQL Server will return an error.

Microsoft recommends that all objects in a production database be owned by dbo, unless you have a specific reason to do otherwise.

</snip>

everdyn Newbie Points: 8 More actions · Answer 2

That was it !

Thank you for the detailed explenation

everdyn