469,914 Members | 2,329 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,914 developers. It's quick & easy.

C#, SQL-DMO, Add FileGroup Issue.

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.

Jul 23 '05 #1
14 3886
Hi

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

John

"LineVoltageHalogen" <tr****************@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
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.
Jul 23 '05 #2
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

Jul 23 '05 #3
LineVoltageHalogen (tr****************@yahoo.com) writes:
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
I am connecting as "sa" who is the owner of the database.

Jul 23 '05 #5

"LineVoltageHalogen" <tr****************@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
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()
Jul 23 '05 #6
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

Jul 23 '05 #7

"LineVoltageHalogen" <tr****************@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
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
Jul 23 '05 #8
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

Jul 23 '05 #9
Simon, why did you switch to SQLDMO.SQLSERVER2 and two for the other
object types?

TFD

Jul 23 '05 #10
Simon, you ROCK! That worked! I am extremely gratefull to you.

TFD

Jul 23 '05 #11

"LineVoltageHalogen" <tr****************@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Simon, why did you switch to SQLDMO.SQLSERVER2 and two for the other
object types?

TFD


No particular reason - in this case it doesn't matter, but it others it may,
so I generally use the extended objects if there is one.

As for resources, I've only ever used Books Online (and Google, of course).
I usually work with SQLDMO in Python and VBScript, and it isn't too hard to
use VB code as an example.

Simon
Jul 23 '05 #12
Simon, one quick question. Does the Java programming language support
SQL-DMO? I was thinking that if I wanted to convert my c# program to
J2EE could it be done?

TFD

Jul 23 '05 #13
No idea. If you can access COM objects from Java, then I don't see why
not, but I don't know how you would do that.

In any case, I'm not sure what the point would be - anyone running
MSSQL must be running Windows, so there's not much benefit to a Java
version. Even if an organization uses Java on Unix/Linux for business
applications with an MSSQL backend, the MSSQL DBAs will still be using
Windows, and SQLDMO is most useful for them.

You might also consider that the SQL2005 version of SQLDMO - SMO - is a
..NET assembly, not a COM object, so .NET is the way to go if you intend
to support SQL2005 at some future point.

Simon

Jul 23 '05 #14
Good points, all of them.

As a side note I managed to finish my application entirely in C#. I
was hard not having any examples and not being a programmer. All the
VB examples out there helped quite and bit as well as your input.

Thanks Again, TFD.

Jul 23 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Peter | last post: by
6 posts views Thread by Fuzzydave | last post: by
14 posts views Thread by Developer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.