| Author |
 Topic  |
|
fastrax
Starting Member
35 Posts |
 Posted - 14 March 2007 : 21:11:16
|
moving sql 2000 to new server and sql 2005
So when I started I thought. "it might be fun to try new software..." A week later I think i was crazy.
So I want to move from my old server. It was sql 2000 and I want to move to my new server sql 2005. Now the biggest hurtle is that the two different servers are in two different parts of town and don't have any connection other than remote desktop.
In my searching for a solution. I tried, detaching the data base, copying the two files spacyun.mdf and spacyun.ndf. Then I copy them to the new server. After that I reattach the database so the forum continues to work on the old server.
On the new server I attach the database. It works it's magic and then the data base appears in the list.
But when I try to access the database on the new server, i get a http 500 internal error. When i try something more radical, like appending setup.asp I get the following error.
quote:
The database could not be opened !!
Check your config.asp file and set the
strConnString so it points to the database.
Also check if strDBType is set to the right databasetype.
Code : 80004005
Error Description :
Cannot open database "spacyun" requested by the login. The login failed.
Now I suspect it's a permision error. Or that i am going about this all wrong. Any ideas?
|
Edited by - fastrax on 15 March 2007 02:19:11 |
|
|
weeweeslap
Senior Member
USA
1077 Posts |
Posted - 15 March 2007 : 03:56:21
|
| did you create the username and pwd in sql server 2005 to match the one you created for sql2000? |
coaster crazy |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
daveo
New Member
97 Posts |
Posted - 15 March 2007 : 10:15:51
|
| If you are using a database user id and password, you have to add it to the the db server and make sure it works on the db. I was not able to get the forum to work with windows authentication. When I move from one SQL 2005 to another that is what I had to do. |
http://copdforum.portalone.us/ |
|
|
|
fastrax
Starting Member
35 Posts |
Posted - 15 March 2007 : 15:46:28
|
quote:
did you create the user name and pwd in sql server 2005 to match the one you created for sql2000?
I did create the username password in sql 2005 to match the one I had on sql2000. Unfortunately I am not sure if it works... (Sql2005 is fairly different than 2000 and I haven't really gotten used to it. this is my first project.)
interesting item... When I open up the user name that I created ,for the database. then I open up user mapping. I see that the user name is not attached to the domain I just attached. (even though when I open the domain and look at security/users the user name is there.) If I try to attach ,the created user on 2005sql, to the attached domain. I get the following error.
quote:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for User 'spacyun'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
User, group, or role 'spacyun' already exists in the current database. (Microsoft SQL Server, Error: 15023)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
But if I go into the database and click on database diagrams, i get the following message.
quote:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
|
Edited by - fastrax on 15 March 2007 15:47:07 |
|
|
|
fastrax
Starting Member
35 Posts |
Posted - 15 March 2007 : 15:58:05
|
I tried a couple of things...
I added the account that I created on the sql2005 that should match the old account from sql2000. Of course it says this account already exists. Then I tried to add a different username (added a one to the back of it) Same login name and added db_owner credentials to it. I was able to add the account. but can't access the data. Looks like I can create a new data base though buy using setup.asp
quote:
The database needs to be installed !!
Is there a way of taking ownership and getting access to the data? Or should I be looking at making a new database and then exporting and importing the data? If so, how would I go about that. Or more to the point, am I just crazy? |
|
|
|
daveo
New Member
97 Posts |
Posted - 15 March 2007 : 16:40:35
|
Run SQL Server Management Console.
Open the security tab.
You will see login tab. Open it. Add the user that owns the database.
Make sure that it is assigned to that for default db. Set password as you are using it.
In the database tab, make sure that it exists and is the db owner. |
http://copdforum.portalone.us/ |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
fastrax
Starting Member
35 Posts |
Posted - 15 March 2007 : 18:49:32
|
So I created a new user. (in security, logins.) and called it unspacy. then I went into user mapping, selected the database. Then added a check box for db_owner. Clicked OK
Then I confirmed by going into databasses, Selected the database, security, users. the user, unspacy, is now in the users section. I open it up and see that db_owner is selected. (What does the default schema have to do with this?) it's set to dbo. No settings in securables or extended properties.
Then I change the config.asp file.
strConnString = "Provider=SQLOLEDB;Data Source=shadowme1;database=unspacy;uid=unspacy;pwd=********;" '## MS SQL Server 6.x/7.x/2000 (OLEDB connection)
When I try to access the data base http://setup.thegeko.com/forum/ I get http 500 internal server error.
If I add setup.asp I get the database needs to be installed!!
So is the forum associated with the original uid it was created with? And if so, if I change the uid, can I access the old data at all?
I can't remove the old uid out of the database in sql server management and it has no login name. So I assume that it dosn't have nay permission to access the SQL server.
Also I can't seem to associate a new uid with the database. So what am I missing?
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
fastrax
Starting Member
35 Posts |
Posted - 16 March 2007 : 01:01:02
|
the old user id is there. spacyun.
spacyun is listed in the security/users. When i open it, it has no login name and no way of adding one. When I first tried attaching this database I did create the spacyun account. But can't add it to the database user because it already exists. Though from what I can see, it's not usable because it doesn't have permission to work on the new sql server. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 16 March 2007 : 04:18:56
|
Try changing all 3 forum prefixes, in config.asp from FORUM_ to spacyun.FORUM_. See if it helps.
You could also define the spacyun schema as the default schema for the user now being used to access the database. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
daveo
New Member
97 Posts |
Posted - 16 March 2007 : 11:06:31
|
| You need to add the user to the SQL Server users. Actually what I have done in the past is add a new user, then assign that user as the DB owner for the database. It went smoother. |
http://copdforum.portalone.us/ |
|
|
|
fastrax
Starting Member
35 Posts |
Posted - 16 March 2007 : 14:25:30
|
ruirib...
I tried changing the 3 forum prefixes. That didn't work. How do I change the schema. Do I go into database, security, users, user_id (unspacy) Double click. Then change default schema? When I make the change to the user_id (spacyun) it reverts back to dbo.
Also looked under database, security, schemas and added unsays to the list. but didn't know if it should have spacyun as the owner. Or if I should have changed spacyun to have unspacy as the owner.
Daveo, I have tried to add a new user. but unsure how to have the new user take ownership of the database.
What about changing the table names? Though there are a lot of them. Probably not the best idea.  |
|
|
|
Art
Starting Member
USA
32 Posts |
Posted - 16 March 2007 : 15:09:21
|
Hmm. I am having the same (I think) issue. I recently had to upgrade from a trial version of 2003 to 2003 standard. After that was done, I had to re-install sql 2005 express, as well as the management studio etc. I got help with setting up the forum from the old forum owner, but he died and I am really pulling my hair out with this. I had been learning apache and PHP before that, and I had never imagined that I would soon own a IIS server running MSSQL express.
Anywho, the forum I am (trying) to run is version 3.1, mod 4 I think. I plan on upgrading after I learn how to get this one working. I HATE the fact that this is making me feel stupid, but I can't just give up on it (gotta keep the legacy going you know?). If there is an SQL Guru here that would like to help me out, I can show my appreciation (in a limited working-guy fashion) by hosting a site on my server for you, or making a 50 dollar contribution to Snitz forums or your favorite open source project...or you. Not a large sum, but about the limit of my resonable budget. I would just plug away forever and a day trying to solve the issue if it was just my site's forum (StarFrontiers.org), but I promised to get Adoption Related Services Forum online soon, and I am failing in that promise (ARSPonline.org). Of course, I will also gladly write a tutorial of the entier process of setting up the system (complete with windows pings) and post it here once I can do it for myself.
Send me an e-mail to Masthead@Tampabay.rr.com if you can do a remote assistance for me, and I would appreciate any direction to a good thread here that would help a noob dealing specifically with 2003 and mssqlexpress.
EDIT:
If I get this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'forumuser'. The user is not associated with a trusted SQL Server connection.
-does that at LEAST mean that I have the server named correctly in config.asp?
|
http:\\StarFrontiers.org |
Edited by - Art on 16 March 2007 15:50:08 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 16 March 2007 : 15:27:31
|
fastrax, by right clicking the desired login, choosing properties, the clicking User Mapping, you will get a list of DBs to which you can assign the user, by checking the check box on the left. The right column specifies the default schema for the user in the database.
Restore the usual forum prefixes and try assigning the schema spacyun as the default one, for that user in that database. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
Topic Locked
