Arthur Fuller
artful at rogers.com
Tue Apr 6 01:45:33 CDT 2004
Thanks for the vote of confidence, Francisco, but Robert is quite right. Somehow in my copy/paste operation I screwed it up. All the egg belongs on my face. Meanwhile I'm giving some thought to Robert's objections to the strategy of "related" dbs (which I freely admit I wouldn't do if the choice were always mine, but sometimes it isn't). Arthur -----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 1:00 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Creating joins across multiple databases Knowing Arthur, i'm sure that was a publisher's err, not an author err, from posting to HTML... Djabarov, Robert said the following on 4/1/2004 12:43 PM: >...He's consistently missing schema owner...Just an observation ;) > >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 2:41 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Creating joins across multiple databases > > >copy paste... >Djabarov, Robert said the following on 4/1/2004 10:51 AM: > > > >>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