Using a view in one SQL database in another

Question

This is a is it possible question.

I have one database file that contains user information, I have another that is for support requests.

I want a view of my user data to be used in my support database. to link support jobs to the users. Is it possible to link this information through like linked tables in access and create a view of data from both systems.

If not I guess I could do a batch copy of the data each night into a table in the support system.

Any Ideas of how to do this.

The answer was

This was in my support database

SELECT     Org_Name, Org_id, Con_id, Con_FirstName, Con_LastName, UserName
FROM         SupportDb.dbo.qry_UserDetails

Answer 1

Found this for you ;). Good luck with it.

Answer 2

Assume 2 servers:

Source - the one with the Users table Target - the one with the support request.

On the source you create a readonly view of your users and you create an SQL user / AD user which has read access to that particular view.

Then on the target you create a linked server, point to the source, and use the credentials of the user you've just created.

Then you can access the view through your Target Database. Good luck.

The syntax looks like server.database.schema.object(http://msdn.microsoft.com/en-us/library/ms177563.aspx)

and here's some more info on creating a linked server: http://msdn.microsoft.com/en-us/library/ms188279.aspx

Answer 3

In the support database, create a synonym for the user table in the (remote) user database.

(Depending on user access privileges, this may require a linked server, or can simply done with server.database.schema.object notation)

Then, create a view that joins (local) support jobs with (remote) users.

Using this solution, you cannot enforce referential integrity though.