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 |
|
sqlconfused
Yak Posting Veteran
50 Posts |
 Posted - 2012-09-25 : 03:48:57
|
HelloTonight I backed up my SQL 2005 server database (through Websitepanel, a GUI interface that my webhoste uses) and restored it onto a 2008 server for which I'd created a login and password. According to the ISP this is all that is required to convert from 2005 to 2008 using the GUI Websitepanel.After the backup and restore, I then changed all my connection strings in my code to reflect the new IP of the 2008 SQL server.I uploaded my files, and tried my site.Right away I received an error for a simple SQL string which was "select ip from bans". I logged into Server Management Studio Express 2008 and confirmed this wasn't valid. The table existed as did the column and I was puzzled as to why a simple Select didn't work.After wasting an hour trying to get the 2008 SMS Express to run (what a mess), and figure out they removed the Open Table option in lieu of Open First 100 Rows, I realized that what I needed to do was change my SQL string:OLD : select ip from bansNEW: select ip from [oxp].[sqluser].[bans] Under 2005 my old username was sqluser. My 2005 database is about 30 tables, half of which are 'dbo.anytablename' and the other half are 'sqluser.anytablename'My new username is oxp. So now I'm faced with rewriting 100+ lines of code to read:[oxp].[sqluser].[tablename]This is not something I want to do. Is there a way to, either on the old 2005 DB or the new 2008 DB, change the schemas to be unified, or to at least be the same username?One thing I haven't tried is deleting the old username 'sqluser' and recreating it in the 2008 config. I'm hesitant to get rid of the old database because as I found out tonight, I may need to fall back upon it to have a operational site.I suspect even if I did this, I'd end up with [oxp].[sqluser].[tablename] even if I was logged on as sqluser.I've asked the ISP for help but this forum has always been very helpful and knowledgable so I'm asking here too.  |
|
|
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2012-09-28 : 14:44:11
|
| Well since nobody answered this, I changed all schemas to dbo.tablename and backed it up, restored to a 2008 SQL server and it worked right away.Problem solved! |
 |
|
|
komkrit
Yak Posting Veteran
60 Posts |
Posted - 2012-10-01 : 10:15:43
|
| Three part object name has a format like this[db name].[schema name].[table name]It obviously that you ISP createdatabase name=[oxp]then create schema name = [sqluser]Then put every tables into newly created. So multipart format shown like this work.[oxp].[sqluser].[bans] Finally they did not set default database for your username so your sql user could not access table without multipart format.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulWelcome for all questions. Let us know if our solution solved your problem. |
|
|
|
|
|
|
|
|
2005 to 2008 upgrade - schema issue