Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Re: avoiding select ... from [Dbname].[dbo].[TableName] so can just use [TableName]

Expand Messages
  • Paul Livengood
    one short term fix that I WOULD NOT RECOMMEND is to create views to the tables in the default Schema.   If the object (table) is in schema Finance.TableName
    Message 1 of 7 , Feb 22, 2012
    • 0 Attachment
      one "short term fix" that I WOULD NOT RECOMMEND is to create views to the tables in the default Schema.
       
      If the object (table) is in schema Finance.TableName and the user has a default schema of dbo then you can create a view of dbo.TableName that looks at the table Finance.TableName.
       
      I would not recommend this as it would slow down your system without a lot of index work, and it would bloat the size of your db, not to mention the amount of management would be insane...

      Paul


      ________________________________
      From: Charles Carroll <911@...>
      To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
      Sent: Wednesday, February 22, 2012 11:30 AM
      Subject: [SQLQueriesNoCode] Re: avoiding select ... from [Dbname].[dbo].[TableName] so can just use [TableName]

      I control schemas and db setup for next month or 2 and am ignorant on
      subject. I control db not client. Client issues will come later after
      testing on dbs I setup in house and control 100%

      On Wednesday, February 22, 2012, Arnie Rowland <arnie@...> wrote:
      >
      >
      > I don't know of any connection string fixes for these kinds of multiple
      > schema issues where objects may exist in non-default schemas.
      >
      > Bottom line Charles, is that IF the client is now using multiple
      > schemas, you are toast. To try and handle multiple schemas in constants
      > would, in my opinion, require quite a bit of decision code. You could
      > search for the object and discover the schema -unless the culient has
      > re-used an object name in a different schema (it happens). You 'could'
      > conduct the search after error when trying the default schemas. But what
      > a mess that code would be...
      >
      > You need to consider the schema as part of the object name and handle it
      > as such.
      >
      > And I certainly understand the pressing urgency to create a short term
      > fix. It just may not be possible though...
      >
      > Unless, of course, the client is willing to abandon the multiple schema
      > design.
      >
      > Regards,
      >
      > Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172
      >
      > "You cannot do a kindness too soon because you never know how soon
      > it will be too late."
      > -Ralph Waldo Emerson
      >
      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
      > Sent: Wednesday, February 22, 2012 9:51 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: Re: [SQLQueriesNoCode] avoiding select ... from
      > [Dbname].[dbo].[TableName] so can just use [TableName]
      >
      > So what connection string will avoid the two part prefix in my context.
      >
      > I am looking for the short term fix i.e.
      > 1. what to do in connection string
      > 2. an article or command to change owner/schema/permissions/etc. so I
      > can use just table name in short term.
      >
      > I am aware of the issues that our need may create problems in the future
      > and will examine the options in future and refactor - this code drop
      > will be one of many several a week I can take the ideal solution in
      > future drop AND WILL - but for now I need the short term fix. We are in
      > crunch mode to test 20 other aspects of my application: architecture,
      > data quality, algorithms, UI, XML Parsing, reporting features,
      > dashboard/BI features, deployment issues of the many pieces (back end,
      > API clients, silverlight front end, winForm front end, webservice
      > issues) etc. and many code changes will be made -- AFTER QA and my
      > bosses run this code and work with the data by then I can stop and
      > breath and get a nirvana/correct way I need a decent workaround for now.
      >
      > I am aware of the issues that our need for short term fix may create
      > problems in the future and will examine the options in future and
      > refactor
      > - this code drop will be one of many several a week I can take the ideal
      > solution in future drop AND WILL - but for now I need the short term fix
      > that I will remove later.
      >
      > On Wed, Feb 22, 2012 at 11:04 AM, Arnie Rowland <arnie@...> wrote:
      >
      >> **
      >>
      >>
      >> Let me add to Paul's explanation.
      >>
      >> Many organizations are now starting to use multiple schemas just as a
      >> developer might use multiple namespaces. Schemas allow better security
      >
      >> AND organization -especially useful in very large can complex
      > databases.
      >>
      >> It is quite possible that a SQL Server object that is needed (table,
      >> procedure, function, etc.) is NOT in the dbo schema, NOT in the user's
      >
      >> default schema, and NOT in any default schema. Without the schema
      >> prefix, an error condition will occur.
      >>
      >> The 'best' practice is to get over it and start using two-part names
      >> at the very minimum ([schema].[object]).
      >>
      >> Regards,
      >>
      >> Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172 Ph.D.,
      >> MCITP, MCDBA, MCSE, MVP (SQL Server)
      >>
      >> "You cannot do a kindness too soon because you never know how soon it
      >> will be too late."
      >> -Ralph Waldo Emerson
      >>
      >>
      >>
      >>
      >> -----Original Message-----
      >> From: SQLQueriesNoCode@yahoogroups.com [mailto:
      >> SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
      >> Sent: Wednesday, February 22, 2012 7:53 AM
      >> To: SQLQueriesNoCode@yahoogroups.com
      >> Subject: Re: [SQLQueriesNoCode] avoiding select ... from
      >> [Dbname].[dbo].[TableName] so can just use [TableName]
      >>
      >> Most SQL Objects (Tables, Stored Procedure, Views, Functions, etc)
      >> have a four part name.
      >> [server].[database].[schema].[object name]
      >>
      >> when you write a select statement with just the name (select a,b,c
      >> from
      >> [TableWhatever]) SQL automatically fills in the other three parts of
      >> the name [server].[database].[schema].
      >>
      >> It decides what to fill them in with based on several factors (too
      >> many to go into in this email), but most are either defaults or
      >> current connections.
      >> IE;
      >> server - will be the server you are currently connected to database -
      >> will be the database you are currently connected to...if you do not
      >> specify a database during a connection it will connect to the default
      >> database of the user connecting.
      >> schema - will be the default schema for the users security.
      >>
      >> So, if you want to avoild using all four parts then you need setup
      >> defaults correctly.
      >>
      >>
      >> does that help?
      >> Paul
      >>
      >>
      >> ________________________________
      >> From: Charles Carroll <911@...>
      >> To: SQLQueriesNoCode@yahoogroups.com
      >> Sent: Wednesday, February 22, 2012 8:07 AM
      >> Subject: [SQLQueriesNoCode] avoiding select ... from
      >> [Dbname].[dbo].[TableName] so can just use [TableName]
      >>
      >>
      >>
      >>
      >>
      >> I am fine with SQL Server stored procedures writing and calling them
      >> but one thing always bugged me the DBAs at previous jobs took care of
      >> ...
      >>
      >> Lets start with a simple example my C# code for example would say:
      >>
      >> select a,b,c from [TableWhatever]
      >>
      >> => This works fine on SQL Server Databases some of my DBAs create <=
      >>
      >> But on some of the databases DBAs create the same query would have to
      >> be phrased as:
      >>
      >> select a,b,c from [dbo].[DatabaseWhatever].[TableWhatever]
      >>
      >> **********************************************************************
      >> *****************************************************************
      >>
      >> MAIN QUESTION:
      >>
      >> How can I administer permissions so the former query always works I
      >> hate having to have 2 prefixes before each table.
      >>
      >> PS what I know about SQL Server permissions could fit in a thimble...
      >> **********************************************************************
      >> *******************************************************************
      >>
      >>
      >> More Details:
      >>
      >> I store table names as constants i.e.
      >>
      >> private const string TableNamePrefix = "[ARDash].[dbo].";
      >>
      >> public static string ConfigI
      >
      >


      [Non-text portions of this message have been removed]



      ------------------------------------

      Yahoo! Groups Links



      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.