Hello, I’ve received a website that uses sql server on the live environment. In the code at many places sql is created, say:
select * from mytable
with which the db is queried. This works fine.
However, locally on my dev machine, I use sql server express edition. It looks like select * from mytable doesn’t work there, but instead I should use:
select * from [mylocaldb].[dbo].[mytable]
Without the brackets, it just doesn’t work. Changing the code is not an option (at the moment).
How can I get my local site to work? Is there a setting I can change or am I missing something else?
All you need to connect to SQL express through VS 2005 IDE .Connect to server options. Once you connected you need to open a query browser there itself and try your query. Have your tried this. Generally Sql instance is DBO by default and all rights are given to DBO.So this works fine.
The sql string that’s build in the code is select * from table. When I copy this code in the sql server express edition management studio, it doesn’t work, while it does work in the live sql server db.
What does work is the [dbo].[table] version, but this is not what the code is creating…
Or… do the database objects change the code to something with brackets behind the scenes? In that case it might be the connection string after all. However, if I change the connection string to something else, I get different errors, so I think the string is correct.
The brackets is just to “escape” the names of the objects used in the query, they are only needed if one of the name of an objects conflict with a SQL keyword. They are not required, unless you have a conflict.
Your connection in sqlexpress is probably pointing to a diferent DB (master probably) so the need to use “database.schema.table” if you point the connection to your database “mylocaldb” you can just use mytable in the queries as dbo is the default schema.
That last part was correct, many thanks! I’ve got the statement working in sqlexpress. Now to get it working in the code. I was looking in the wrong direction there, then. I’ll start playing around with the connection string a bit more. Thanks for the help Pufa!