>
>Still trying to decide on distributing the MDFs or just running a script. I'll need to run scripts for upgrades, so I have to have that capability in any case.
>>
>>I also haven't decided on using SMO, Process.Start(sqlcmd), or breaking the SQL on the "GO" words and using the normal ExecuteNonQuery.>
>I've always used scripts. I used to use an old utility I wrote in 2002 (using VFP) for processing versioned scripts to create a database. The database is updated with the same utility when new scripts are available. It uses SQL DMO (because it's so old).
>
>This year, I finally got around to writing a .NET version of my VFP utility. I used SMO this time around. The list of script versions are stored as XML (whereas the actual scripts are simply .sql files). Not very secure, since the whole database schema can be seen by looking at those files (whereas, all the scripts in my VFP version were stored in DBFs). I'm not sure if that matters or not ... but this was a quick-and-dirty utility I wrote and might make better when/if I have time to get around to it.
>
>Also, this was regular SQL Server, not Express ... although I doubt if that matters either.
>
>Just my 2 cents.
>
>~~Bonnie
Your 2 cents is usually worth a lot more than that <g>
I went with SMO, and got a successful install on the application this morning.
One of my biggest problems in all of this was trying to research it out. I ran across all sorts of stuff that just didn't turn out to be true. One site said SMO script strings were limited to 20,000 bytes. My 35k script is running just fine. Frustrating to try and figure out what works before you code it, just to find out that most of what you learned was wrong or didn't apply to the current version.
Thankfully I'm not worried about security on the .sql files or I would have to encrypt them. But I think I like the script option the best as well.
Hardest part of setting up the SMO was locating the dlls. They aren't listed and you have to browse for them at C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
