Here's what I'm going for:
I have an 05 VB.NET windows application that will be used as a smart client for our folks in the field. The windows application includes 05 SQL Server Express. I have included in the Data Sources of my project and attached file going through the wizard Microsoft SQL Server Database File (SqlClient) ='s (myfile.mdf) and then selected all tables, views, stored procedures, and functions... the corresponding myfileDataSet.xsd with the myfile.mdf are now located in the root of the project. I now recompile the project without error and go to the properties section Publish tab... select the Application Files button and myfile.mdf Publish Status is set to Include and the Download Group set to Requried. With this in place I right click on the myfile.mdf from the Soultion Explorer and under the properties section have set the build action to compile and use the copy always setting for the Copy to Output Directory.
My app.config looks like this:
<configSections>
<sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="myfile.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
</sectionGroup>
</configSections>
<connectionStrings>
<add name="myfile.My.MySettings.ffgscrmConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
After the publish is completed on the client machine... install for WIndows Installer 3.1, SQL Server Express, and the Windows Application contains no data but everything else works fine.
My problem is that I need to attach the myfile.mdf to the new SQL Server Express instance on the client machine during the installation process so that when the application fires it will be pointed to the above location on the client.
Anyone have any ideas... scripts... includes for an ApplicationEvents.vb, source code on how to do this correctly
Kind regards,
BillB
NEWBIE.. SQL EXPRESS Attach during VB.NET Windows Application Install - Oneclick
singam
Hey Mike,
Totally know what you're saying... this is an initial load that contains data that i need to move to the client. Once the client is connected the whole process is automated from the main sql server and the client is simply a passthrough (works disconnected / and connected) based on multiple record update flags. You are right in that the connection string is a temp thing but it will still be used to write client data to the local box before updating the server. Can you send an example of your suggestions 1 and 2 above Have a great weekend.
Thanks,
Bill
DanMeyers
The database is automatically attached to a User Instance of SQL Express when your application runs, you don't need to do anything special to make this happen. This process is dictated by the fact that you've included the database file within your project and the connection string contains the AttachDbFilename and User Instance keywords.
Details about User Instances are here and you can learn more about ClickOnce and database files in the VS documentation. I did a webcast about some advanced topics in database embedding, you can find source code and a link to the webcast on the blog.
Mike
USJOHN
Hey Mike... thanks for taking the time. I've tried attaching the db in the vs app with no success... the install goes well but the db does not attach. I've built a db attach msi using an install.bat file and the SQL Server Express Utility... the bat file looks like this:
cd C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
SSEUtil -m -a myfile.mdf myfile
which totally works great! Ive included (pathed) the SSEUtil.exe, the mdf and ldf, and the bat file to the above directory.
Since the above is a bat file i could not attach it to the launch conditions in the dbinstall application. I also still need to include the dbinstall.msi in the main project for oneclick to install after all prerequisties and the application is installled. Plan on doing this after I get the install bat firing in the dbinstall.msi compile.
Many thanks,
Bill
Sniper167
Hi Bill,
Fair warning, SSEUtil is a totally unsupported utility that was never designed to be used the way you're using it. It certainly wasn't designed to be deployed as a general solution for attaching databases to the main instance, there are supported ways to do that using pure T-SQL. I'm also pretty sure SSEUtil assumes an instance name of SQLEXPRESS, so if you ever need to deal with a non-default instance name, your solution will fail.
I'm a bit concerned about the configuration you're describing. The command line you're running with SSEUtil will attach your database file to main instance SQLEXPRESS, but the connection string you referenced earlier in the thread does not connect to the main instance, it connects to a User Instance, which is totally separate from the main instance. Basically, the connection string and the SSEUtil command you've provided are fundamentally incompatible as they are referencing two different SQL Servers.
I'm kind of working in a vacuum here, so I'm assuming that your goal is actually to work against the main instance and that you're not actually using the connection string you provided earlier. If that is the case, I'd actually suggest:
Even though your current solution "works," I think using supported mechanisms will pay-off in the long run. It will certainly be more flexible and less prone to failures. Let me know how things turn out.
Mike
DeveloperJTM
Hi Bill,
I'll work on some examples.
I'm interested in how you came to the solution of using SSEUtil, could you describe the process that got you there Where did you look How did you search What specific phrases did you use to search What sites, documents, etc. suggested this solution to you
I work closely with our help documentation team and it will help me give them feedback to know more about how you (and others) look for information. Clearly we're falling short that you weren't able to find the BOL topic about CREATE DATABASE when looking for information about attaching your database. Also, if you have ideas about what would help you even more, we're all ears. (OK, we're not all ears, that would look pretty funny and how would be breath You know what I mean.)
Mike