0

I have a two big programs that connect to a SQL Server 2005 database.

Now we will migrate to a new server with SQL Server 2008.. the programs don't work anymore when connected to the new server, the cause is that in all the queries in the programs only table names are used, and they are not dbo tables.. so SQL Server 2008 doesn't recognise them, unless I use the schema name before the table name...

It is very very difficult for me to change all the queries in the two programs to add the schema name before the tables names.

I read in this forum that if I specify the default schema the problem will be solved.. but it haven't been solved though.

The only solution that seems to be working is when I changed the schema of the table to dbo.. but I am not sure if this action will be OK or will it cause some other problems related to this modification?

Is there any better solution?

Will changing the schema of the tables cause me other kind of problems?

Many thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anwar
  • 35
  • 5
  • You simply need to set the default schema for the User the application uses to match whatever schema the non-dbo tables are in. Also, learn to always use schema.object when addressing items in the database. Future code maintainers and the query optimizer will thank you – billinkc May 31 '14 at 04:24
  • I did, but it didn't solve the problem.. maybe I didn't do it correctly : In Microsoft Sql Server Management Studio, under [database]->security->users I right clicked the user, and chosen "Properties".. In the first page, I have default schema already set to the desired name. – Anwar May 31 '14 at 05:02
  • 1
    This is why it is best practice in SQL to always use schema-qualified names. [TechNet](http://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx), [SQL Server Central](http://ask.sqlservercentral.com/questions/89280/two-part-naming-convention-and-performance.html), [SQL Server Mag](http://sqlmag.com/t-sql/t-sql-best-practices-part-1). – Greenstone Walker May 31 '14 at 06:34
  • You are right, I just learned that (as before it never caused any problems).. And in fact, the program in question wasn't developed by myself :) – Anwar May 31 '14 at 08:18
  • Thank you marc_s for the editing, I learned from that too :) – Anwar May 31 '14 at 08:21

1 Answers1

0

Default schemea will work for you. What are the issue with this approach?

Change schema name will cause a big issue and not advisable. Where and how much schema name change?(just think).

You just set a default schema with only one procedure first and check, if this is ok. then change the whole database schema.

https://dba.stackexchange.com/questions/21158/net-sql-server-authentication-schema-issue

In sql server 2005, how do I change the "schema" of a table without losing any data?

Change Schema Name Of Table In SQL

Best practice for SQL Server 2008 schema change

Community
  • 1
  • 1
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
  • I did set the default schema, but it didn't solve the problem.. maybe I didn't do it correctly : In Microsoft Sql Server Management Studio, under [database]->security->users I right clicked the user, and chosen "Properties".. In the first page, I have default schema already set to the desired name. – Anwar May 31 '14 at 05:09
  • In above 2nd and 3rd link, it will show to change schema name. Also show other link while change schema name. – Ajay2707 May 31 '14 at 05:17
  • Ajay2707, thank you very much for you answer. But I know how to change the schema name of a table.. I did it for some tables and it worked, my question was about if this practice would cause me other kind of problems, and your answer was : Yes it will cause big issue and is not advisable.. and my second question was if there is any other solution? The solution of setting the default schema for the user (that you proposed first) didn't work for me.. did I do any thing wrong? I described the way how I did it in my comment to your first answer – Anwar May 31 '14 at 05:24
  • Dear I understand your issue. When you migrating,Good to generate either script with your schema or other option is generate script without schema and set default schema in sql,Which you want and then run the script.So retest as I suggest.Do from starting,Set everything before run script. Not change manually after run the script or migrating.This will definitely work for you.I had the same issue.First I include the schema as my default schema is dbo, but in production it is other and had issue. Finally solved as I was generate the script without sc. name,So wil take default schema and it work. – Ajay2707 May 31 '14 at 05:41
  • Thank you very much Ajay2707. I will do as you said, I hope this would solve it.. thanks again – Anwar May 31 '14 at 05:47