Create link between database

Question

Create link between database

satishkumar.d

Old Hand

Points: 308
More actions
April 30, 2008 at 12:16 am

#189828

Hi All,
Could you please tell me how to create link between two databases which is in the same server?
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Can you explain what exactly you are trying to do? these is no need to link different DBs under same server. You can access objects from 2nd DB while you are connected to 1st DB.

i.e.

User DB1

Select {DB2}..{Table}

Mahesh

[/font]

MH-09-AM-8694

satishkumar.d Old Hand Points: 308 More actions · Answer 2

Thank you for your reply Mahesh. But,

I have two tables, namely [Vendor] and [Manager], created in the first database called (AIT). I have another table called (Contract) on the other database called (CIT).

I need two foreign keys in the [Contract] table to reference the other tables [Vendor] and [Manager].

When I tried creating the SQL Query that would create them. it gave me the error message:

Cross-database foreign key references are not supported.

Could you please tell me the solution for this?

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]Satish, I think you have already got the answer for this question. One of the expert have already told you that it can't be done. Even the error message is self explainer.

Mahesh[/font]

MH-09-AM-8694

satishkumar.d Old Hand Points: 308 More actions · Answer 4

Yes Mahesh. But i tried the same in Oracle between schemas in using Synonyms and using database link concepts. In MS SQL Server, when i tried this without using database link, it throws an above error message.

So that i asked whether is there any possible way to create link between the database and perform the cross database references?

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[font="Verdana"]As per my knowladge there is no such way to do this[/font]

MH-09-AM-8694

satishkumar.d Old Hand Points: 308 More actions · Answer 6

Thank you for your reply Mahesh... Please let me know, if you come to know any solution for this.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

There is no way to "link" databases in SQL Server so that you can have cross-database referential integrity. Period. End of Story.

However, you could write a trigger that will validate data between databases using the query syntax outlined in the post above.

I did a quick search and there's also a third-party tool that supposedly does the work: http://www.sqlmag.com/Article/ArticleID/94547/sql_server_94547.html

Here's a trigger sample from Michelle Poolet (just change it to query between the databases):

CREATE trigger ut_Insert_title on title5 for INSERT as

BEGIN

DECLARE @numrows int,

@nullcnt int,

@validcnt int,

@errno int,

@errmsg varchar(255)

SELECT @numrows = @@rowcount

/* ON CHILD INSERT RESTRICT */

IF

UPDATE(pub_id)

BEGIN

SELECT @nullcnt = 0

/* CHECK PARENT FOR RELATED VALUE */

SELECT @validcnt = count(*)

FROM inserted, publishers

WHERE inserted.pub_id = publishers.pub_id

IF @validcnt + @nullcnt != @numrows

BEGIN

SELECT @errno = 30002,

@errmsg = 'Sorry, cannot INSERT INTO "title5" because an associated row in "publishers" does

not exist.'

GOTO error

END

RETURN

error:

RAISERROR @errno @errmsg

ROLLBACK TRANSACTION

END

go

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning