| Author |
Topic |
|
dougeth
Starting Member
4 Posts |
 Posted - 2008-01-30 : 18:37:35
|
| Hello All,I am a newbie when it comes to MS SQL Server administration and I am hoping you can help me out. We are migrating from a shared webhosting platform to our own internal dedicated web / MS SQL (2005) server and have encountered an error that appears to be stemming form Query Syntax. In our old system we could simply query via the following format:"Select [Column] from [Table Name]"But on the SQL Server I just set up we have to query via this format:"Select [Column] from [Database Name].[Table Name]"We have literally hundreds of preprogrammed Queries and it would be quite difficult to change them all. Does anyone know how I can set up SQL Server to run so that our quieries do not require the DatabaseName in the statement? I have placed the connection code below, if that helps any. Set objConn= Server.CreateObject("ADODB.Connection")objConn.Open "Provider=SQLOLEDB; Data Source =; Initial Catalog =; User Id =; Password=" Set objRec = Server.CreateObject("ADODB.Recordset"). |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-30 : 19:34:39
|
| It should be"Select [Column] from [Database Name].<[SchemaOwner].[Table Name]"Example:Select * from Pubs.dbo.JobsDinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dougeth
Starting Member
4 Posts |
Posted - 2008-01-30 : 21:41:29
|
| Dinakar,Thank you for your post. With your comments well taken, how can I go about setting up SQL Server so all I have to do is specify a table name in the query. As I stated, that is what we are currently doing today.Thank you again for your post and I hope you (or someone) can help me |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-31 : 04:44:36
|
| You should specify your database in the Initial Catalog part of your connection. Otherwise, in SQL Server, set the default database of the user to the database you want to use. Both of these will set the scope of the query to the database, and in that case you do not need three part naming. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-31 : 13:39:38
|
| Specifying the schema owner improves efficiency as SQL Server has to resove who the object belongs to and when you specify it, you save some resources so SQL Server doesnt have to do that and also helps in query plan reuse.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-01-31 : 14:14:15
|
As identified in prior posts there are ways to make it so you do not have to require the fully qualified object syntax. However, I would strongly recommend to NOT do that. There are several issues with doing so. Just off the top of my head: - Performance is diminished - without the fully qualified syntax it will cause SQL Server to do a few additional checks to get the right object. This can be huge if you are doing anything repeatedly or recursively.
- If you have multiple databases then some of the objects will not be able to be resolved since the a query runs in the context of the current database.
- If you have tables with the same name in different databases... ouch ugly.
Personally, if someone is requesting this of you, I would tell them forget it. IMO the only way to go is to use the fully qualified syntax. |
|
|
|
dougeth
Starting Member
4 Posts |
Posted - 2008-01-31 : 14:24:03
|
quote:
Originally posted by tfountain
As identified in prior posts there are ways to make it so you do not have to require the fully qualified object syntax. However, I would strongly recommend to NOT do that. There are several issues with doing so. Just off the top of my head: - Performance is diminished - without the fully qualified syntax it will cause SQL Server to do a few additional checks to get the right object. This can be huge if you are doing anything repeatedly or recursively.
- If you have multiple databases then some of the objects will not be able to be resolved since the a query runs in the context of the current database.
- If you have tables with the same name in different databases... ouch ugly.
Personally, if someone is requesting this of you, I would tell them forget it. IMO the only way to go is to use the fully qualified syntax.
Thank you for your advice and I sincerely agree with you. Unfortuneatly, my employer has already invested the time and money in making this Queries and I am not authorized to change them. I have tried the suggestions mentioned earlier, but I am still getting an error indicating that the Object (Table Name) is not recognized. Any thoughts as to what I am doing wrong? |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-01-31 : 15:42:30
|
| If that's the case then you have 2 options as identified above:1) Set the Initial Catalog of the connection string of your code to the database where all the objects reside.2) On the database server, set the default database of the user(s) that connect to the database to the database where all the objects reside.If you have tried these and they do not work, verify that all the objects being referenced actually do reside in the same database.One thing to note, when we are using the term database, we are not referring to the server itself. A server can contain multiple instances and each instance can contain multiple databases.If your situation is such that you find you have objects in multiple databases you need to find a way to convince your employer to follow the best practices of using fully qualified names. Honestly, the only reason to not use fully qualified names are wrong reasons. There are many pros to use them, and many cons to not.Let me pose a situation to you - how much time are you spending trying to get this hack to work? How much time would it take you to go through all your database objects and find all references and change them to use the fully qualified syntax? I bet in the end you would have spent less time making the code changes instead of your current approach.At a minimum (assuming all your objects are owned by and not individual users), at least qualify your objects with the owner (well, now in 2005 it's the schema). That will still buy you the performance benefits of not having to resolve the owner/schema of the object. |
|
|
|
dougeth
Starting Member
4 Posts |
Posted - 2008-01-31 : 15:47:39
|
quote:
Originally posted by tfountain
If that's the case then you have 2 options as identified above:1) Set the Initial Catalog of the connection string of your code to the database where all the objects reside.2) On the database server, set the default database of the user(s) that connect to the database to the database where all the objects reside.If you have tried these and they do not work, verify that all the objects being referenced actually do reside in the same database.One thing to note, when we are using the term database, we are not referring to the server itself. A server can contain multiple instances and each instance can contain multiple databases.If your situation is such that you find you have objects in multiple databases you need to find a way to convince your employer to follow the best practices of using fully qualified names. Honestly, the only reason to not use fully qualified names are wrong reasons. There are many pros to use them, and many cons to not.Let me pose a situation to you - how much time are you spending trying to get this hack to work? How much time would it take you to go through all your database objects and find all references and change them to use the fully qualified syntax? I bet in the end you would have spent less time making the code changes instead of your current approach.
Thank you for your response. I will make a last attempt to try and get the sever to work and if I still can not get the queries to work right, I will let "the boss" know that we need to update our queries. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-01 : 05:38:37
|
| Connect to the server using one (or both) of the connection suggestions provided so far and execute "SELECT DB_NAME()". If this does not match the database you are trying to use then you have set something up incorrectly. Also check the default schema of the user and ensure that this matches the schema of the object. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-01 : 06:00:16
|
| .... or you could merely open a connection and, whilst it is open, execute & examine sp_who2 in SSMS and see what db the connection is using. |
|
|
|
|
Differences in Query Syntax