Connect to Master Database in SQL Server Express from Class Librar

[Guest]

I am developing a Windows Form application that uses a SQL Server Express
2005 database, which it connects to using the AttachDBFilename property.
That works well when you know where your database file is.

I would like to run some queries against the master database in my SQL
Server Express 2005 instance, but there is no real way to tell where this
database resides on the system. I will be checking to see if any of my app
databases are connected, how big they are, etc. Although I can connect to my
specific database instances using AttachDBFilename, I can't seem to connect
to the master database on my local system with generic 'instance' connection
strings. I would think the query string would be similar to...

"Data Source=.\SQLEXPRESS;Integrated Security=True;User
Instance=False;initial catalog=master"

This connection string doesn't work using the following VB.NET code. It
always returns 'closed' as the status, because it never really connects.

_sSQLConnectionString = "Data Source=.\SQLEXPRESS;Integrated
Security=True;User Instance=False;initial catalog=master"
Dim oCon As New SqlClient.SqlConnection(_sSQLConnectionString)
Dim strSQLSearch As String
strSQLSearch = "Select * From master.dbo.sysdatabases"
Dim oDataAdapter As New SqlClient.SqlDataAdapter(strSQLSearch, oCon)
Dim oDataSet As New DataSet
oDataAdapter.Fill(oDataSet, strTableName)
oDataAdapter.Dispose()
oDataAdapter = Nothing
oCon.Close()
oCon.Dispose()
oCon = Nothing

I can't seem to figure out what connection string I need to connect to the
master database on my local SQLEXPRESS instance. I have verified that the
master DB does in fact exist, because it shows up in the SQL Express
Management Studio and SSEUtil. Any help would be greatly appreciated. Thanks.

 

[Bryan Phillips]

Try using this connection string

Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;

BTW, as long as you have a SQL connection open, you can access objects
in any other mounted database by using the object's fully qualified
name: databasename.owner.tablename. As an example, you can use this
query to get a list of logins: SELECT * FROM master.dbo.syslogins



Bryan Phillips
MCSD, MCDBA, MCSE
Blog: http://bphillips76.spaces.live.com

 

[Linda Liu [MSFT]]

Hi Jon,

The first time a user instance is generated for each user, the master and
msdb system databases are copied from the Template Data folder (e.g.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data) to a
path under the user's local application data repository directory for
exclusive use by the user instance. This path is typically 'C:\Documents
and Settings\<UserName>\Local Settings\Application Data\Microsoft\Microsoft
SQL Server Data\SQLEXPRESS.

You could connect to the master database in your client application without
specifying the AttachDBFileName property in the connection string. The
following is a sample connection string.

string connstring = "Data Source=.\SQLExpress; Integrated Security=true;
User Instance=true; Initial Catalog=master;";

In fact, you could access master database even when your program connects
to another database. The following lines of code work fine.

DataSet dataset = new DataSet();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\\SQLExpress; Integrated Security =
true;" +
"User Instance=true; " +
"AttachDBFileName=" + @"c:\attachdb\MyDBFile.mdf; ";

SqlDataAdapter adapter = new SqlDataAdapter("select name from
sys.DataBases",conn);

conn.Open();
adapter.Fill(dataset, "DataBases");
conn.Close();

When you set the User Instance property false in the connection string,
your program will connect to the parent instance of the SQLExpress.

Hope this helps.
If you have anything unclear, please feel free to let me know.



Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.