Hi,
I'm interested in looking at SQL Server Mobile Edition (Laguna) and
wondered if there was a beta program for it, and the possibility of
getting on the program.
http://www.microsoft.com/sql/ce/productinfo/SQLMobile.asp
Thanks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.htmlSorry, wrong newsgroup, please ignore.
Mark Allison wrote:
> Hi,
> I'm interested in looking at SQL Server Mobile Edition (Laguna) and
> wondered if there was a beta program for it, and the possibility of
> getting on the program.
> http://www.microsoft.com/sql/ce/productinfo/SQLMobile.asp
> Thanks.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
Showing posts with label program. Show all posts
Showing posts with label program. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
Best Way to 'Program' for SRS
My background is as a Java programmer.
I am using SRS to create some reports. I am having a hard time getting used
to only dealing with the output from the database server. I'm used to having
access to objects, methods, etc...
I am trying to determine if I am better off just writing stored procedures
or if there is some way to use a .Net language (perhaps C#).
My data needs a fair amount of processing after it comes out of the database
before it is ready for display.
For instance, I need to get a row, perform some calculations, update another
row and then display... I think I could get it done in a stored proc, but it
still seems like a limited environment and a lot of what I need seems to
require cursors, which I am told are expensive.
So... Any comments on the best way to attack this?
Can I use C# to sit between my database and SRS? What kind of output would
my C# classes send to SRS?Hunter,
I would gravitate toward doing as much as possible data massaging at the
data source level. It will be less expensive than doing it in code. RS
allows you to use expressions where the field values can be changed before
displayed on the report but in your case this may not be enough. Another way
you can tackle this is to create a custom data extension which will get the
dataset, pre-process it, and return it to the report. Mine ADO.NET dataset
extension could get you started.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:7D8F6391-A8BB-49A8-B279-7EFF622398B9@.microsoft.com...
> My background is as a Java programmer.
> I am using SRS to create some reports. I am having a hard time getting
used
> to only dealing with the output from the database server. I'm used to
having
> access to objects, methods, etc...
> I am trying to determine if I am better off just writing stored procedures
> or if there is some way to use a .Net language (perhaps C#).
> My data needs a fair amount of processing after it comes out of the
database
> before it is ready for display.
> For instance, I need to get a row, perform some calculations, update
another
> row and then display... I think I could get it done in a stored proc, but
it
> still seems like a limited environment and a lot of what I need seems to
> require cursors, which I am told are expensive.
> So... Any comments on the best way to attack this?
> Can I use C# to sit between my database and SRS? What kind of output would
> my C# classes send to SRS?|||I agree with Leo... If there are common functions, etc, do these in views
or in the sql for your data source. Especially when you do views, these
expressions can be used by many reports, ( like formatting a name, or date,
or taking several values and making some business calculation). You can
write code in each report using VB, and that is OK for small things, but the
code is not sharable across many reports except though copy/paste. Which
leaves writing a customer assembly, and referencing it in reports. This
allows for sharing a single code base of common functions across many
reports, but increases the level of complexity and management...
hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:7D8F6391-A8BB-49A8-B279-7EFF622398B9@.microsoft.com...
> My background is as a Java programmer.
> I am using SRS to create some reports. I am having a hard time getting
used
> to only dealing with the output from the database server. I'm used to
having
> access to objects, methods, etc...
> I am trying to determine if I am better off just writing stored procedures
> or if there is some way to use a .Net language (perhaps C#).
> My data needs a fair amount of processing after it comes out of the
database
> before it is ready for display.
> For instance, I need to get a row, perform some calculations, update
another
> row and then display... I think I could get it done in a stored proc, but
it
> still seems like a limited environment and a lot of what I need seems to
> require cursors, which I am told are expensive.
> So... Any comments on the best way to attack this?
> Can I use C# to sit between my database and SRS? What kind of output would
> my C# classes send to SRS?
I am using SRS to create some reports. I am having a hard time getting used
to only dealing with the output from the database server. I'm used to having
access to objects, methods, etc...
I am trying to determine if I am better off just writing stored procedures
or if there is some way to use a .Net language (perhaps C#).
My data needs a fair amount of processing after it comes out of the database
before it is ready for display.
For instance, I need to get a row, perform some calculations, update another
row and then display... I think I could get it done in a stored proc, but it
still seems like a limited environment and a lot of what I need seems to
require cursors, which I am told are expensive.
So... Any comments on the best way to attack this?
Can I use C# to sit between my database and SRS? What kind of output would
my C# classes send to SRS?Hunter,
I would gravitate toward doing as much as possible data massaging at the
data source level. It will be less expensive than doing it in code. RS
allows you to use expressions where the field values can be changed before
displayed on the report but in your case this may not be enough. Another way
you can tackle this is to create a custom data extension which will get the
dataset, pre-process it, and return it to the report. Mine ADO.NET dataset
extension could get you started.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:7D8F6391-A8BB-49A8-B279-7EFF622398B9@.microsoft.com...
> My background is as a Java programmer.
> I am using SRS to create some reports. I am having a hard time getting
used
> to only dealing with the output from the database server. I'm used to
having
> access to objects, methods, etc...
> I am trying to determine if I am better off just writing stored procedures
> or if there is some way to use a .Net language (perhaps C#).
> My data needs a fair amount of processing after it comes out of the
database
> before it is ready for display.
> For instance, I need to get a row, perform some calculations, update
another
> row and then display... I think I could get it done in a stored proc, but
it
> still seems like a limited environment and a lot of what I need seems to
> require cursors, which I am told are expensive.
> So... Any comments on the best way to attack this?
> Can I use C# to sit between my database and SRS? What kind of output would
> my C# classes send to SRS?|||I agree with Leo... If there are common functions, etc, do these in views
or in the sql for your data source. Especially when you do views, these
expressions can be used by many reports, ( like formatting a name, or date,
or taking several values and making some business calculation). You can
write code in each report using VB, and that is OK for small things, but the
code is not sharable across many reports except though copy/paste. Which
leaves writing a customer assembly, and referencing it in reports. This
allows for sharing a single code base of common functions across many
reports, but increases the level of complexity and management...
hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:7D8F6391-A8BB-49A8-B279-7EFF622398B9@.microsoft.com...
> My background is as a Java programmer.
> I am using SRS to create some reports. I am having a hard time getting
used
> to only dealing with the output from the database server. I'm used to
having
> access to objects, methods, etc...
> I am trying to determine if I am better off just writing stored procedures
> or if there is some way to use a .Net language (perhaps C#).
> My data needs a fair amount of processing after it comes out of the
database
> before it is ready for display.
> For instance, I need to get a row, perform some calculations, update
another
> row and then display... I think I could get it done in a stored proc, but
it
> still seems like a limited environment and a lot of what I need seems to
> require cursors, which I am told are expensive.
> So... Any comments on the best way to attack this?
> Can I use C# to sit between my database and SRS? What kind of output would
> my C# classes send to SRS?
Sunday, February 12, 2012
Best way to generate script
Hi!
I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:
ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")
For Each uneTable In BDConfig.Tables
If Not uneTable.IsSystemObject Then
NouvelleBD.ExecuteNonQuery(uneTable.Script)
End If
Next
We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?
Thank and sorry about my bad English ^_^
I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:
Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0
Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure
db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next
scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True
ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)
This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.
|||Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.
Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.
|||I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.
Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()
I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||
Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.
For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.
I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.
Best way to generate script
Hi!
I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:
ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")
For Each uneTable In BDConfig.Tables
If Not uneTable.IsSystemObject Then
NouvelleBD.ExecuteNonQuery(uneTable.Script)
End If
Next
We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?
Thank and sorry about my bad English ^_^
I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:
Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0
Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure
db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next
scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True
ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)
This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.
|||Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.
Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.
|||I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.
Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()
I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||
Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.
For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.
I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.
Best way to create SQL2005 DB from VB.Net
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 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 I
need 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 divantage 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
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
Subscribe to:
Posts (Atom)