?
Solved

SQL Server Management Studio Express

Posted on 2010-01-10
15
Medium Priority
?
922 Views
Last Modified: 2012-05-08
We have SQL 2000 & SQL 2005 installed on our server (SBS 2003) running different databases, I can connect to the SQL 2000 databases using SQL Server Management studio express but I cannot connect to SQL 2005, basically I need to remove a database from the SQL 2005 instance but I am unable to connect to it, is there a way to do this?

Thanks for your help
0
Comment
Question by:seanfoord
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26280201
Have you enabled remote access on the SQL Server 2005 box?
0
 

Author Comment

by:seanfoord
ID: 26280349
Thanks for the quick response, the SBS2003 has remote access enabled, I use the to remotely log in.
 
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26280459
Seems like you have installed SQL Server 2005 express in your machine as a Named instance..
Named instances can be accessed by typing either

servername\instancename
or
serverip\instancename

and you can find the instance in either services.msc or SQL Server Configuration manager..
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:seanfoord
ID: 26280601
rrjeqan17,
Thankyou for the solution, that worked!
This has unfortunately led into another problem, I receive an error message saying "cannot connect to server\arcserve_db. All I want to do is remove this database from our system as the related software is uninstalled & it was unable to remove the DB instance during the uninstall program, do you know of any way to remove this db from SQL server 2005 express
Thanks
 
 at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26280695
>> All I want to do is remove this database from our system as the related software is uninstalled & it was unable to remove the DB instance during the uninstall program, do you know of any way to remove this db from SQL server 2005 express

Just go to Control Panel -> Add/ Remove Programs and you would be able to uninstall Express version of SQL Server 2005..

If you don't find any entries in Control Panel, then you can uninstall Express Edition using the Windows Installer Cleanup Utility Tool from here:

http://support.microsoft.com/kb/290301

If both didn't work, the the manual approach to remove the instance below:

http://support.microsoft.com/kb/909967
0
 

Author Comment

by:seanfoord
ID: 26280869
Thanks again for your quick response & help, the SQL 2005 DB has another DB being used in it for another program, I really would like to just delete the one DB instance inside SQL 2005 if that makes sense & not remove the program
 
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26280883
>> I really would like to just delete the one DB instance inside SQL 2005

Kindly confirm whether you meant

* One database within a instance
* One SQL Server 2005 instance among two SQL Server 2005 instances..

I am asking this because it contradicts with the original question which states that you have two instances one is 2000 and the other is 2005 and need to remove 2005 instance alone..

If you meant the first one, ie., deleting the database in a database then you can do it via a Drop database statement.

If you meant the second one, then go to add/ remove programs and then click on Uninstall SQL Server 2005 and it would ask you whether you need to uninstall a instance or the complete installation.. Just provide the instance name correctly to remove that installation..

Hope this clarifies..
0
 

Author Comment

by:seanfoord
ID: 26281018
I know this sounds confusing, we do have 2 sql instances on our server (sbs2003)
1. Sql 2000 standard which has 5 databases inside it (remains as is)
2. Sql 2005 express which has 2 databases inside it ( I would like to remove one of the databases from this version of SQL but leave the program installed on our system with the other database operational)
Thanks heaps for your patience, I have very little understanding of SQL
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26281041
That clarifies..
Just remove the unnecessary databases by either using DROP DATABASE statements like

USE master
GO
DROP DATABASE ur_database_name
GO

where ur_database_name is the database you tried to remove

Or a simple way by using SQL Server Management Studio Express..
Just right click on that database and choose Delete..

It would do.
0
 

Author Comment

by:seanfoord
ID: 26281822
I tried using the SQL Management Studio express to delete the database and I received the error  in message ID: 26280601 above, in the example you mentioned in your last post  "USE master etc..." do I create a batch file (.bat)  & execute it?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26282174
Ok.. Just try this one out..

1. Go to services.msc and check whether this Express instance is up and running. If not then start it out..
2. Login using sa or any other administrator credentials to this instance.
3. Try dropping the database again.

Make sure:

1. SQL Browser service is up and running.
2. The login you use has necessary privileges to drop that database.
0
 

Author Comment

by:seanfoord
ID: 26289177
The Express instance of the database "arcserve_db" is not running and will not start. There are a number of errors in the log file which I have shown below,.

erver TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.
For more information, see Help and Support Center at  

TDSSNIClient initialization failed with error 0x2740, status code 0xa.
For more information, see Help and Support Center at  

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
 
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26289799
>> erver TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.

Got it..
* Your Default instance 2000 is listening on that port and hence assign port other than 1433 and 1434.
* Make sure your Browser Service is up and running
* Make sure that the port you are going to assign is excluded in Windows Firewall.
* Make sure Named pipes are enabled..

Don't forget to restart your SQL Server services after configuring these set of things..
And do let me know whether you are able to connect now or post the error message encountered to help you out..
0
 

Author Comment

by:seanfoord
ID: 26297699
That worked a charm, there are no error logs & I can now connect to any database instance.
Thanks rrjegan17  for all you help and time
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26299923
Welcome..
And glad to help you out..
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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