|
sqlconfused
Starting Member
Canada
17 Posts |
 Posted - 09/25/2012 : 03:48:57
|
Hello
Tonight 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 bans
NEW: 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. 
|
Edited by - sqlconfused on 09/25/2012 03:50:50
|
|
2005 to 2008 upgrade - schema issue
