Cross-database references

Hi,

I'm doing a web application that will get some information from an ERP.

At this moment I have 2 databases:

1) The aspnetdb, where I have the tables for Merbership and Role

2) The ERP database

I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.

I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?

Do you think this will work? Can someone make some comments about this situation, and give me some tips?

Thank you!

As long as you have a user who is having access to both the databases you can put your tables in any of them.

|||

Querying from multiple databases (besides having security access to both of them) on requires you to fully name the tables/views involved.

Here is the format for a fully qualified table name:

[catalog_name].[schema_name].[table_name]

So, you can join to your heart's content.

What you cannot do is create foreign keys to in one database pointing to tables in another database. So, declarative relational integrity goes out the window.

(You can create database triggers to mimic a foreign key though.)

|||

david wendelken:

What you cannot do is create foreign keys to in one database pointing to tables in another database. So, declarative relational integrity goes out the window.

(You can create database triggers to mimic a foreign key though.)

These tables were on the ERP DB, and I was passing them to the aspnetdb and SQL Server gave me an error about that. I completely forgot about one foreign key constrait, that was poiting to a table on the ERP DB.

Loosing this constraint will not be so critical, right?

|||

cseven:

Loosing this constraint will not be so critical, right?

That is a business decision, not a technical one. It's your business (or your customer's/employer's), not mine! :)

What is the business result of having orphaned records in the table? (I.e., records that point to non-existent recrods in the other database.)

Could other programmers accidentally add in those records to statistics or counts? Is that a bad thing in business terms?

Is it likely that those orphaned records would find themselves used for dropdown lists to "help" choose "correct" records to assign other table's data to?

This is data associated with users. Could it open up a security breach? Does that matter?

What else could go wrong in business terms?

Are any of these potential business problems important? Important enough to spend the time and money to write a database trigger to prevent them?

|||

You are right, altough "my" situation is not so critical (I think so) as your examples. It's a simple internal requests application, and the only cross database reference is the ItemID that I store on the Requests table.

So, for each request i have multiple item lines, referencing to an Item on the ERP products table.

I had the Foreign Key Constraint and only removed it because I moved the tables to another DB, so the better thing to do is to create the triggers.

What I need to do is something like this,right?

http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html

|||

Yes, except that appears to be for the SqlLite database, whatever that is.

You'll need to use SqlServer T-Sql syntax instead.

A few more googles (or the online help) will get you there. Triggers are pretty easy.

Cross-database foreign key references are not supported. Workaround?

When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
Charles
Given that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.
A
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>
|||>> Is there any way round this, or am I trying to do something unreasonable?[vbcol=seagreen]
You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.
Anith
|||Charles,
By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)
RLF
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>
|||Hi Aaron
Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.
Charles
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epNsk9UUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Given that I don't fully understand your scenario, or what set of
> requirements is driving the separation of the data in the first place, if
> you really want to enforce the relationship, then keep both tables in the
> same database, and then create a view in the other database where you
> think you need a "copy" of the data.
> A
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Russell
Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.
Cheers.
Charles
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23IfhS%23UUIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Charles,
> By definition, constraints only work within a database. The only
> mechanism that I have used for cross-database constraints is triggers.
> (And only occasionally.)
> RLF
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> You are not trying to do anything unreasonable. In fact, it is a very
> common issue. Since DRI between databases are not supported, people
> usually use triggers (a way of procedural referential integrity) to get
> the job done.
> --
> Anith
>
|||On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still like
> to have a constraint based on rows in a table of another database (running
> in the same instance).
> Is there any way round this, or am I trying to do something unreasonable? If
> it's not possible, what would others normally do in this situation?
> TIA
> Charles
Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.
|||Hi
There is also the option of using a function in the constraint.
John
"Charles Law" wrote:

> Hi Anith
> Thanks for the quick reply. As I replied to Russell, with your and his vote,
> it looks like I will be looking at triggers.
> Cheers.
> Charles
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>
>
|||Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...[vbcol=seagreen]
> Hi
> There is also the option of using a function in the constraint.
> John
> "Charles Law" wrote:

Cross-database foreign key references are not supported. Workaround?

When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
CharlesGiven that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.
A
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>|||>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.
--
Anith|||Charles,
By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)
RLF
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>|||Hi Aaron
Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.
Charles
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epNsk9UUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Given that I don't fully understand your scenario, or what set of
> requirements is driving the separation of the data in the first place, if
> you really want to enforce the relationship, then keep both tables in the
> same database, and then create a view in the other database where you
> think you need a "copy" of the data.
> A
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like to have a constraint based on rows in a table of another database
>> (running in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
>>
>|||Hi Russell
Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.
Cheers.
Charles
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23IfhS%23UUIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Charles,
> By definition, constraints only work within a database. The only
> mechanism that I have used for cross-database constraints is triggers.
> (And only occasionally.)
> RLF
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like to have a constraint based on rows in a table of another database
>> (running in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
>>
>|||Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> Is there any way round this, or am I trying to do something
>> unreasonable? If it's not possible, what would others normally do in
>> this situation?
> You are not trying to do anything unreasonable. In fact, it is a very
> common issue. Since DRI between databases are not supported, people
> usually use triggers (a way of procedural referential integrity) to get
> the job done.
> --
> Anith
>|||On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still like
> to have a constraint based on rows in a table of another database (running
> in the same instance).
> Is there any way round this, or am I trying to do something unreasonable? If
> it's not possible, what would others normally do in this situation?
> TIA
> Charles
Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.|||Hi
There is also the option of using a function in the constraint.
John
"Charles Law" wrote:
> Hi Anith
> Thanks for the quick reply. As I replied to Russell, with your and his vote,
> it looks like I will be looking at triggers.
> Cheers.
> Charles
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> Is there any way round this, or am I trying to do something
> >> unreasonable? If it's not possible, what would others normally do in
> >> this situation?
> >
> > You are not trying to do anything unreasonable. In fact, it is a very
> > common issue. Since DRI between databases are not supported, people
> > usually use triggers (a way of procedural referential integrity) to get
> > the job done.
> >
> > --
> > Anith
> >
>
>|||Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> Hi
> There is also the option of using a function in the constraint.
> John
> "Charles Law" wrote:
>> Hi Anith
>> Thanks for the quick reply. As I replied to Russell, with your and his
>> vote,
>> it looks like I will be looking at triggers.
>> Cheers.
>> Charles
>>
>> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> >> Is there any way round this, or am I trying to do something
>> >> unreasonable? If it's not possible, what would others normally do in
>> >> this situation?
>> >
>> > You are not trying to do anything unreasonable. In fact, it is a very
>> > common issue. Since DRI between databases are not supported, people
>> > usually use triggers (a way of procedural referential integrity) to get
>> > the job done.
>> >
>> > --
>> > Anith
>> >
>>|||"Charles Law" <blank@.nowhere.com> wrote in message
news:e9P4QCVUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Aaron
> The databases are separate since the look-up data will change from
> customer to customer, and it is more convenient to be able to 'plug in' a
> new set of static data if it is in a separate database.
>
I think your question demonstrates exactly the opposite. It is much LESS
convenient to put the static data in a separate database precisely because
that data is very likely to be referenced by a foreign key.
--
David Portas|||> I hope you realize that whatever solution you choose, it might not be
> fully watertight. If you ever need to restore one of your databases
> from a backup, that may violate your integrity - I don't know a fully
> safe solution
Good point.
> Also be aware that sometimes trigger do not fire.
That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:6d8fa87b-9204-4858-a727-7b8c40d625e0@.1g2000hsl.googlegroups.com...
> On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like
>> to have a constraint based on rows in a table of another database
>> (running
>> in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If
>> it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
> Charles,
> I hope you realize that whatever solution you choose, it might not be
> fully watertight. If you ever need to restore one of your databases
> from a backup, that may violate your integrity - I don't know a fully
> safe solution. Also be aware that sometimes trigger do not fire.|||Hi David
I can't argue with your logic there.
I'll have to give this some more careful thought. Perhaps I am creating more
problems than I solve by separating the static data from the rest.
Charles
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uXD2f9VUIHA.5360@.TK2MSFTNGP03.phx.gbl...
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e9P4QCVUIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Aaron
>> The databases are separate since the look-up data will change from
>> customer to customer, and it is more convenient to be able to 'plug in' a
>> new set of static data if it is in a separate database.
> I think your question demonstrates exactly the opposite. It is much LESS
> convenient to put the static data in a separate database precisely because
> that data is very likely to be referenced by a foreign key.
> --
> David Portas
>|||On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> > I hope you realize that whatever solution you choose, it might not be
> > fully watertight. If you ever need to restore one of your databases
> > from a backup, that may violate your integrity - I don't know a fully
> > safe solution
> Good point.
> > Also be aware that sometimes trigger do not fire.
> That would be worrying if it were random. Do you mean that they sometimes
> don't fire for no apparent reason?
> Charles
not random. Described here:
http://www.devx.com/dbzone/Article/31985|||On Jan 7, 12:57 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Hi John
> Any idea how performance compares between use of FK constraint (if it were
> available), trigger and function?
> Charles
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> > Hi
> > There is also the option of using a function in the constraint.
> > John
> > "Charles Law" wrote:
> >> Hi Anith
> >> Thanks for the quick reply. As I replied to Russell, with your and his
> >> vote,
> >> it looks like I will be looking at triggers.
> >> Cheers.
> >> Charles
> >> "Anith Sen" <an...@.bizdatasolutions.com> wrote in message
> >>news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> >> Is there any way round this, or am I trying to do something
> >> >> unreasonable? If it's not possible, what would others normally do in
> >> >> this situation?
> >> > You are not trying to do anything unreasonable. In fact, it is a very
> >> > common issue. Since DRI between databases are not supported, people
> >> > usually use triggers (a way of procedural referential integrity) to get
> >> > the job done.
> >> > --
> >> > Anith
I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
winner.|||I guess that's what one would expect, but good to have it confirmed (on
2000).
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:632fe8e1-a5ea-4d23-b90c-9e803f7f5a13@.p69g2000hsa.googlegroups.com...
> On Jan 7, 12:57 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> Hi John
>> Any idea how performance compares between use of FK constraint (if it
>> were
>> available), trigger and function?
>> Charles
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
>> > Hi
>> > There is also the option of using a function in the constraint.
>> > John
>> > "Charles Law" wrote:
>> >> Hi Anith
>> >> Thanks for the quick reply. As I replied to Russell, with your and his
>> >> vote,
>> >> it looks like I will be looking at triggers.
>> >> Cheers.
>> >> Charles
>> >> "Anith Sen" <an...@.bizdatasolutions.com> wrote in message
>> >>news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> >> >> Is there any way round this, or am I trying to do something
>> >> >> unreasonable? If it's not possible, what would others normally do
>> >> >> in
>> >> >> this situation?
>> >> > You are not trying to do anything unreasonable. In fact, it is a
>> >> > very
>> >> > common issue. Since DRI between databases are not supported, people
>> >> > usually use triggers (a way of procedural referential integrity) to
>> >> > get
>> >> > the job done.
>> >> > --
>> >> > Anith
> I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
> winner.|||Hi Charles
There is an example in the link Alex posted.
John
"Charles Law" wrote:
> Hi John
> Any idea how performance compares between use of FK constraint (if it were
> available), trigger and function?
> Charles
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> > Hi
> >
> > There is also the option of using a function in the constraint.
> >
> > John
> >
> > "Charles Law" wrote:
> >
> >> Hi Anith
> >>
> >> Thanks for the quick reply. As I replied to Russell, with your and his
> >> vote,
> >> it looks like I will be looking at triggers.
> >>
> >> Cheers.
> >>
> >> Charles
> >>
> >>
> >> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> >> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> >> Is there any way round this, or am I trying to do something
> >> >> unreasonable? If it's not possible, what would others normally do in
> >> >> this situation?
> >> >
> >> > You are not trying to do anything unreasonable. In fact, it is a very
> >> > common issue. Since DRI between databases are not supported, people
> >> > usually use triggers (a way of procedural referential integrity) to get
> >> > the job done.
> >> >
> >> > --
> >> > Anith
> >> >
> >>
> >>
> >>
>
>|||Thanks. It looks, though, like they do not fire if the settings are
incorrect, or if someone changes them. So long as the rules are followed
then there shouldn't be a problem?
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
> On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> > I hope you realize that whatever solution you choose, it might not be
>> > fully watertight. If you ever need to restore one of your databases
>> > from a backup, that may violate your integrity - I don't know a fully
>> > safe solution
>> Good point.
>> > Also be aware that sometimes trigger do not fire.
>> That would be worrying if it were random. Do you mean that they sometimes
>> don't fire for no apparent reason?
>> Charles
> not random. Described here:
> http://www.devx.com/dbzone/Article/31985|||On Jan 8, 5:24 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Thanks. It looks, though, like they do not fire if the settings are
> incorrect, or if someone changes them. So long as the rules are followed
> then there shouldn't be a problem?
> Charles
> "Alex Kuznetsov" <alk...@.gmail.com> wrote in message
> news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
> > On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> >> > I hope you realize that whatever solution you choose, it might not be
> >> > fully watertight. If you ever need to restore one of your databases
> >> > from a backup, that may violate your integrity - I don't know a fully
> >> > safe solution
> >> Good point.
> >> > Also be aware that sometimes trigger do not fire.
> >> That would be worrying if it were random. Do you mean that they sometimes
> >> don't fire for no apparent reason?
> >> Charles
> > not random. Described here:
> >http://www.devx.com/dbzone/Article/31985
Also use database permissions to make sure that your users cannot
truncate your parent table. Truncate also does not fire triggers. Bulk
insert also does not fire them either. Also dropping recreating a
parent table is a way around your triggers...|||> Also use database permissions to make sure that your users cannot
> truncate your parent table. Truncate also does not fire triggers. Bulk
> insert also does not fire them either. Also dropping recreating a
> parent table is a way around your triggers...
Ok. Point taken.
Cheers
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:35375a5f-32fd-4389-b3d9-b9160a923061@.i7g2000prf.googlegroups.com...
> On Jan 8, 5:24 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> Thanks. It looks, though, like they do not fire if the settings are
>> incorrect, or if someone changes them. So long as the rules are followed
>> then there shouldn't be a problem?
>> Charles
>> "Alex Kuznetsov" <alk...@.gmail.com> wrote in message
>> news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
>> > On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> >> > I hope you realize that whatever solution you choose, it might not
>> >> > be
>> >> > fully watertight. If you ever need to restore one of your databases
>> >> > from a backup, that may violate your integrity - I don't know a
>> >> > fully
>> >> > safe solution
>> >> Good point.
>> >> > Also be aware that sometimes trigger do not fire.
>> >> That would be worrying if it were random. Do you mean that they
>> >> sometimes
>> >> don't fire for no apparent reason?
>> >> Charles
>> > not random. Described here:
>> >http://www.devx.com/dbzone/Article/31985
> Also use database permissions to make sure that your users cannot
> truncate your parent table. Truncate also does not fire triggers. Bulk
> insert also does not fire them either. Also dropping recreating a
> parent table is a way around your triggers...

Cross-database Dependency Check

Is there anyway to check for cross-database dependencies?

For example:
I have a stored proc in the Pubs database that contains a query that references a table in the Northwind database (as well as reference a table in the Pubs database.) I'm having no problems finding the depencency for the Pubs table. I can't seem to find the dependency for the Northwind table.

Any help would be appreciated.

Thanks in advance.

ScottSQL Server 2000 only tracks objects in the current database within dbo.sysdepends.

-PatP