C#, SQL-DMO, Add FileGroup Issue.

#1

Greetings All, I was hoping that someone might be able to shed some
light on this issue. I am trying to add a FileGroup/Datafile to an
existing SQL database. The code below compile and it runs in .NET
Studio, however nothing happens? The form closes, no error messages
are thrown, there is no sign of any type of error taking place. The
end result (assuming the code is correct) is tha there should be a new
FileGroup with a datafile in it, here is the code:

************************************************** ******************
private void AddFileGroup(frmMyForm f)
{
SQLMy.SQLServer MySQLServerName = new SQLMy.SQLServer();
SQLMy.Database MyDBDbName = new SQLMy.Database();
SQLMy.FileGroup MyDBDataGroup = new SQLMy.FileGroup();
SQLMy.DBFile MyDBDataFile = new SQLMy.DBFile();

try
{
MySQLServerName.Connect
myGetConfigData.OlapServerName*,myGetConfigData.Ol apUserLogin*,myGetConfigData.OlapUserPassw*ord);

MyDBDbName.Name =
myConnectionData.OlapDatabaseN*ame.ToString().Trim ();

MyDBDataGroup.Name = "DBDataGroup";
MyDBDbName.FileGroups.Add (MyDBDataGroup);

MyDBDataFile.Name = "DBData";
MyDBDataFile.PhysicalName =
myConnectionData.OlapDBDataPat*h.ToString().Trim() + @"\DBData.ndf";
MyDBDataFile.Size = 50;
MyDBDataFile.MaximumSize = -1;
MyDBDataFile.FileGrowth = 5;
MyDBDataFile.FileGrowthType = 0;
MyDBDataFile.PrimaryFile = false;

MyDBDbName.FileGroups.Item("DB*DataGroup").DBFiles .Ad (MyDBDataFile);

this.Close();
MySQLServerName.DisConnect();
}

catch (Exception e)
{
MessageBox.Show(e.Message);

}

finally
{
MySQLServerName.DisConnect();

}
}

************************************************** ******************

Note: myGetConfigData: This is a class that reads in the values for the
db connection from an XML file. This class has been tested extensively
and the data values are being populated into the variables.

I would appreciate any help that anyone might be able to provide to me.

Regards, TFD.

#2

Hi

Have you run profiler to see what it is doing at the database end?

John

"LineVoltageHalogen" <tropicalfruitdrops (AT) yahoo (DOT) com> wrote

Greetings All, I was hoping that someone might be able to shed some
light on this issue. I am trying to add a FileGroup/Datafile to an
existing SQL database. The code below compile and it runs in .NET
Studio, however nothing happens? The form closes, no error messages
are thrown, there is no sign of any type of error taking place. The
end result (assuming the code is correct) is tha there should be a new
FileGroup with a datafile in it, here is the code:

************************************************** ******************
private void AddFileGroup(frmMyForm f)
{
SQLMy.SQLServer MySQLServerName = new SQLMy.SQLServer();
SQLMy.Database MyDBDbName = new SQLMy.Database();
SQLMy.FileGroup MyDBDataGroup = new SQLMy.FileGroup();
SQLMy.DBFile MyDBDataFile = new SQLMy.DBFile();

try
{
MySQLServerName.Connect
myGetConfigData.OlapServerName*,myGetConfigData.Ol apUserLogin*,myGetConfigData.OlapUserPassw*ord);

MyDBDbName.Name =
myConnectionData.OlapDatabaseN*ame.ToString().Trim ();

MyDBDataGroup.Name = "DBDataGroup";
MyDBDbName.FileGroups.Add (MyDBDataGroup);

MyDBDataFile.Name = "DBData";
MyDBDataFile.PhysicalName =
myConnectionData.OlapDBDataPat*h.ToString().Trim() + @"\DBData.ndf";
MyDBDataFile.Size = 50;
MyDBDataFile.MaximumSize = -1;
MyDBDataFile.FileGrowth = 5;
MyDBDataFile.FileGrowthType = 0;
MyDBDataFile.PrimaryFile = false;

MyDBDbName.FileGroups.Item("DB*DataGroup").DBFiles .Ad (MyDBDataFile);

this.Close();
MySQLServerName.DisConnect();
}

catch (Exception e)
{
MessageBox.Show(e.Message);

}

finally
{
MySQLServerName.DisConnect();

}
}

************************************************** ******************

Note: myGetConfigData: This is a class that reads in the values for the
db connection from an XML file. This class has been tested extensively
and the data values are being populated into the variables.

I would appreciate any help that anyone might be able to provide to me.

Regards, TFD.

#3

I used Lumigents Entegra to trace the action. There is only one call
to the table: master.dbo.spt_values, here is the complete text:

************************************************** ************************************************** ******************
-- sp_MSdbuserpriv
select @@version, N'login_id' = convert(int, suser_sid()), N'pagesize'
= v.low, N'highbit' = v2.low, N'highbyte' = v3.low,
N'casesens' = (case when (N'A' != N'a') then 1 else 0 end), @@spid,
convert(sysname, serverproperty(N'servername')),
is_srvrolemember(N'sysadmin'), @dbrole,
N'InstanceName' = convert(sysname, serverproperty(N'instancename')),
N'PID' = convert(int, serverproperty(N'processid'))
from master..spt_values v,master..spt_values v2,master..spt_values v3
where v.number=1 and v.type=N'E' and v2.number=2
and v2.type=N'E' and v3.number=3 and v3.type=N'E'
************************************************** ************************************************** *******************

So, it looks like the connection is being made but the command to
create the filegroup/datafile is never being issued?

TFD

#4

LineVoltageHalogen (tropicalfruitdrops (AT) yahoo (DOT) com) writes:

Quote:

I used Lumigents Entegra to trace the action. There is only one call
to the table: master.dbo.spt_values, here is the complete text:

-- sp_MSdbuserpriv

Given the name of the procedure, I could be that you fail a permission
check. What priviledges do the user you connect with have?

Why you don't an exception raised I don't know, but DMO might
communicate errors by other means. (I don't know DMO myself.)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#5

I am connecting as "sa" who is the owner of the database.

#6

"LineVoltageHalogen" <tropicalfruitdrops (AT) yahoo (DOT) com> wrote

Greetings All, I was hoping that someone might be able to shed some
light on this issue. I am trying to add a FileGroup/Datafile to an
existing SQL database. The code below compile and it runs in .NET
Studio, however nothing happens? The form closes, no error messages
are thrown, there is no sign of any type of error taking place. The
end result (assuming the code is correct) is tha there should be a new
FileGroup with a datafile in it, here is the code:

<snip>

The problem seems to be the way you're using the Database object - it looks
like you've instantiated a database object, but a database object by itself
has nothing to do with a server. So instead of this:

MyDBDataGroup.Name = "DBDataGroup";
MyDBDbName.FileGroups.Add (MyDBDataGroup);

You probably need this:

MyDBDataGroup.Name = "DBDataGroup";
MySQLServerName.Databases(MyDBDbName.Name).FileGro ups.Add(MyDBDataGroup);

Similarly, when you add the file, instead of this (by the way, you have Ad,
not Add, but I guess that's a copy and paste error):

MyDBDbName.FileGroups.Item("DB*DataGroup").DBFiles .Ad (MyDBDataFile);

Try this:

MySQLServerName.Databases(MyDBDbName.Name).FileGro ups("DB*DataGroup").DBFiles.Add
(MyDBDataFile);

In fact, in this case you don't need a database object at all - normally you
only need to instantiate a SQLDMO object when you're creating a completely
new one. To work with an existing object, you just get a reference to it
from the relevant collection on the server. The script below is a Python
version of what you're trying to do - it might make this clearer.

Simon

import win32com.client

srv = win32com.client.Dispatch('SQLDMO.SQLServer2')
fg = win32com.client.Dispatch('SQLDMO.FileGroup')
f = win32com.client.Dispatch('SQLDMO.DBFile')

srv.Name = 'kilkenny'
srv.LoginSecure = True
srv.Connect()

fg.Name = 'NewFileGroup'

srv.Databases('Development').FileGroups.Add(fg)

f.Name = 'NewDataFile'
f.PhysicalName = 'D:\MSSQL\Data\NewDataFile.ndf'
f.Size = 50
f.MaximumSize = -1
f.FileGrowth = 5
f.FileGrowthType = 0
f.PrimaryFile = False

srv.Databases('Development').FileGroups('NewFileGr oup').DBFiles.Add(f)

srv.Disconnect()

#7

Simon, thanks for the feedback. However, when I ran the code it
tripped on the following two lines:

MySQLServerName.Databases(MyDB*DbName.Name).FileGr oups.Add(My*DBDataGroup);

MySQLServerName.Databases(MyDB*DbName.Name).FileGr oups("DB*Da*taGroup").DBFiles.Add
(MyDBDataFile);

The error message was:

'SQLDMO._SQLServer.Databases' denotes a 'property' where a 'method' was
expected

Any ideas?

TFD

#8

"LineVoltageHalogen" <tropicalfruitdrops (AT) yahoo (DOT) com> wrote

Simon, thanks for the feedback. However, when I ran the code it
tripped on the following two lines:

MySQLServerName.Databases(MyDB*DbName.Name).FileGr oups.Add(My*DBDataGroup);

MySQLServerName.Databases(MyDB*DbName.Name).FileGr oups("DB*Da*taGroup").DBFiles.Add
(MyDBDataFile);

The error message was:

'SQLDMO._SQLServer.Databases' denotes a 'property' where a 'method' was
expected

Any ideas?

TFD

I don't know much about C#, but a combination of brute force and ignorance
produced this code, which does work correctly. It seems C# is not as happy
as Python or VBScript to reference objects in the way I suggested earlier,
so a Database object is probably needed after all - apologies for the
misleading response:

SQLDMO.SQLServer2 srv = new SQLDMO.SQLServer2();
SQLDMO.FileGroup2 fg = new SQLDMO.FileGroup2();
SQLDMO._Database db = new SQLDMO.Database();
SQLDMO.DBFile f = new SQLDMO.DBFile();

srv.Name = "kilkenny";
srv.LoginSecure = true;
srv.Connect(null,null,null);

fg.Name = "NewFileGroup";
db = srv.Databases.Item("Development", null);
db.FileGroups.Add(fg);

f.Name = "NewDataFile";
f.PhysicalName = @"D:\MSSQL\Data\NewFile.ndf";
f.Size = 50;
f.MaximumSize = -1;
f.FileGrowth = 5;
f.FileGrowthType = 0;
f.PrimaryFile = false;

fg.DBFiles.Add(f);

srv.DisConnect();

For some reason, this declaration doesn't work, hence the underscore in the
version above:

SQLDMO.Database db = new SQLDMO.Database();

This is just C# ignorance on my part, so I don't know if it's something to
worry about or not.

Simon

#9

Thanks Simon, I will try what you say. On another note do you know of
any good references for SQL-DMO?
I have Mitchell's book but it is for VB and not C#. MSDN only has info
for VB, C or C++ and not C#!

TFD