Arthur Fuller
artful at rogers.com
Tue Apr 6 01:37:48 CDT 2004
Yes, Robert, there was a problem in my syntax. A correction will appear in the next column. Bizarre thing was, in my saved query the syntax was right but when I pasted it into Word, somehow I screwed it up. Story of my life :) Arthur -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Djabarov, Robert Sent: Thursday, April 01, 2004 10:51 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Creating joins across multiple databases Based on what I've seen so far, it's not a good idea to do cross-db joins, and not only because of security issues as Francisco mentions, but also because of questions like "Who the heck is this guy and what is he doing in my swamp?" which immediately start popping up, which leads to finger-pointing when there is a slow-down or outage. It also violates database designation (DSS, OLTP) when it's OLTP for a native user while becoming a DSS for the new-comer. BTW, Francisco, did you copy the article or retyped it? If you copied it, I see a minor problem with Author's syntax... Robert Djabarov SQL Server & UDB Sr. SQL Server Administrator Phone: (210) 913-3148 Pager: (210) 753-3148 9800 Fredericksburg Rd. San Antonio, TX 78288 www.usaa.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco H Tapia Sent: Thursday, April 01, 2004 12:41 PM To: dba-SQLServer at databaseadvisors.com Subject: [dba-SQLServer] Creating joins across multiple databases I'm including the latest article from Arthur Fuller on the Builder.com SQL Newsletter because I hope it can bring forward some positive discussion. Plus I'm sure many of us have dealt with this particular situation or will deal with it in the future. /<BUILDER.COM SQL Newsletter>/* Creating joins across multiple databases* Enterprise databases often comprise several databases--one from production, another from marketing, and so on. All of the databases rely on a certain amount of common data, which is frequently copied to each database that needs it. However, this copying routine creates a new problem: skew. Where there is duplication, there is at least the opportunity for skew. The solution to this problem is often replication, or scheduled jobs that update each copy of the common data from one "master" database. This approach works, but sometimes a better method is to move all the common data into one database, and then access it as needed from the other databases. This eliminates both the skew and the replication jobs. This approach typically requires joining some local tables to the common tables. For example, suppose you have two databases: Common and Sales. The Customers table resides in the Common database, while the Orders table resides in the Sales database. You want to create a query that lists the Customer information along with the Order information. How do you join these two tables? The answer lies in prefixes. SQL Server uses a dot-delimited nomenclature that extends outward to the database and the server. To specify a column in a table in another database, name the other database: SELECT CustomerID FROM Common.Customers To join a table from the Common database to a table in the current (Sales) database, use this same nomenclature: SELECT * FROM Common.Customers INNER JOIN Orders ON Common.Customers.CustomerID = Orders.CustomerID Not every organization's application domain requires multiple databases. If your organization does require multiple databases, it's easy to create joins across databases. /Arthur Fuller has been developing database applications for more than 20 years. He frequently works with Access ADPs, Microsoft SQL 2000, MySQL, and .NET./ /<BUILDER.COM SQL Newsletter>/ Yes it is easy enough to join accross multiple databases and even accross several Sql Server linked servers :) (very cool)... however my concern and one that was not addressed in this article is the security aspect of it... say you just have a user who calls the report... does the user need to use an application who's using an application role and thus gets better than normal priviladges on the server? I have not been successful in adding new users to another database w/o also having to takes some hard steps; my solution was to ... Create VIEWS on the target database... add the user to that 2nd database, then give explicit selet crights on the views and then join accross from db2 to db1 via the sproc using the views instead of the tables... so while the data is not duplicated, the user is, and now has rights to base views that represent tables :( , so because I think I might be doing this wrong.. I'm hoping Arthur or someone else will chime in with a few clues :) Thanks, -- -Francisco _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com