| Author |
 Topic  |
|
chp
Starting Member
16 Posts |
 Posted - 19 December 2008 : 09:03:38
|
Hi,
My host recently moved my Snitz database from a shared SQL 2000 server onto my own dedicated machine. In theory I was supposed to change the connection string and I'd be up and running.
The database has been migrated and all the tables are there, everything looks identical - no data lost, no changed permissions, nothing. But when I point my connection string to the new database, it doesn't "see" it.
I get this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'FORUM_MEMBERS'.
/forum/inc_header.asp, line 222
I ran setup.asp and it said, "You need to create all the tables in the database before you can start using the forum."
I did this on a backup version of the database, just to see if it helped, and it created duplicates of tables that were already there. Names exactly the same, everything. Only empty, which isn't what I need.
I am stumped. Does anyone have an idea what could be going wrong here? My database has been moved a few times before and I've never encountered this particular problem. I'd appreciate any ideas. For right now I'm still running off the old box until I can get this figured out.
Clara< |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 December 2008 : 10:54:01
|
| Probably you have a new user name and your tables are owned by the previous user name and so they can't be found. One way to solve it would be to change the ownership of the tables to the new user, or, even easier, to create a database user with the same user name used in the previous machine.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
chp
Starting Member
16 Posts |
Posted - 19 December 2008 : 11:46:50
|
Hi Rui and Podge - thank you both!
It would appear that I am definitely connecting to the correct SQL Server and the correct database on that server. I base this conclusion on the fact that when I was asked to run setup, it created duplicate tables in the very same database. Am I drawing the correct conclusion with that one?
In the connection string, I was told by my host to change the hostname to "localhost" because it is now on my machine, so that's what I've done.
The strange thing is that the usernames and permissions all transferred. I've been comparing the databases side by side, checking the table permissions, users, etc., and it appears that my tables are still owned by the same username. When I test my connection string using that username in the ODBC Data Source Administrator, it goes through without a hitch. But when I change that connection string in my config file, poof, Invalid object name 'FORUM_MEMBERS'
It's as if the forum sees my new database server, yes, but it doesn't acknowledge the existence of the 2GB forum database that's in there!< |
Edited by - chp on 19 December 2008 11:56:39 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 December 2008 : 12:03:32
|
I admit that you're using Enterprise Manager, right? You also said there were two sets of tables, right? If so, can you tell me how are the table names being shown?
For example you have a dbo.FORUM_MEMBERS and a userx.FORUM_MEMBERs, or similar? If you can tell me what you had in the old database and in the new one, I can probably tell you how to overcome that.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 19 December 2008 : 12:13:09
|
as rui said in a previous post, it is all down to the owner of the tables
there is a simple fix for this, and that is to change the strTableprefix values in config.asp and prepend the db owner to the table name, so that instead of FORUM_ it says dbo.FORUM_ or whatever the owner of the actual tables is.
might be worth asking your host how they migrated the database then I could give you a better idea of a proper fix, it could be that if they did a backup/restore that your user has orphaned rights to the db which need fixing.
< |
|
|
|
chp
Starting Member
16 Posts |
Posted - 19 December 2008 : 12:14:26
|
Yes - I'm using Enterprise Manager.
I deleted the brand new empty duplicate tables that were created when I ran setup.asp, but I think you're on to something. If my memory serves me right, they were created by dbo and not by the username that created the original tables (and that's the one that still owns all the tables in the new db, and it's the username I use to log in).< |
|
|
|
chp
Starting Member
16 Posts |
Posted - 19 December 2008 : 12:21:16
|
Hi - sorry I missed your post, HuwR.
First, the tables are all listed simply as "FORUM_...." with no dbo or userx (or my username) prefix. But under the "Owner" column, they are currently all owned by userx. When I ran setup.asp and it created the duplicate tables, it appears to me that they were *not* owned by the same userx, that they were by the dbo.
I tried changing the strTableprefix value so it said userx.FORUM_... and it still gave me an "invalid object name" error.
Edited to add: My host copied the database and then ran a restore to write all the tables. (Their language, not mine.)< |
Edited by - chp on 19 December 2008 12:23:18 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
chp
Starting Member
16 Posts |
Posted - 19 December 2008 : 12:26:29
|
Thank you Rui!
I changed the strTableprefix values in my config.asp so that instead of FORUM_ it says cparkes.FORUM (which is the owner of the tables). When I uploaded the new config.asp file and tried to pull up my site, it gave me an "invalid object name" error. Do I need to re-run setup.asp or anything to set the fix?< |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 19 December 2008 : 12:30:17
|
it sounds that your user is not actually the owner of the tables according to the new server, so you are suffering from what is called orphaned users, to fix it, open a query window in enterprise manager and type in the following and then run it.
EXEC sp_change_users_login 'Auto_Fix', 'userx'< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 19 December 2008 : 12:31:16
|
where userx is your sql username  < |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 19 December 2008 : 12:31:52
|
| just run the orphaned user fix I posted, that will sort it all out.< |
|
|
|
chp
Starting Member
16 Posts |
Posted - 19 December 2008 : 12:40:26
|
| Thank you! I tried HuwR's fix and it doesn't appear to have solved the problem. (Or maybe the database is so messed up at this point that I should just have them copy over a more recent version?) I'm off to check Gremlin's code. I shall report back as soon as I can. Thank you again, guys - it really helps to know I'm not alone here.< |
|
|
|
chp
Starting Member
16 Posts |
Posted - 19 December 2008 : 12:48:30
|
| Quick question: If I change ownership of the tables to dbo, do I need to update my connection string? I ask because I see no login information for dbo. (And I apologize in advance if I've just asked a really stupid question.)< |
|
|
|
Topic |
|
Topic Locked
