Billy Pang
tuxedo_man at hotmail.com
Wed Jun 30 00:01:28 CDT 2004
sorry... just reread what I wrote... instead of: "it is not possible for a sproc to query table in another db that the sproc does not reside in." rather, it should have read.... "it is difficult for a sproc to query other tables in other databases after the sproc has been created." Billy >From: "Billy Pang" <tuxedo_man at hotmail.com> >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer] Difference between views and queries >Date: Wed, 30 Jun 2004 04:56:50 +0000 > >oh boy, can't wait to get into this discussion :)...there is one example I >can think of.. the example would be based on the physical storage of the >sproc in a single db. it is not possible for a sproc to query table in >another db that the sproc does not reside in. > >To illustrate, let's say that there are three copies of the northwind db on >db server: northwind0, northwind1 and northwind2; all three have exact db >schema; > >Your goal is to develop a report that counts number of records in the >products table in each of the northwind databases. sometimes you may only >want to count records in northwind0 and northwind1 but not northwind2; >sometimes you may only want northwind2 and northwind1 but not northwind0; >and sometimes you want all three northwind dbs in your count. It is >possible to create a sp for each of the permutation but if a fourth >northwind db is introduced to the system, then the code base is doubled. > >The alternate solution would be to build dynamic sql to piece together >select statement referencing tables using the [db].[owner].[table] format; >which tables are pieced together is based on what the user selects as >criteria for report. Not elegant solution but it is a lot less code to >maintain. > >With that being said, dynamic sql is not as safe for reporting compared to >sproc. > >I think what Francis is trying to get at is that it is not possible for a >developer to claim that a particular "coding method" is never needed. >Rather, it is more correct to state that it is not likely that a particular >"coding method" is ever needed. Difference between the words "impossible" >and "improbable". Similiar to how a judge cannot hand out a verdict to >court case before the case is presented no matter obvious it may seem. > >Another point of reference on this topic would be look up "Validating User >Input" in BOL for best practices on this issue. > >Billy > > >>From: "Arthur Fuller" <artful at rogers.com> >>Reply-To: dba-sqlserver at databaseadvisors.com >>To: <dba-sqlserver at databaseadvisors.com> >>Subject: RE: [dba-SQLServer] Difference between views and queries >>Date: Tue, 29 Jun 2004 20:08:54 -0400 >> >>All I want is an example that cannot be coded by one or more sprocs. >> >>Arthur >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francis >>Harvey >>Sent: Tuesday, June 29, 2004 10:47 AM >>To: 'dba-sqlserver at databaseadvisors.com' >>Subject: RE: [dba-SQLServer] Difference between views and queries >> >> >>Francisco, >> >>I would like to consider the opinion of someone who shares my namesake >>as seriously as possible, but in this case, I believe you suffer from >>Arthur's problem of a lack of experience with the very specific problems >>I cited. It is fine to talk about not using dynamic SQL in general when >>using SQL Server, but just as with normalization, there are exceptions >>and no one should be stating it as an absolute rule or bad practice >>until they have reviewed the problems I mentioned. Again, I will state, >>for those particular problems, dynamic SQL is often the superior >>solution. >> >>Also, the "scaling" issue is teetering awfully close to using FUD. You >>can always use any programming method incorrectly, but correctly written >>dynamic SQL does not suffer from the horrible efficiency loss that not >>having a cached query plan would seem to imply. Similarly, the "time >>spent" argument is bad logic. I don't spend less time on my dynamic SQL >>solutions, I spend more time because the problems are difficult. Adding >>more time for a static SQL solution will not result in a better design >>or even a working design in some cases. >> >>I don't think you, Arthur or I are in disagreement over security, I just >>didn't like the oversimplified way security was described, so I chose to >>emphasize a particular point. I think this is an important part that >>should be highlighted. >> >>Francis R Harvey III >>WB 303, (301)294-3952 >>harveyf1 at westat.com >> >> >> > -----Original Message----- >> > From: dba-sqlserver-bounces at databaseadvisors.com >> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf >> > Of Francisco H Tapia >> > Sent: Monday, June 28, 2004 12:42 PM >> > To: dba-sqlserver at databaseadvisors.com >> > Subject: Re: [dba-SQLServer] Difference between views and queries >> > >> > >> > Francis, >> > I respectfully agree w/ Arthur on this. Dynamic SQL as a rule, is >> > bad design. Many times over you will find that there are comparable >> > solutions that often meet the demand much better, and "scale" much >> > better than that of dynamic sql. There is no "ONE" right way >> > of doing >> > things, that is the nature of this business. However >> > justifying dynamic >> > sql because of some time spent on design is imnsho incorrect. >> > You will >> > often find more times that enabling users/roles via Views/Sprocs and >> > Functions to be far superior solutions than to open wide dynamic sql. >> > >> > re: security. >> > Of course if you deny permissions on any object the take >> > precedence over >> > allows, this is the nature of security. That is why you must be >> > explicitly "SURE" when making DENY requests on any object. >> > NOT giving >> > rights to an object does not equeal REJECT. >> > >> > If I do not give a user rights to a view, under Role1, but >> > then give him >> > rights to that object via Role2. There will be 0 issues w/ >> > accessability. However If by poor planning I had a "REJECT" >> > rights in >> > Role1, then I'd have the issues you are stating. >> > >> > >> > >> > >> > Francis Harvey wrote On 6/25/2004 2:24 PM: >> > >> > >Arthur, >> > > >> > >Do some research on the basic solutions for SQL that has to >> > adjust for >> > >varying databases, conditions (dynamic search), or servers (different >> >> > >SQL dialects). Some designs also call for encapsulating >> > business logic >> > >outside of the data tier. In these situations, dynamic SQL is often >> > >the only logical choice. I have only had to deal with dynamic search >> > >myself, but this is enough to convince me of its usefulness. >> > > >> > >Describing permissions solely as additive in nature is >> > incorrect. This >> > >is the point I was making. >> > > >> > >Francis R Harvey III >> > >WB 303, (301)294-3952 >> > >harveyf1 at westat.com >><snip> >>_______________________________________________ >>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 >> > >_________________________________________________________________ >MSN Premium with Virus Guard and Firewall* from McAfee® Security : 2 months >FREE* >http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines