SQL Server Management Studio: how to connect to SQL Express databases
Summary
SQL Server Management Studio is the new GUI from Microsoft for managing your databases. There are a few tricky steps to take when working for the first time with SQL Express databases within Visual Studio or Visual Web Developer Express. This article provides a step-by-step walkthrough.
Where is my database?
SQL Server Management Studio allows you to connect to the running version of SQL Express on your local machine. f you're working with SQL Server Express in conjunction with Visual Web Developer Express or Visual Studio, then you may be surprised not to see a database that you know you use in a project.
The fact is that SQL Server Express databases within your projects run differently to the full-blown SQL Server model. They run as User Instances - which means that they get their own personal database engine when they start up. You can't see your project database because it's not attached to the engine that is running as a service on your local machine.
If you're working extensively with the project database, such as creating tables and stored procedures, then you'll want to connect with SSMS for a more user-friendly experience. So how do you do it? Unfortunately this is rather convoluted.
Here is a step-by-step walkthrough.
1. Ensure the SQL Express database is running
The SQL Express instance is only active when it's being actively used. Open your local website through Visual Studio or Visual Web Developer to start a connection.
2. Find the connection details
Click the New Query button - it's at the top left of the tool bar. A blank Query Window opens in the right pane.
Enter the SQL query in the box, and click the Execute button (it's on the toolbar as a red exclamation).
SELECT owning_principal_name, instance_pipe_name
FROM master.sys.dm_os_child_instances;
This SELECT statement is pulling back data from a system table. Hopefully you'll only see one row returned. If there is more than one row, look for your own Windows user name in the first column.
Figure 1. Our Results
Copy the text in the second column (the Pipe Name) - you'll be pasting it into another window.
3. Connect to the User Instance
From the File menu, choose Connect Object Explorer (or click the Connect button on the toolbar). Paste the Pipe Name string into the Server name drop down box and click connect.
Figure 2. Server Connection
Get a Connection Error?
The connection can be a bit flaky. If SSMS won't connect, close down your development enviroment and reopen it.
4. Identify your Database
You may see a number of different databases listed when you expand the database pane. It depends on how hard you've been at work! If you have developed several website projects, then each database will be listed. In particular, you may have a number with the same name, as in figure 4. The pathname should help you know which is which!
Figure 4. Our Many SQL Express Databases
Now you've found your database - work away!
Summary
This article provided a step-by-step walkthrough with screenshots to show you how to connect to SQL Express user instances from SQL Server Management Studio.
Submitted: 10 August 2007
Author: Margaret Cruise O'Brien
(c) M.C.O.B. Technology 2007