SQL Express and user sessions
So I have my little utility, a port forwarder (with smarts). It reads a database to determine how to forward requests. But I was running into trouble when I implemented a service controller that needed access to the database. I really didn't want to stuff around with installation scripts etc. I just wanted to connect to an mdf file.
More details:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic7
It appears that there is scope within SQL server for small utilities that need to access data. There is a type of deployment called xcopy where you simply copy the mdf file to the target and request that SQL starts with a special user instance. The user instance temporarily attaches the mdf file, that is why you may not find your database in the SQL manager.
My problem (amongst many others) is that the database is used exclusively by the user instance of SQL Server. I was creating a windows service, which would run as user "network service" (I think) but my controller interface runs as Interactive User. So it could not connect to the mdf. Once a user instance has been created it holds the mdf file open in case of more incoming requests, unless specifically told to shutdown.
Not that I am driving forwards while looking backwards but I have to say all this would have been handled nicely in Foxpro with:
close database
But NO now I need something like:
private string Detach(string mdfName, SqlConnection sqlConn)
{
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
SqlCommand cmd = new SqlCommand("sp_dboption", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dbname", mdfName);
cmd.Parameters.AddWithValue("@optname", "offline");
cmd.Parameters.AddWithValue("@optvalue", "true");
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
return "SQL exception going offline\n" + se.ToString();
}
catch (InvalidOperationException ioe)
{
return "SQL InvalidOperationException going offline\n" + ioe.ToString();
}
cmd = new SqlCommand("sp_detach_db", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dbname", mdfName);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
return "SQL exception when detaching\n" + se.ToString();
}
catch (InvalidOperationException ioe)
{
return "SQL InvalidOperationException when detaching\n" + ioe.ToString();
}
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
return "";
}
I am sure that some of that is not neccessary, but it worked.
More details:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic7
It appears that there is scope within SQL server for small utilities that need to access data. There is a type of deployment called xcopy where you simply copy the mdf file to the target and request that SQL starts with a special user instance. The user instance temporarily attaches the mdf file, that is why you may not find your database in the SQL manager.
My problem (amongst many others) is that the database is used exclusively by the user instance of SQL Server. I was creating a windows service, which would run as user "network service" (I think) but my controller interface runs as Interactive User. So it could not connect to the mdf. Once a user instance has been created it holds the mdf file open in case of more incoming requests, unless specifically told to shutdown.
Not that I am driving forwards while looking backwards but I have to say all this would have been handled nicely in Foxpro with:
close database
But NO now I need something like:
private string Detach(string mdfName, SqlConnection sqlConn)
{
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
SqlCommand cmd = new SqlCommand("sp_dboption", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dbname", mdfName);
cmd.Parameters.AddWithValue("@optname", "offline");
cmd.Parameters.AddWithValue("@optvalue", "true");
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
return "SQL exception going offline\n" + se.ToString();
}
catch (InvalidOperationException ioe)
{
return "SQL InvalidOperationException going offline\n" + ioe.ToString();
}
cmd = new SqlCommand("sp_detach_db", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dbname", mdfName);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
return "SQL exception when detaching\n" + se.ToString();
}
catch (InvalidOperationException ioe)
{
return "SQL InvalidOperationException when detaching\n" + ioe.ToString();
}
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
return "";
}
I am sure that some of that is not neccessary, but it worked.
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home