Thursday, February 9, 2012

AttachDatabase and Click Once

I am using Click Once to deploy SQL Express as well as the dbf files. The dbf files will be deployed to the data directory.
When my application start up I am using the Server.AttachDatabase method to attach 4 database files to the SQL Express engine. Since the dbf files gets deployed to the below folder that is read only, I am geting the error message below. How do I get around this problem? Please help.

C:\Documents and Settings\user\Local Settings\Apps\Data\5THMDK6K.YTT\KL652YA1.D7J\powe..tion_176981e083d435cc_0001.0001_d3662e0c064603fa

Error Message

C:\Documents and Settings\user\Local Settings\Apps\Data\5THMDK6K.YTT\KL652YA1.D7J\powe..tion_176981e083d435cc_0001.0001_d3662e0c064603fa\Data\\PowerAnalysisLocal.mdf" failed with the operating system error 5(Access is denied.)

Attach Code

public class DBAttach

{

private LogThis logger;

private Microsoft.SqlServer.Management.Common.ServerConnection svrConn = null;

private Server srv;

private ArrayList dbArr = new ArrayList();

public DBAttach()

{

this.logger = new LogThis("ServiceLibarary.DBAttach");

svrConn = new Microsoft.SqlServer.Management.Common.ServerConnection(@.".\SQLEXPRESS");

svrConn.Connect();

srv = new Server(svrConn);

dbArr.Add("PowerAnalysisLocal");

dbArr.Add("TimeBasedLocal");

dbArr.Add("ProgramBasedLocal");

dbArr.Add("ControlBasedLocal");

}

public void Dispose()

{

svrConn.Disconnect();

}

public void AttachFiles()

{

startLocalServer();

CommonUtils dbUtil = new CommonUtils();

string dbPath = dbUtil.getFileDirectory();

foreach (string str in dbArr)

{

if (!IsDBAttached(str))

AttachDB(str, dbPath);

}

AddSysUser();

}

public void AddSysUser()

{

StringBuilder sSQL = new StringBuilder();

// connect using window authentication

using (SqlConnection sqlConn = new SqlConnection())

try

{

sqlConn.ConnectionString = @."server=.\SQLEXPRESS;Integrated Security=SSPI;database=dbnamel;pooling=false";

sqlConn.Open();

SqlCommand comm1 = new SqlCommand("[sp_addAnalyzerUser]", sqlConn);

comm1.ExecuteScalar();

}

catch (Exception e)

{

logger.Error(e.ToString());

}

}

public void AttachDB(string dbName, string dbPath)

{

try

{

StringCollection strdbs = new StringCollection();

strdbs.Add(dbPath + @."\" + dbName + ".mdf");

srv.AttachDatabase(dbName, strdbs, AttachOptions.RebuildLog);

}

catch (Exception e)

{

logger.Error(e.ToString());

}

}

public void DetachDBS()

{

try

{

foreach (string str in dbArr)

{

if (IsDBAttached(str))

srv.DetachDatabase(str, true);

}

}

catch (Exception e)

{

logger.Error(e.ToString());

}

}

public void startLocalServer()

{

System.ServiceProcess.ServiceController serviceCtrl = new System.ServiceProcess.ServiceController();

serviceCtrl.MachineName = System.Environment.MachineName;

serviceCtrl.ServiceName = "MSSQL$SQLEXPRESS";

//Start the Sql Server windows service

try

{

//only start it if its not currently running

if (serviceCtrl.Status.ToString() != "Running")

serviceCtrl.Start();

}

catch (Win32Exception myEx)

{

logger.Error(myEx.ToString());

}

catch (Exception ex)

{

logger.Error(ex.ToString());

}

}

public bool IsDBAttached(string dbName)

{

if (srv.Databases.Contains(dbName))

return true;

else

return false;

}

}

Error 5 usually means that the service account that SQL Server is running under doesn't have read/write permissions in the folder the database is in.

You can fix this by changing the service account (via Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager | SQL Server (SQLEXPRESS) | Properties) or by granting access to the service account in file explorer.|||This needs to be done programmatically, since it is a ClickOnce installation on a client PC. The folder seems to be read only.
Thanks

No comments:

Post a Comment