Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

EndWith

Catch ex As Exception

MsgBox(ex.ToString)

EndTry

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating a User in Sql....

By Default the sql have the user Sa...
I want to create a user with permissons to create a
database...
How can i do it?
If you have some code sample i would apreciate a lot!
Thanks in advance> By Default the sql have the user Sa...

quote:

---

> I want to create a user with permissons to create a
> database...
> How can i do it?
> If you have some code sample i would apreciate a lot!

---

You should check and learn about the security as soon as possible. Using sa
is a very bad practice. Start with "Managing Security" module in Books
OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm). You will find out that you need to
create a login (use Windows logins, if it is possible) and put the login in
the dbcreator fixed server role.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

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 Script out of Sample Data

SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO script?

I have a commercial application that I would like to include sample data, and instead of restoring a backup like I am doing now, I would like to first run a script that creates the database, stored procedures, etc, then run a script that inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based on exisiting data?

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comTim,

You can use our free tool, QALite or ObjectScriptr to generate insert
statements. You can download it from site.

--
-oj
http://www.rac4sql.net

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:J%1Lb.761723$HS4.6022647@.attbi_s01...
SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO
script?

I have a commercial application that I would like to include sample data, and
instead of restoring a backup like I am doing now, I would like to first run a
script that creates the database, stored procedures, etc, then run a script that
inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based
on exisiting data?

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||"Tim Morrison" <sales@.kjmsoftware.com> wrote in message news:<J%1Lb.761723$HS4.6022647@.attbi_s01>...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
> INTO script?
> I have a commercial application that I would like to include sample
> data, and instead of restoring a backup like I am doing now, I would
> like to first run a script that creates the database, stored procedures,
> etc, then run a script that inserts sample data if the customer so
> chooses.
> I know I can do this manually, but is there any way to create the script
> based on exisiting data?
> TIA
> --
> Tim Morrison
> ----------------------
> ---
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
> related website.
> http://www.vehiclewebstudio.com
> --

You could use BCP/DTS to load the sample data from external files, but
if you want to use pure TSQL, then this is one possibility:

http://vyaskn.tripod.com/code.htm#inserts

Simon|||Looks like ObjectScriptr is just what im looking for.

Thanks,

Tim Morrison

"oj" <nospam_ojngo@.home.com> wrote in message
news:JN7Lb.781998$Fm2.760879@.attbi_s04...
> Tim,
> You can use our free tool, QALite or ObjectScriptr to generate insert
> statements. You can download it from site.
> --
> -oj
> http://www.rac4sql.net
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:J%1Lb.761723$HS4.6022647@.attbi_s01...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
INTO
> script?
> I have a commercial application that I would like to include sample data,
and
> instead of restoring a backup like I am doing now, I would like to first
run a
> script that creates the database, stored procedures, etc, then run a
script that
> inserts sample data if the customer so chooses.
> I know I can do this manually, but is there any way to create the script
based
> on exisiting data?
> TIA
> --
> Tim Morrison
> -----------------------
--
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
related
> website.
> http://www.vehiclewebstudio.com|||Hi !
Try http://www.sqlscripter.com

This tool is able to create INSERT, UPDATE and DELETE commands.

Nicksql

Creating a Custom Connection Manager Sample

http://msdn2.microsoft.com/en-us/library/ms345276.aspx

does anyone know where this can be obtained? it did not come with the RTM. it does not seem to be available via download either?

a little help here!

If you will provide an email address, I can email you this sample and a 2nd custom connection manager sample that will be included in the next refresh of BOL, scheduled to coincide with SP1.

-Doug

|||

andy.d.loechler@.wellsfargo.com

thanks

|||Can I get it too. (cipracunari at sezampro dot yu)
|||

As Books Online has been updated, we also have some updated samples to match-

Download details: SQL Server 2005 Samples and Sample Databases (April 2006)
(http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en)

It is in the SqlServerSamples.msi.

Creating a Custom Connection Manager Sample

http://msdn2.microsoft.com/en-us/library/ms345276.aspx

does anyone know where this can be obtained? it did not come with the RTM. it does not seem to be available via download either?

a little help here!

If you will provide an email address, I can email you this sample and a 2nd custom connection manager sample that will be included in the next refresh of BOL, scheduled to coincide with SP1.

-Doug

|||

andy.d.loechler@.wellsfargo.com

thanks

|||Can I get it too. (cipracunari at sezampro dot yu)|||

As Books Online has been updated, we also have some updated samples to match-

Download details: SQL Server 2005 Samples and Sample Databases (April 2006)
(http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en)

It is in the SqlServerSamples.msi.

Creating & Maintaining Demo Database

I would like to create a demo database. Here are the specifications.
1. I need an easy way to store the sample data so that the database can
be refreshed after use and put back to its original data.
2. I am constantly making changes to the production database that this
demo is based off of. I would like to have a simple way of keeping the
demo database (table structures and stored procedures) in sync with the
production database without affecting the data (see #1). I worry about
making changes to the production database and forgetting to make the
change in the demo database.
I have some ideas but would love to hear how others have done this sort
of thing.
Thanks in advance,
Debbie
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!1. Back it up and restore it whenever you need to...
2. Every shop handles this type of thing differently, but my personal core
rules are: Never make changes on a production database via Enterprise
Manager, Query Analyzer's UI options, or any other database frontend UI.
Instead, script all changes and check them in to a source control system
(e.g. Visual Source Safe). This keeps a very good audit trail and allows
you to easily roll changes to other databases without scratching your head
and wondering what has changed between versions. Another option is to use a
product like Red Gate's SQL Compare, but I highly recommend you get into the
habit of scripting changes and storing them instead of relying on software
to find the changes after you make them (in other words, be proactive rather
than reactive!)
"Debbie" <anonymous@.email.com> wrote in message
news:ukvh7#K$DHA.1548@.TK2MSFTNGP12.phx.gbl...
> I would like to create a demo database. Here are the specifications.
> 1. I need an easy way to store the sample data so that the database can
> be refreshed after use and put back to its original data.
> 2. I am constantly making changes to the production database that this
> demo is based off of. I would like to have a simple way of keeping the
> demo database (table structures and stored procedures) in sync with the
> production database without affecting the data (see #1). I worry about
> making changes to the production database and forgetting to make the
> change in the demo database.
> I have some ideas but would love to hear how others have done this sort
> of thing.
> Thanks in advance,
> Debbie
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||hello Debbie,
have a look at www.dbghost.com for an outline of a
process which will rid you of any code change management
issues - guarenteed. You can use it with SourceSafe (read
Adams comment) making it a true change management
proposal.
regards,
Mark Baekdal
>--Original Message--
>I would like to create a demo database. Here are the
specifications.
>1. I need an easy way to store the sample data so that
the database can
>be refreshed after use and put back to its original data.
>2. I am constantly making changes to the production
database that this
>demo is based off of. I would like to have a simple way
of keeping the
>demo database (table structures and stored procedures)
in sync with the
>production database without affecting the data (see
#1). I worry about
>making changes to the production database and forgetting
to make the
>change in the demo database.
>I have some ideas but would love to hear how others have
done this sort
>of thing.
>Thanks in advance,
>Debbie
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

Creating & Maintaining Demo Database

I would like to create a demo database. Here are the specifications.
1. I need an easy way to store the sample data so that the database can
be refreshed after use and put back to its original data.
2. I am constantly making changes to the production database that this
demo is based off of. I would like to have a simple way of keeping the
demo database (table structures and stored procedures) in sync with the
production database without affecting the data (see #1). I worry about
making changes to the production database and forgetting to make the
change in the demo database.
I have some ideas but would love to hear how others have done this sort
of thing.
Thanks in advance,
Debbie
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!1. Back it up and restore it whenever you need to...
2. Every shop handles this type of thing differently, but my personal core
rules are: Never make changes on a production database via Enterprise
Manager, Query Analyzer's UI options, or any other database frontend UI.
Instead, script all changes and check them in to a source control system
(e.g. Visual Source Safe). This keeps a very good audit trail and allows
you to easily roll changes to other databases without scratching your head
and wondering what has changed between versions. Another option is to use a
product like Red Gate's SQL Compare, but I highly recommend you get into the
habit of scripting changes and storing them instead of relying on software
to find the changes after you make them (in other words, be proactive rather
than reactive!)
"Debbie" <anonymous@.email.com> wrote in message
news:ukvh7#K$DHA.1548@.TK2MSFTNGP12.phx.gbl...
> I would like to create a demo database. Here are the specifications.
> 1. I need an easy way to store the sample data so that the database can
> be refreshed after use and put back to its original data.
> 2. I am constantly making changes to the production database that this
> demo is based off of. I would like to have a simple way of keeping the
> demo database (table structures and stored procedures) in sync with the
> production database without affecting the data (see #1). I worry about
> making changes to the production database and forgetting to make the
> change in the demo database.
> I have some ideas but would love to hear how others have done this sort
> of thing.
> Thanks in advance,
> Debbie
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for the response.
On the backup/restore solution, I am concerned that when we make changes
to the database, the restore will overwrite any of our changes with the
old version.
Any other ideas will be welcome, I really want to think this through
before turning something over to my sales department.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Yes, it will overwrite any changes. So restore, apply your changes, then
back up the new version
"Debbie" <anonymous@.email.com> wrote in message
news:efdRKVL$DHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the response.
> On the backup/restore solution, I am concerned that when we make changes
> to the database, the restore will overwrite any of our changes with the
> old version.
> Any other ideas will be welcome, I really want to think this through
> before turning something over to my sales department.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

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)