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.Hi
Have you run profiler to see what it is doing at the database end?
John
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1111709868.033611.319870@.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.|||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|||LineVoltageHalogen (tropicalfruitdrops@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am connecting as "sa" who is the owner of the database.|||"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1111709868.033611.319870@.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()|||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|||"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1111782756.223772.282810@.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|||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|||Simon, why did you switch to SQLDMO.SQLSERVER2 and two for the other
object types?
TFD|||Simon, you ROCK! That worked! I am extremely gratefull to you.
TFD|||"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1111788757.101232.212850@.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|||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|||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|||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.
No comments:
Post a Comment