ClickOne Deployment and SQL Server Express

This is probably a dumb question. But if I embed a SQL Server Express database into my project and then attempt to deploy it via ClickOnce, will I be able to access that database using the SQL Server Management Studio Express CTP if it is installed on the same machine as the client that my app would be installed?

It depends...

ClickOnce deploys the database basically using XCopy and then VS employs a new feature of SQL Express called User Instances to allow the application to start up a special instance of SQL Server and attach the database to that instance at runtime. (Read more about User Instances)

You can use Management Studio to connect to this deployed database while the user instance is running and the database is attached, but not once it shuts down. User Instances eventually shut down when there are no connections open to them. You have to know the Pipe name to connect to a User Instance and you can learn more about how to get the Pipe name and use it to connect to the User Instance in this BOL topic.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hi,

I encounter a problem when trying to deploy my project (VB2005 Express & SQL2005 Express) by using ClickOnce.

The connectionString is "

connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=;AttachDbFilename=|DataDirectory|\MyDB.mdf;Integrated Security=True;User Instance=True"

After the program is intalled on a client computer, when running, it throws an error: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance." I checked the SQL server is runnning on the computer. The user name used to log on has administrator privilege. The computer has multiple user accounts.

If I change User Instance=False in the ConnectionString, it throws an exception when debuging run the program: "An attempt to attach an auto-named database for file C:\.....\bin\Debug\MyDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I cann't figure out why. Any help would be appreciated.

Thanks.