visual studio .net general: Hullo people,

Looking for a little inspiration here. I have a project that I'm
creating the installer for - using an installer project in VS. One of
the things it need to do is create the db that the project uses.

Using a custom action I've got it creating the DB itself, the user
groups in Active Directory that it needs, along with mapping the groups
within the database. Where I'm stuck for ideas is the best way of
creating all the tables, views, stored procedures, functions and all the
object level permissions that go with them.

Using the SQL Server Management Studio to create a script for the DB
produces a script file that doesn't create the objects in the right
order - it tries to create views before the tables they reference they
exist, for example. They only way I can see of making that work
properly is to script each of the object types separately.

The other idea I had was to include the db file itself and do a create
for attach instead...not sure how practical that is though.

Anyone got an ideas as to the best way to proceed?

--
Chris Crowther
Developer

Hello Chris,

Based on your description, you're building a setup program for your
application and you would like to do some intialization(create table, view,
sps ...) in the new created database. You're wondering what's the best
means to do it, correct?

I think your current choice on using custom action to manipulate the
database is reasonable. As far as I know, for such intialization of
database(SQL Server 2005) in custom action, we can use one of the following
means:

1. Use SQL 2005 SMO to manipulate an installed database of a certain server
instance. SMO is naturally .net based which would be easy to use in .net
code.

2. Use ADO.NET code to connect the database and executring t-sql statements

3. Programmaticaly attach a prepared database file into the given server
instance.

I think if the database structure and the components contained in it are
not very complex, using the #1 and #2 would be preferred. And I've seen
many MSDN sample application use the #2 to setup sample database. #3 is
very convenient to do(e.g through SMO call), however, it will require your
setup to embed the database file, if the extra database file size won't be
critical for your setup program, using #3 will save lots of code or
scripts. How do you think?


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Pretty much.

[quoted text, click to view]

I've not considered SMO, will have a look and see if it's practical at all.

[quoted text, click to view]

I guess that depends on your perception of complex; there's 11 tables,16
views, 19 stored procedures and 2 functions - so not complex in the
great scheme of things, just moderatly tedious to have to create a
script file that creates them in the correct order, so dependencies are
created before the objects that need them.

Mostly I'm trying to find the way which requires the least amount of
effort on my part.

[quoted text, click to view]

Size is pretty much irrelevant - the installer will be on a CD and the
database file is only 3MB. It's custom software for a customer, the
database creation will only ever be done once - any subsequent installs
should be using an existing database.

Attaching the database would be the easiest way, but the user creation,
that maps for the groups in Active Directory to a user in SQL Server,
has to be done programmaticaly because the group name is created during
install, using the machine name of the server the software is running on
(similar to the security group naming scheme used by SQL Server in fact).

[quoted text, click to view]

Chris Crowther
Developer

Thanks for your reply Chris,

If the size of the package isn't a problem, I think using the "attaching
database file" approach would be preferred. For the logins/users mapped to
Active directory groups, I think you need to use execute some extra query
to create them after the database has been attached.

Anyway, if there is anything else you wonder, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

With SQL Express I find that we can just copy the database and its log and
the configuration file to a new installation and away it goes.

On stronger versions of MS SQL is that not also true?

It is the easiest way I can think of to setup a computer.

[quoted text, click to view]