database from another data source. At the moment I create the empty SQL
database by manually running a DDL script in SQL Server Management Studio.
However, I need to modify the VB.Net program so that the database is created
programmatically before beginning the load process. My question is how best
to do this?
I know I can do this by cutting and pasting the DDL into Visual Studio and
converting it into a string. But this is an onerous task, and as the DDL is
still evolving I do not consider this an option.
Another option is to open the DDL file, read it into a string, and then
execute it. Has anyone done this? One di
vantage of this option is that Ineed to ship the DDL as a separate file with my app and this leaves it open
to modification by the user.
If there is another way I haven’t come across I would greatly appreciate a
few pointers.You can create a vb application that accepts the user inputs like database
name, server name and other parameters and use SQL-DMO to creae the database
.
You can check this link for the code...
http://groups.google.co.in/group/mi...7dfc44c4bf8e926
and this link for the interface...
http://www.codeproject.com/useritems/SQLDBBackup.asp
Hope this was what you wanted.
"David" wrote:
> I have written a VB.Net (2005) program to load an empty SQL Server 2005
> database from another data source. At the moment I create the empty SQL
> database by manually running a DDL script in SQL Server Management Studio.
> However, I need to modify the VB.Net program so that the database is creat
ed
> programmatically before beginning the load process. My question is how bes
t
> to do this?
> I know I can do this by cutting and pasting the DDL into Visual Studio and
> converting it into a string. But this is an onerous task, and as the DDL i
s
> still evolving I do not consider this an option.
> Another option is to open the DDL file, read it into a string, and then
> execute it. Has anyone done this? One di
vantage of this option is that I
> need to ship the DDL as a separate file with my app and this leaves it ope
n
> to modification by the user.
> If there is another way I haven’t come across I would greatly appreciate
a
> few pointers.
>|||I had a look at SQL-DMO in the SQL Server Books Online and quickly discovere
d
it has been superseded by SQL-SMO (SQL-DMO is a COM object, whereas SMO is
implemented as a .NET assembly).
Since I'm using VS2005 I'll look into SMO and will see how I go.
"Omnibuzz" wrote:
> You can create a vb application that accepts the user inputs like database
> name, server name and other parameters and use SQL-DMO to creae the databa
se.
> You can check this link for the code...
> http://groups.google.co.in/group/mi...7dfc44c4bf8e926
> and this link for the interface...
> http://www.codeproject.com/useritems/SQLDBBackup.asp
> Hope this was what you wanted.
>
> "David" wrote:
>|||Thats right. But implemetation in SMO hasn't got much documentation yet. I
had been trying to get it and didn't get much help.
Thats why suggested DMo. I apologise if I misled you :)
--
"David" wrote:
> I had a look at SQL-DMO in the SQL Server Books Online and quickly discove
red
> it has been superseded by SQL-SMO (SQL-DMO is a COM object, whereas SMO is
> implemented as a .NET assembly).
> Since I'm using VS2005 I'll look into SMO and will see how I go.
> "Omnibuzz" wrote:
>|||I have been experimenting with SMO, and while I can get it to create
databases, tables, etc, I can't work out how to submit a script file for
execution. Do you know how this is done in SQL_DMO (there is a good cross
reference in the docs)?
P.S. In my case I have to use a script file as the DB design is done in
Visio and the script is generated for me. To convert this to command using
the SMO objects would take forever, and I would have to duplicate changes
made in the Visio diagram.
"Omnibuzz" wrote:
> Thats right. But implemetation in SMO hasn't got much documentation yet. I
> had been trying to get it and didn't get much help.
> Thats why suggested DMo. I apologise if I misled you :)
> --
>
>
> "David" wrote:
>|||try ExecuteImmediate and the ExecuteWithResults methods in the dmo for
executing scripts..
Is this the one you are looking for'
--
"David" wrote:
> I have been experimenting with SMO, and while I can get it to create
> databases, tables, etc, I can't work out how to submit a script file for
> execution. Do you know how this is done in SQL_DMO (there is a good cross
> reference in the docs)?
> P.S. In my case I have to use a script file as the DB design is done in
> Visio and the script is generated for me. To convert this to command using
> the SMO objects would take forever, and I would have to duplicate changes
> made in the Visio diagram.
> "Omnibuzz" wrote:
>|||Did you try, for instance Database.ExecuteNonQuery or Database.ExecuteWithRe
sults?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:17A1C906-2BF4-4662-8ADC-6FA34C673214@.microsoft.com...
>I have been experimenting with SMO, and while I can get it to create
> databases, tables, etc, I can't work out how to submit a script file for
> execution. Do you know how this is done in SQL_DMO (there is a good cross
> reference in the docs)?
> P.S. In my case I have to use a script file as the DB design is done in
> Visio and the script is generated for me. To convert this to command using
> the SMO objects would take forever, and I would have to duplicate changes
> made in the Visio diagram.
> "Omnibuzz" wrote:
>|||Many thanks to you both (Ominbuzz and Tibor). Databases.ExecuteNonQuery work
s
like a charm.
The only thing I need to do now is work out how to get the script text file
into the compiled exe so that I don't have to deploy it and users can't
change it. But that would probably be better posted in the Vb.Net newsgroup.
"Tibor Karaszi" wrote:
> Did you try, for instance Database.ExecuteNonQuery or Database.ExecuteWith
Results?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:17A1C906-2BF4-4662-8ADC-6FA34C673214@.microsoft.com...
>
>|||Perhaps you can zip (or similar) it with a password and/or encryption and ha
ve your code unzip it.
I'm not .NET expert, though, so there are probably much neater ways of doing
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:51F43AB1-FE3A-440E-BF67-F9A9859C80AE@.microsoft.com...
> Many thanks to you both (Ominbuzz and Tibor). Databases.ExecuteNonQuery wo
rks
> like a charm.
> The only thing I need to do now is work out how to get the script text fil
e
> into the compiled exe so that I don't have to deploy it and users can't
> change it. But that would probably be better posted in the Vb.Net newsgrou
p.
> "Tibor Karaszi" wrote:
>|||or you can just add the script as a string or a file your project resources.
http://msdn2.microsoft.com/en-us/li...resourcemanager(VS
.80).aspx
-oj
No comments:
Post a Comment