cross-database view/query 1

[cdck]

I'm working with two separate SQL Server databases on the same server; one is a database tied to third-party software, and the other is a supplemental database we created to hold tables for the applications developed to fill in gaps the third-party software did not handle. In the past these applications consisted of Access MDBs which connect data from tables in both databases and store Queries containing this combined data which are then further filtered with more queries.

We are now working to build an application in Visual Basic which connects to these databases and avoids using Access. However, I am having difficulty finding information regarding how to continue combining this data.

In searches, I find references to cross-database views, queries, and stored procedures, but while I am finding many questions from users who have run into issues, I cannot find information on how to write them in the first place.

What I need to know is:

1. If you are writing a cross-database view, can it be stored in one of the two databases involved?
2. The samples I've seen all refer to the fields as "database.table.field" while querying - which makes sense, but is there anywhere that you need to identify the outside database in order for the query to know where to look? (Is there a need for some sort of connection string, or does the server just understand that you are referring to this database over here when you do that?)
3. Is anyone aware of content that teaches you how to correctly create cross-database views which I can use to understand this technique?

Cheryl dc Kern

 

[RiverGuy]

1. Yes, you can store the views in either database. You would probably want to store them in your supplemental database.

2. You fully qualify an object from within the same server this way: DatabaseName.SchemaName.ObjectName. So if your connection string was connected to Database1, and you wanted to write a query against a table in Database2, it would look like this SELECT * FROM Database2.SchemaName.ObjectName.

Also keep in mind that when working with long names you will sometimes want to alias your tables within your queries. Take the same example of being connected to Database1

SELECT a.ID, a.ClientName, b.SalesAmount
FROM CustomTable a
INNER JOIN Database2.dbo.ThirdPartyTable b
ON a.ID = b.ID

In this example, I do not need to worry about specifying the table name in the select list with the shortened aliases of a and b.

 

[cdck]

Thank you so very much, this is exactly what I needed, and gives me a basis for understanding the information in all other posts I've found. Thanks again!

Cheryl dc Kern