Sending commands to the SQL engine with the |DataDirectory| string in the database name

[Paul Hemans]

Hi,
Here's my code:
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();

}

The problem seems to be that mdfName is "|DataDirectory|\myMdf.mdf". Is it
correct to presume that SQL has converted the |DataDirectory| into a full
path and so my request is no longer valid? Any suggestions for getting
around that?

[Andrea Montanari]

hi,

Post by Paul Hemans
Hi,
SqlCommand cmd = new SqlCommand("sp_dboption", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
return "SQL exception going offline\n" + se.ToString();
}
The problem seems to be that mdfName is "|DataDirectory|\myMdf.mdf".
Is it correct to presume that SQL has converted the |DataDirectory|
into a full path and so my request is no longer valid? Any
suggestions for getting around that?

yes.. try using
cmd.Parameters.AddWithValue("@dbname", sqlConn.DataBase);

[Paul Hemans]

Thanks heaps Andrea for the help,

Post by Andrea Montanari
hi,

Post by Paul Hemans
Hi,
SqlCommand cmd = new SqlCommand("sp_dboption", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
return "SQL exception going offline\n" + se.ToString();
}
The problem seems to be that mdfName is "|DataDirectory|\myMdf.mdf".
Is it correct to presume that SQL has converted the |DataDirectory|
into a full path and so my request is no longer valid? Any
suggestions for getting around that?

yes.. try using
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply