Deploying MSDE database with vb.net

khebert

New member
Joined
Nov 29, 2004
Messages
3
Programming Experience
3-5
I am about to pull my hair out (what's left of it) trying to deploy a vb.net package with an MSDE database. I have installed the MSDE toolkit, which works great for making sure that the .Net and MSDE is installed on the client machine. However, I can't for the life of me figure out how to attach the database!!
Help!!!!
 

Tarik

Active member
Joined
Apr 16, 2005
Messages
41
Location
Egypt-Alexandria
Programming Experience
3-5
you can write simple batch do the job for you and writ the following code inside your batch
VB.NET:
osql -S ServerName -U UserName -P Password
exec sp_attach_db @DB =N'DB_Name' ,
@filename1 =N 'C:\MyData\DBName.mdf',
@filename2=N 'C:\MyData\DBName_Log.ldf'
where you shall replace the following :
ServerName with your server name or the Server IP
UserName with the actual user name you se to connect to the data base
Password with the actual password used
DBName with your database name

Dear juggaloBrotha the link you have posted does not wok
 
Last edited:

ManicCW

Well-known member
Joined
Jul 21, 2005
Messages
428
Location
Mostar
Programming Experience
Beginner
Simple solution

There is much more simple solution. You need to generate script from msde database (right click on database in server explorer). Then you attach sql file as embeded resource. Now all you need is some code to execute it during install process (or when program starts). Now, on that part I'm stuck to. HELP!!!
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Hehehehehe..... I have such an animal.... but it comes at a price.... no, not a $$$.... the price of your soul. Muhwhahahahaha!

Seriously, I did just finish something last night to my app that allows me to connect to a SQL Server database and run scripts against it. But like I said there was a price to be paid for it, and that was having to compromise and use the unmanaged SQLDMO COM objects. Ick! Unfortunately running it this way caused some problems. For starters you can't pass anything with a GO statement in it. This meant I had to parse apart each script, replace all occurances of "GO" with another delimiter that won't break anything (I used "~") Then split on that new delimiter, putting the script into an array of string, then looping through the array, executing each element in the array (each part of the script).

If you want, when I get home, I can clean it up a bit, and post it with some directions on getting it to work and what would need to be included (like the reference to the SQLDMO.)

Tg
 

ManicCW

Well-known member
Joined
Jul 21, 2005
Messages
428
Location
Mostar
Programming Experience
Beginner
Nice

Sure that would be interesting to see. Your skills are much higher than mine. I never used com objects!!!

I found commercial solution in Red Gate's SQL Packager (very expencive). You can create exe file that creates database on msde or sql server.

I was wondering if you know how to execute exe file during installation process (install project vs.net) before program istallation???
 

uniquegodwin

Member
Joined
Aug 15, 2005
Messages
14
Location
Chennai
Programming Experience
1-3
Tarik said:
you can write simple batch do the job for you and writ the following code inside your batch
VB.NET:
osql -S ServerName -U UserName -P Password
exec sp_attach_db @DB =N'DB_Name' ,
@filename1 =N 'C:\MyData\DBName.mdf',
@filename2=N 'C:\MyData\DBName_Log.ldf'
where you shall replace the following :
ServerName with your server name or the Server IP
UserName with the actual user name you se to connect to the data base
Password with the actual password used
DBName with your database name

Dear juggaloBrotha the link you have posted does not wok
Hey,can you please tell me what is the difference between an mdf and an idf file? Are two files always needed?
I like this idea of creating batch file.its really neat. :)
Thanks
 

Tarik

Active member
Joined
Apr 16, 2005
Messages
41
Location
Egypt-Alexandria
Programming Experience
3-5
My Answer Is Quoted From MCAD/MCSD Book Ok
SQL Server 2000 databases have three types of files:
  • Primary data files. The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended filename extension for primary data files is .mdf.
  • Secondary data files. Secondary data files comprise all of the data files other than the primary data file. Some databases might not have any secondary data files, while others might have multiple secondary data files. The recommended filename extension for secondary data files is .ndf.
  • Log files. Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended filename extension for log files is .ldf
so you need both MDF & LDF Files and in some cases you may need NDF File
 

uniquegodwin

Member
Joined
Aug 15, 2005
Messages
14
Location
Chennai
Programming Experience
1-3
oh,I got that :)
Can you tell me the way i should make the batch file for restoring the database too if u can,please?? Thanks :)
I searched on google,but im not able to find this kind of thing which you gave me :) so,please help me...
Thank you so so so so muchhh :)
 

uniquegodwin

Member
Joined
Aug 15, 2005
Messages
14
Location
Chennai
Programming Experience
1-3
This line..
VB.NET:
exec sp_attach_db @DB =N'DB_Name
I thought it would backup those to those 2 files?? or restore them? but,it doesnt do both...what does this do? Im sorry for asking so muchhh..please man,im desperate to know how to deploy this MSDE database with my application...I really badly need help...pleaseee..Thank youuu so muchhhh.
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Actualy you don't need the LDF files, and in fact, it's better that you don't distribute them as it just leads to bloat on the distribution.

To attach a DB w/o the LDF file, use this:

VB.NET:
sp_attach_single_file_db 'DataBaseName', 'C:\Full_Path_to\Location_of\MDB_File'
It'll generate a warning that the LDF file couldn't be found, but that's OK, as it will create one for you. We use this all the time, and it works great.

I should note, that this is run through Query Analyzer, in the master database. (any connection to the master database will do, it doesn't *have* to be in QA.)

-tg
 
Top Bottom