>From the MS SQL Server Books Online "Object Visibility and Qualification Rules
When you create an object, Microsoft® SQL Server™ uses the following defaults for the parts of the name that are not specified: server defaults to the local server. database defaults to the current database. owner_name defaults to the username in the specified database associated with the login ID of the current connection. When you create an object, SQL Server uses the defaults to build the fully qualified name. For example, if a user is logged into Northwind as the database owner (dbo) user, either of the following two statements creates a table named Northwind.dbo.TableX: CREATE TABLE TableX (cola INT PRIMARY KEY, colb NCHAR(3)) or CREATE TABLE Northwind.dbo.TableX(cola INT PRIMARY KEY, colb NCHAR(3)) Note It is recommended that the full table or view name be specified to eliminate possible confusion relating to the object in question. When you refer to an object, Microsoft® SQL Server™ uses the following defaults for the parts of the name that are not specified: server defaults to the local server. database defaults to the current database. owner_name defaults to the username in the specified database associated with the login ID of the current connection. If that user owns no object with the specified name, SQL Server looks for an object with the specified name that is owned by the database owner (dbo) user. For example, assume LoginX connects to a server that has two databases: DBY and DBZ. LoginX is associated with UserA in database DBY and with UserB in database DBZ. LoginX executes a SELECT statement in the current database. USE DBY SELECT * FROM DBY..TableX Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX. In the next example, LoginX executes a SELECT statement on a table that is not in the current database: USE DBY SELECT * FROM DBZ..TableY Because LoginX is associated with UserB in database DBZ, SQL Server first looks for DBZ.UserB.TableY. If there is no table with this name, SQL Server then looks for a table DBZ.dbo.TableY. Note SQL Server does not try to deduce the owner of remote tables based on the current login. To ensure that distributed queries execute properly, use fully-qualified names. The visibility for stored procedures that begin with sp_ differs from the visibility for regular stored procedures. For more information, see CREATE PROCEDURE. " Many more examples exist in the docs. -Joe --- Simon Windsor <[EMAIL PROTECTED]> wrote: > Hi > > Thanks. > > I have spent many hours seaching the Web and MS-SQL documentation, > and no > where does it state, or give an example of > > table_source = database.owner.table > > As you say this is very similar to Oracle with database links. > > Thanx, once again > > Simon > ----- Original Message ----- > From: "Joe Raube" <[EMAIL PROTECTED]> > To: "Simon Windsor" <[EMAIL PROTECTED]> > Sent: Tuesday, November 26, 2002 5:41 PM > Subject: Re: Perl - Sql Server question > > > > Do the databases exist in the same server? > > > > If so, as long as the permissions exist, this is very similar to > > accessing multiple schemas under Oracle. > > > > Oracle > > > > select a.id, b.id > > from schema1.table1 a, schema2.table2 b > > where b.id = a.id; > > > > SQL Server > > > > select a.id, b.id > > from database1.dbo.table1 a, database2.dbo.table2 b > > where b.id = a.id; > > > > -Joe > > > > --- Simon Windsor <[EMAIL PROTECTED]> wrote: > > > Hi > > > > > > I am sorry for asking this question, but I have been asked to > port > > > a number of MySQL databases to MS Sql Server, and to move the > > > Perl/CGI application to ISAPI Perl. > > > > > > Everthing has gone fairly well, due mainly to the portability > of > > > Perl/DBI, except I do not know how to perform a joined query > across > > > two databases in SQL Server. > > > > > > This is easy in Oracle and MySQL but I cannot find how to do > this > > > for MS SQL Server. > > > > > > Does anyone know if it is possible, or should I collapse my > three > > > databases into one. > > > > > > It is shame that whilst Perl is very portable, and SQL to a > lesser > > > extent, the ideas that different venders use for their > databases, > > > schemas, users etc are all different. > > > > > > SImon > > > > > > Simon Windsor > > > Email: [EMAIL PROTECTED] > > > Tel: 01454 61768 > > > Mob: 07720 447385 > > > > >
