Solved

Microsoft SQL Server Error: 233

Posted on 2009-05-20
11
2,464 Views
Last Modified: 2012-05-07
Hi all,
I'm in the proces of migrating a MSSQL2000 Standard Edition database in a shared hosting to environment, over to a dedicated server, MSSQL2008 Web Edition database.

I've done the following:

1. Fully exported the database from MSSQL2000 - my hosting company provided me with this.
2. Imported it into MSSQL2008
3. The database seems to import fine, with all table and the users and so on/

4. Checked for" orphaned" users on MSSQL2008 - none seem to be returned with the following script - whilst connect to the required database as sa.
EXEC sp_change_users_login 'Report';

5. Manually create the user login onto the MSSQL2008 database - and ran:
EXEC sp_change_users_login 'Report';
Still no errors.

However, then I try to connect to the database, I get the following error?!?
Any ideas as to the problem - I'm getting desperate!

Regards

Jamie


MSSQLError.jpg
0
Comment
Question by:Jamie
  • 6
  • 5
11 Comments
 

Author Comment

by:Jamie
ID: 24429403
Hi All,
I've been using the following to help me through the process, but still getting the attached error:
http://www.dell.com/downloads/global/solutions/public/white_papers/SQL_2000_SQL_2008_Migration_Guide.pdf
thanks
JamWales
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24429423
On the server; In the SQL Network Configuration; Turn off Named Pipes and Turn On TCP/IP; then restart SQL Service and give it another go.

HTH
0
 

Author Comment

by:Jamie
ID: 24429541
Hi St3veMax,
Sorry, I forgot to say - I'm trying to logon, whilst on the actual MSSQL server itself. That said, to be certain I have checked, and named pipes are already disabled, and tcpip enabled.
Thanks
JamWales
MSSQL-Network.jpg
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24429559
OK, WHat you see is identical to our clustered environment.

What happens it you try and connect to SQL from the locally installed management studio ?

Cheers
0
 

Author Comment

by:Jamie
ID: 24429593
Hi St3veMax,

The errors I described above are from the locally installed management studio - until this works I have not looked at trying to connect from a remote location. I'm using remote desktop to access the server directly.
Regards
JamWales
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 13

Expert Comment

by:St3veMax
ID: 24429609
OK, With you. Disable Shared Memory, Restart SQL Server and try again.
0
 

Author Comment

by:Jamie
ID: 24429745
Hi St3veMax,
Tried that and the orginal error has gone - only to be replaced with another one?!

I set the default database to the one that was restored from MSSQL2000  in the login connection properties on Management Studio - plus this has also been set in the login properties on the user (security settings) in the database itself.

 I very much appreciatre the time and help you are giving me - I'm out of my depth with this one!

Regards
JamWales
MSSQL-Error4064.jpg
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 500 total points
ID: 24429766
No Worries Jam.

Set your default database to 'Master' and try again.

You will then need to link your login back to the record in teh old db as it will have been orphaned.

EXEC sp_update_users_login 'report'

then

EXEC sp_change_users_login 'update_one', 'yourusername', 'yourusername'

HTH

0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24429771
also; You should then be able to change your default database back once you've tested the above.
0
 

Author Closing Comment

by:Jamie
ID: 31583396
YAHOOOOOOOOO - It's Works!!!!!!!!!!!!!!!!!!!!!!! Many thanks for your help, saves me drinking 20 cans of Red Bull a day!
JamWales
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24429914
Glad to help...BTW Relentless is better ;o)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now