Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2009-06-02 : 10:42:25
|
| I am brand new to this forum and fairly new to SQL Server. I am tasked with getting some reports off a MISYS server, using the SQL from them to create new reports directly off SQL Server Reporting Services, etc. I am not totally sure how I want to go about this, but one problem I had was that the existing queries have no database name or schema in the SQL, but I wanted to try to store them as views, but not in the same schema where everything else is stored. So I thought about storing them in another 'test' database (on the same server). The problem is that the SQL then looks at the objects in its own database. I know I could change them to include the database name and the schema everywhere, but that would be time consuming and error prone. Is there a way, at the top of a query, view, stored procedure, or whatever, to tell the SQL to look at a certain database and schema, like a 'default' schema just for the query?Duane |
|
|
SQLRatankalwa
Starting Member
42 Posts |
Posted - 2009-06-02 : 10:55:05
|
| The Default schema is "dbo"You can create a new user and set the default schema in your database.That will execute using the default schema set while creating the user.Ratan KalwaSQL Server Professionals User Grouphttp://www.revalsys.com |
 |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-02 : 11:52:54
|
| Good idea - Thank you. I didn't even think of that. Now, if I do that, though, all the objects will still be created in that schema, correct? Is there anyway to keep any views or other object separate yet still point to that "default" schema?Duane |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-02 : 11:54:05
|
quote:
Originally posted by duanecwilson
I am brand new to this forum and fairly new to SQL Server. I am tasked with getting some reports off a MISYS server, using the SQL from them to create new reports directly off SQL Server Reporting Services, etc. I am not totally sure how I want to go about this, but one problem I had was that the existing queries have no database name or schema in the SQL, but I wanted to try to store them as views, but not in the same schema where everything else is stored. So I thought about storing them in another 'test' database (on the same server). The problem is that the SQL then looks at the objects in its own database. I know I could change them to include the database name and the schema everywhere, but that would be time consuming and error prone. Is there a way, at the top of a query, view, stored procedure, or whatever, to tell the SQL to look at a certain database and schema, like a 'default' schema just for the query?Duane
just identify what schema those objects currently belong too. then for all users you want, add them to a group for which default schema is the above schema. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-02 : 13:18:52
|
| In SQL Server 2005, you can set a user's default schema to be something else. In fact, in previous versions of SQL Server, the default schema is in fact the user's schema. this is why some people demand that all tables, procs, views, etc. be fully qualified with dbo.[name]. |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-02 : 14:55:23
|
| This has helped me understand more. The lightbulb clicked on, at least partially. If you can bear with me, this relates to my first question: Let's say I have a query that is written as SELECT FirstName, LastName, EmpID FROM Employees. It has been running like this on the MISYS Server - an independent application that uses SQL Server as its back end. I think it is like Access pass-through queries. Anyway, when I went into SQL Management Studio, I had to click on the MHC database before the query would run because it didn't know what database or schema to use. But the tables and views the SQL is looking for is MHC.dbo. If I write the SQL so it says SELECT MHC.dbo.LastName, MHC.dbo.FirstName, MHC.dbo.LastName, MHC.dbo.EmpID FROM MHC.dbo.Employees, then it works fine from anywhere. But if my user has a different default schema, then the "generic" version looks in his schema. I thought that maybe in a SQL expression I could tell it once to "default" to a different schema without fiddling with the SQL, some statements of which are hundreds of lines. I would like to create views, etc. using the present SQL, without alteration, but use them in a different "account" so the user can keep track of his own views without adding more views to the .dbo schema. Can this be done? Or will I have to change all the views. These views run fine in the .dbo. Right now, from what I understand, if they create a view using their own schema, it will have their own schema in the long name, but it will not run views in .dbo unless they specify .dbo in their SQL statement.I know I must be overcomplicating this, but it is not crystal clear to me yet.Duane |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-02 : 15:04:26
|
quote:
Originally posted by mcrowley
In SQL Server 2005, you can set a user's default schema to be something else. In fact, in previous versions of SQL Server, the default schema is in fact the user's schema. this is why some people demand that all tables, procs, views, etc. be fully qualified with dbo.[name].
A quick question here, too. If you set that schema to something else, won't every view and object they create also be in that schema? I would like to be able to create things in my own schema that would run on another schema by default. That way, I could keep track of my own objects and yet still have everything run in the other schema without having to fully qualify every long object name in those long queries. Obviously, I would be willing to set that right in the query if that was possible.Let's say I have database mhc where all the objects created so far are .dbo. I want to go in there and create some test views, for example. If my default schema is set to .dbo, all the objects I create would be visible to everyone else using a default schema of .dbo, wouldn't they? If I have my own schema .abc, for example, then I would think my objects would run only .abc schema objects, wouldn't they (unless I was able to specify in the SQL)? If I am not understanding correctly, please explain. Thank you.Duane |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-02 : 15:23:59
|
| I think I answered my own question. I am surprised that none of you gurus thought of it. I must just have trouble making my own question clear. But would one of you verify that I am on the right track using the following statements at the top of any sql query or procedure:USE [mhc]GOSELECT * FROM ..., etc.Then, I could use the dbo objects without qualifying.Duane |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-02 : 15:39:56
|
Having users create their own database objects will make a system unusable after a while. Very likely a short while.But to clear up the question itself, here is how SQL Server does name resolution:When a query specifies a table with no schema, the default schema is checked first. if the table is found, that table is used. If the table is not found, the dbo schema is checked. If the table is found there, then that table (from the dbo schema) is used in the query. If the schema is specified, only that table will do. No other schema is considered.When a user tries to create an object and the user has not specified what schema the object should belong to, the object will be placed in the user's default schema. Now. Here is where it gets interesting. Suppose User A creates a bunch of tables in his default schema (we will call it "A"). So he has tables [A].[Orders] and [A].[LineItems]. User B also creates tables in her default schema (let's call it "B"). She has only [B].[Orders]. Both users populate these tables with the orders that interest them from the tables [dbo].[Orders] and [dbo].[LineItems] (not necessarily the same orders, mind you). All of this happens in the same database. User A works for a bit, and declares that he has found something interesting, and sends User B a query like the following:select sum([LineItems].[qty]), [Orders].[OrderNumber]from [Orders] join [LineItems] on [Orders].[OrderNumber] = [LineItems].[OrderNumber]group by [Orders].[OrderNumber] Now, tell me why the results that User A got from this query will not necessarily be the same as the results that User B gets? Both will get results. No objects can not be resolved. The syntax is fine. Nothing will indicate that there could be an error. But you will still have two confused, and likely very angry users on your hands. |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-02 : 16:18:25
|
| This is all a good point. So, then, if I create views (I am going to be doing reports, so simple selects should be all I need), as long as I USE the right database, my queries will get the .dbo views and tables, correct (regardless of the default schema)? So my last question is, then, if I create a user whose default schema is test, then the objects he creates will also be .test, but will still access .dbo (as long as no objects with the same name exist in the .test schema)? Am I getting close here?Duane |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-03 : 10:03:13
|
| When you USE a database, your queries will get tables, views, or stored procedures from your default schema, unless you declare which schema you want them from.Your second point is correct. If no object exists in the default schema with the name specified, the dbo schema will be checked for that object.Of course, duplicate object names across multiple schemas is going to be a pain to troubleshoot. Especially if the folks you work with do not qualify their object names enough. |
|
|
|
Abercrombie_LV
Starting Member
1 Post |
Posted - 2010-01-19 : 16:02:22
|
| When I use local web server code and I connecting using a SQL Login with a default schema of LO my SQL Profiler shows that the account connects and references the right schema. When the same code is sent to a remote web server with the same connection string, the LO schema is not referenced, even when we explicitly tell it to use the other schema! I comes back saying that the proc name in the dbo schema can't be found. If we connect as the user in Management studio it works. Connect from a local web server (XP SP3 with IIS 5.1) It works. Connect from a remote IIS 6.0 on Win 2k3, it doesn't work - tries to call the dbo schema instead of the LO schema. The website in question uses WCF. Is there anything WCF side that I should look for (as a DBA asking fellow DBAs)? Thanks |
|
|
|
|
|
|
|
|
Setting default schema in SQL??