Creating an installer for SQL-side components of an app

Hello.
I'm researching about the ways I can create an installer of the SQL (2000)
objects for an application.
At the present time we're using a set of calls to osql utility from a VB
application. But the maintenance of the scripts is becoming cumbersome.
Does MS provides utilities for this? Where can I look up for further info?
Thanks.-
| Thread-Topic: Creating an installer for SQL-side components of an app
| thread-index: AcThIuI4MycuAx2CSYGbOG6c0kGPaQ==
| X-WBNR-Posting-Host: 200.44.173.82
| From: =?Utf-8?B?cnBhbGxhcmVz?= <rpallares@.discussions.microsoft.com>
| Subject: Creating an installer for SQL-side components of an app
| Date: Mon, 13 Dec 2004 06:49:01 -0800
| Lines: 12
| Message-ID: <CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clients
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.clients:29256
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| Hello.
|
| I'm researching about the ways I can create an installer of the SQL
(2000)
| objects for an application.
|
| At the present time we're using a set of calls to osql utility from a VB
| application. But the maintenance of the scripts is becoming cumbersome.
|
| Does MS provides utilities for this? Where can I look up for further info?
|
| Thanks.-
|
|
<><><><><><><><><><><><><><><><><><><><><><><><><> <><><>
Hi,
If you still need assistance and you're using MSDE then I believe you will
find this link useful:
http://msdn.microsoft.com/library/de...us/dnmsde/html
/msdedepl.asp
Regards,
Yasemin Gunduz
Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What u mean with the SQL server Objects ?
"rpallares" <rpallares@.discussions.microsoft.com> wrote in message
news:CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com...
> Hello.
> I'm researching about the ways I can create an installer of the SQL (2000)
> objects for an application.
> At the present time we're using a set of calls to osql utility from a VB
> application. But the maintenance of the scripts is becoming cumbersome.
> Does MS provides utilities for this? Where can I look up for further info?
> Thanks.-
>

creating a subscription via an application

Hello,

I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.

John

There are SOAP APIs that lets you create subscriptions programmatically. Is that what you are looking for? See the following link for details.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

Thanks,

Sharmila

|||This was it. Thank you!!

Creating a database from script - permissions problems

We deploy our app via ClickOnce. Rather than shipping the .mdf and .ldf, the app detects whether the database is there and creates it via script if it's not. The script was originally generated from SQL Exrpess Mangement Console, etc.

In the past, we've made the stipulation that the user must have administrator access to the machine. However, we now need to find a workable solution for users who are not administrators on their machine. Currently, we use the following connection string before attempting to run the db creation script:

conn.ConnectionString = "Server=.\\sqlexpress;Integrated Security = true;User Instance=false";

The script fails when trying to execute "CREATE DATABASE [myDatabaseName] ON PRIMARY "

with the error:

"CREATE DATABASE permission denied in database 'Master'"

How can we get around this permissions issue in the most automated way possible?

TIA!

Hi,

you will need to have a user with dbcreator permissions, otherwise you won′t be able to make an entry in the sysdatabases (Creating a database). Can you use any administrative SQL Server login to connect to the database and make this possible ? The connection string has to include the userid as well as the password then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Creating a Database for deployment in SQL Server

I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.

Should i use database creation scripts as I have previously used on other apps, or is there a more recognised modern way of creating the DB. I have heard mention of "Sql Server Management Studio" possibly being useful here. If so, will there be issues if one of the customers buying my application uses Oracle?

Any advice appreciated.

Cheers,

StewManagement Studio certainly won't work with Oracle. If your main objective is portability, I suppose scripts is still your best bet.

Creating a Database for deployment in SQL Server

I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.

Should i use database creation scripts as I have previously used on other apps, or is there a more recognised modern way of creating the DB. I have heard mention of "Sql Server Management Studio" possibly being useful here. If so, will there be issues if one of the customers buying my application uses Oracle?

Any advice appreciated.

Cheers,

StewManagement Studio certainly won't work with Oracle. If your main objective is portability, I suppose scripts is still your best bet.

CreateSPs Sample Application Causes Exception.

When I use the CreateSPs sample app on a sample database (Person), with a sample table (Contact), the following exception occurred.

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

Add object to collection failed for StoredProcedureParameterCollection of StoredProcedure 'SmoDemo.uspContactSelect'. (Microsoft.SqlServer.Smo)

-
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1116.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add object to collection+StoredProcedureParameterCollection&LinkId=20476

-
Program Location:

at Microsoft.SqlServer.Management.Smo.ArrayListCollectionBase.AddImpl(SqlSmoObject obj)
at Microsoft.SqlServer.Management.Smo.StoredProcedureParameterCollection.Add(StoredProcedureParameter storedProcedureParameter)
at Microsoft.Samples.SqlServer.CreateStoredProcs.CreateSelectProcedure(Schema spSchema, Table tbl) in C:\Program Files\Microsoft SQL Server 2005 Samples\Engine\Programmability\SMO\CreateSPs\CS\CreateSPs\CreateStoredProcs.cs:line 276

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

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

-
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1116.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

-
Program Location:

at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.CheckCollectionLock()
at Microsoft.SqlServer.Management.Smo.ArrayListCollectionBase.AddImpl(SqlSmoObject obj, Int32 insertAtPosition)
at Microsoft.SqlServer.Management.Smo.ArrayListCollectionBase.AddImpl(SqlSmoObject obj)

I have filed a bug for this sample to get fixed.

Thanks for reporting!

|||Can you please post the DDL that you used to create the database and table?

Thanks,
Tony Green [MSFT]|||

Hey Tony

Here is the code, if you'd like me to e-mail you a .cs file then please let me have your e-mail address.

The database was created with SMO, as was the table and associated index.

I'm just learning SMO now and cutting and pasting from other samples so please let me know if you see anything obviously wrong on my end.

Also worthy of note is this code is really just a command line experiment and has not undergone the checks and balances I'd usually do before sharing it.

Thanks

- Doug

using System;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace DatabaseFactory
{
internal static class DatabaseFactory
{
public static Database Create(string name)
{
Server _server = new Server(new ServerConnection("localhost"));

if (_server.Databases.Contains(name))
{
Console.WriteLine("Database already exists");

return null;
}
else
{
Database _database = new Database(_server, name);

FileGroup fg = new FileGroup(_database, @."PRIMARY");

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path of the server
DataFile df = new DataFile(fg, name + @."_Data0",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data0" + @.".mdf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 1024;

// Set initial size in KB (optional)
df.Size = 10240;

// Set the maximum size in KB
df.MaxSize = 20480;

// Add file to file group
fg.Files.Add(df);

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path of the server
df = new DataFile(fg, name + @."_Data1",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data1" + @.".ndf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 1024;

// Set initial size in KB (optional)
df.Size = 2048;

// Set the maximum size in KB
df.MaxSize = 8192;

// Add file to file group
fg.Files.Add(df);

// Add the new file group to the database's FileGroups collection
_database.FileGroups.Add(fg);

// Create a new file group named SECONDARY
fg = new FileGroup(_database, @."SECONDARY");

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path of the server
df = new DataFile(fg, name + @."_Data2",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data2" + @.".ndf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 512;

// Set initial size in KB (optional)
df.Size = 1024;

// Set the maximum size in KB
df.MaxSize = 4096;

// Add file to file group
fg.Files.Add(df);

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path
df = new DataFile(fg, name + @."_Data3",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data3" + @.".ndf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 512; // In KB

// Set initial size in KB (optional)
df.Size = 1024; // Set initial size in KB (optional)

// Set the maximum size in KB
df.MaxSize = 4096;

// Add file to file group
fg.Files.Add(df);

// Add the new file group to the database's FileGroups collection
_database.FileGroups.Add(fg);

// Define the database transaction log.
LogFile lf = new LogFile(_database, name + @."_Log",
_server.Settings.MasterDBPath + @."\" + name +
@."_Log" + @.".ldf");

// Set the growth type to KB
lf.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
lf.Growth = 1024; // In KB

// Set initial size in KB (optional)
lf.Size = 2048; // Set initial size in KB (optional)

// Set the maximum size in KB
lf.MaxSize = 8192; // In KB

// Add file to file group
_database.LogFiles.Add(lf);

// Create the database as defined.
_database.Create();

return _database;
}
}
}

internal static class TableFactory
{
internal static Table Create(Database db, string name)
{
if (db.Tables.Contains(name) == false)
{
Default _default = new Default(db, "dfltEmptyString");

if (_default == null)
{
_default.TextBody = @."''";
_default.Create();
}

Table _table = new Table(db, name, db.DefaultSchema);

StringBuilder columnBuilder = new StringBuilder(name);
columnBuilder.Append("ID");

Column _column = new Column(_table, columnBuilder.ToString(), DataType.Int);
_table.Columns.Add(_column);
_column.Nullable = false;
_column.Identity = true;
_column.IdentitySeed = 1;
_column.IdentityIncrement = 1;

StringBuilder indexBuilder = new StringBuilder(@."PK_");
indexBuilder.Append(_table.Name);

Index _index = new Index(_table, indexBuilder.ToString());
_table.Indexes.Add(_index);
_index.IndexedColumns.Add(new IndexedColumn(_index, _column.Name));
_index.IsClustered = true;
_index.IsUnique = true;
_index.IndexKeyType = IndexKeyType.DriPrimaryKey;

_table.Create();

return _table;
}
else
{
return null;
}
}
}

//internal static class StoredProcedureFactory
//{
// internal static StoredProcedureCollection Create(Database database, Table table)
// {
// StoredProcedure sp = new StoredProcedure(database, "usp_ContactSelect");

// sp.TextBody = @."select * from Criteria";

// sp.TextHeader = @."-- comment";

// sp.Create();

// return database.StoredProcedures;
// }
//}

class Program
{
static void Main(string[] args)
{
Database _database = DatabaseFactory.Create("Person");

Table _table = null;

if (_database != null)
{
_table = TableFactory.Create(_database, "Contact");
}

//if (_table != null)
//{
// StoredProcedureCollection procedures = StoredProcedureFactory.Create(_database, _table);
//}
}
}
}

|||This was fixed after the last CTP and will be in the next. If you want to fix it now add the line below in red.

// Create the new stored procedure object

sp = new StoredProcedure(tbl.Parent, procName, spSchema.Name);

sp.TextMode = false; //ADD THIS LINE

foreach (Column col in tbl.Columns)