Relationship between tables in different databases

We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.
Not possible to create foreign key constraints across databases. You will
have to use triggers to enforce this yourself.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.
|||"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> We also have a "Reference" database that is used by all of the
> applications. My manager would like me to create some relationships
> between tables in Reference and one of the other databases.
In my experience I've found this is generally a bad idea. I don't even
really think this is the intent of the technology anyway. This also
invariably means that at some point you'll have cross-database ownership
chaining in addition to other strange hack such as creating custom triggers
to enforce constraints (as someone else has stated). Also, you'll find
yourself writing a lot more dynamic SQL to get anything done.
sql

Relationship between tables in different databases

We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.Not possible to create foreign key constraints across databases. You will
have to use triggers to enforce this yourself.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.|||"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> We also have a "Reference" database that is used by all of the
> applications. My manager would like me to create some relationships
> between tables in Reference and one of the other databases.
In my experience I've found this is generally a bad idea. I don't even
really think this is the intent of the technology anyway. This also
invariably means that at some point you'll have cross-database ownership
chaining in addition to other strange hack such as creating custom triggers
to enforce constraints (as someone else has stated). Also, you'll find
yourself writing a lot more dynamic SQL to get anything done.

Relationship between tables in different databases

We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.Not possible to create foreign key constraints across databases. You will
have to use triggers to enforce this yourself.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.|||"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> We also have a "Reference" database that is used by all of the
> applications. My manager would like me to create some relationships
> between tables in Reference and one of the other databases.
In my experience I've found this is generally a bad idea. I don't even
really think this is the intent of the technology anyway. This also
invariably means that at some point you'll have cross-database ownership
chaining in addition to other strange hack such as creating custom triggers
to enforce constraints (as someone else has stated). Also, you'll find
yourself writing a lot more dynamic SQL to get anything done.

Relation to dbid in sysdatabases

What if i update a dbid from 14 to 24 say for example, is there a reference
to it in any system tables in the corresponding user database itself that i
would also need to update. Just curious.
Will the db go into suspect mode or will it just continue to function as
normal ?I don't think there are references in the database itself to the dbid, but
there are plenty of references in master to the database id, including in
sysxlogins, which you also asked about. Some of the tables are
pseudo-tables, so they would probably be fine, but many are real tables what
could be corrupted if you updated a dbid.
I have never done this, so I can't tell you for sure what might break.
This query will show you all the columns in tables in master that reference
dbid:
use master
select name, object_name(id), objectproperty(id, 'tableisfake') from
syscolumns where name = 'dbid'
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ORSqfECdDHA.372@.TK2MSFTNGP11.phx.gbl...
> What if i update a dbid from 14 to 24 say for example, is there a
reference
> to it in any system tables in the corresponding user database itself that
i
> would also need to update. Just curious.
> Will the db go into suspect mode or will it just continue to function as
> normal ?
>|||Hi Hassan,
I've never tried this, a lot of the system uses the database name as it's
key, so maybe you would get away without breaking too much
......but any update to system tables is unsupported..........
Why would you want to do this ?
Regards,
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

Registered Server v/s Linked Server

Hello All,
What is the difference between a registered server and a linked server? If
in Query Analyzer one can reference objects using fully qualified names in a
linked server like this:
SELECT * FROM LinkedServer.Northwind.dbo.Products
why can't one do the same by registering the remote server (which is in the
same domain) and then referencing it?
Thanks somebody; help please. This has me baffled!
Jerome SmithRegistering servers if for the mmc console and information
that the snap in needs to access the server. A registered
server in the mmc console doesn't know anything about the
other registered servers, just the mmc console does.
With linked servers, the information is for SQL Server
itself so that it can access data or do whatever.
If registering servers automatically allowed the
functionality of linked servers, you'd lose a lot of control
over security, distributed queries, etc.
-Sue
On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
<jerosmith@.hotmail.com> wrote:
>Hello All,
>What is the difference between a registered server and a linked server? If
>in Query Analyzer one can reference objects using fully qualified names in a
>linked server like this:
>SELECT * FROM LinkedServer.Northwind.dbo.Products
>why can't one do the same by registering the remote server (which is in the
>same domain) and then referencing it?
>Thanks somebody; help please. This has me baffled!
>Jerome Smith
>|||Thanks Sue,
Now I still have the question: what is the purpose of registering servers?
What can one do with registered servers?
Thanks again.
Jerome
"Sue Hoegemeier" <Sue_H@.nomail.please> escribió en el mensaje
news:u4p7rvko25h1l9e4objkancqto2q2395qr@.4ax.com...
> Registering servers if for the mmc console and information
> that the snap in needs to access the server. A registered
> server in the mmc console doesn't know anything about the
> other registered servers, just the mmc console does.
> With linked servers, the information is for SQL Server
> itself so that it can access data or do whatever.
> If registering servers automatically allowed the
> functionality of linked servers, you'd lose a lot of control
> over security, distributed queries, etc.
> -Sue
> On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
> <jerosmith@.hotmail.com> wrote:
> >Hello All,
> >
> >What is the difference between a registered server and a linked server?
If
> >in Query Analyzer one can reference objects using fully qualified names
in a
> >linked server like this:
> >
> >SELECT * FROM LinkedServer.Northwind.dbo.Products
> >
> >why can't one do the same by registering the remote server (which is in
the
> >same domain) and then referencing it?
> >
> >Thanks somebody; help please. This has me baffled!
> >
> >Jerome Smith
> >
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.536 / Virus Database: 331 - Release Date: 03-11-03|||So that you can manage all the registered servers through
one mmc console using Enterprise Manager. A registered
server gives you access through Enterprise Manager.
-Sue
On Sat, 15 Nov 2003 00:19:11 -0400, "Jerome Smith"
<jsmith@.gauss.cl> wrote:
>Thanks Sue,
>Now I still have the question: what is the purpose of registering servers?
>What can one do with registered servers?
>Thanks again.
>Jerome
>"Sue Hoegemeier" <Sue_H@.nomail.please> escribió en el mensaje
>news:u4p7rvko25h1l9e4objkancqto2q2395qr@.4ax.com...
>> Registering servers if for the mmc console and information
>> that the snap in needs to access the server. A registered
>> server in the mmc console doesn't know anything about the
>> other registered servers, just the mmc console does.
>> With linked servers, the information is for SQL Server
>> itself so that it can access data or do whatever.
>> If registering servers automatically allowed the
>> functionality of linked servers, you'd lose a lot of control
>> over security, distributed queries, etc.
>> -Sue
>> On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
>> <jerosmith@.hotmail.com> wrote:
>> >Hello All,
>> >
>> >What is the difference between a registered server and a linked server?
>If
>> >in Query Analyzer one can reference objects using fully qualified names
>in a
>> >linked server like this:
>> >
>> >SELECT * FROM LinkedServer.Northwind.dbo.Products
>> >
>> >why can't one do the same by registering the remote server (which is in
>the
>> >same domain) and then referencing it?
>> >
>> >Thanks somebody; help please. This has me baffled!
>> >
>> >Jerome Smith
>> >
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.536 / Virus Database: 331 - Release Date: 03-11-03
>