creating a trace definition file (TDF) via code

Is there any way (either TSQL/SMO) to create a new TDF file, I'm using the traceserver InitializeAsReader sub, so need to pass it a reference to a TDF file, and I'd like to control what it traces via the TDF rather than parsing the TextData of the output.

Cathal

There is a Trace API that ships with SMO (see the Microsoft.SqlServer.Management.Trace namespace).

See the TraceServer.InitializeAsReader() method. You can specify a predefine trace template as the second parameter and then start the trace with this template.

|||

Thanks Michiel, but I'm actually using that method already, my question is on the creation of the TDF itself.

Ideally I'd like a t-sql/smo script to generate a TDF template, so I could dynamically determine what events to track and also filter for particular users/databases e.g. if I was only interested in tracking northwind events, I'd create a template based on TSQL_SPs, but that also used a DatabaseName column filter set to like '%Northwind%' . Obviously i can do this manually, but I would like to automate this step for an application I'm writing. I suspect it's not possible to serialize a trace definition to a TDF file. I can use TSQL to create a trace with the correct filters I require, but as I can't pass a running trace to InitializeAsReader, I can't use it. At present I've assumed I'll have to filter the eventData via code, but I thought I'd ask just in case.

Cathal

|||This is not possible, AFAIK. I'll make sure this gets logged as a feature request.

Creating a DECLARE variable with SMO

How can I create this stored proc with the SMO StoredProcedure class?

It doesn't want to work and I think it has something to do with the DECLARE statement. Anyone know how I can get this to work

Cheers
Jon

storedProc.TextBody = "DECLARE @.GroupID int"+

"SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")"+

"INSERT INTO gworkshop.Users"+

"(GroupID, Username, Password, Active, Deleted)"+

"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";

What does the rest of your code to create the procedure look like? Are you trying to create it and it's failing? If so, what's the error message?|||

I'm not in work right now so can't paste the code but basically its creating a server then the database name then in the storedprocedure I add parameters like this

StoredProcedure.Parameters.Add(new StoredProcedureParameter(storedprocedure,"@.Name", DataType.VarChar(30)));

The error is something like "There was an error creating stored procedure", then the procedure name

|||

If you are debugging, you can break on the exception it's throwing, then look at the inner exception(s) of that exception to find out exactly what the server is not liking about your stored procedure. Also, you can look at the script of the stored procedure ( use the script function), and look at the syntax it generates for you.

Whenever I create a stored procedure, I always do it like so:

Microsoft.SqlServer.Management.Smo.StoredProcedure proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure(database, procName);

proc.AnsiNullsStatus = true;

proc.QuotedIdentifierStatus = true;

proc.TextMode = false;

// Insert Params

Microsoft.SqlServer.Management.Smo.StoredProcedureParameter param = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(proc, "@." + paramName, paramDataType);

// If it's an output

param.IsOutputParameter = true;

proc.Parameters.Add(param);

// Now for the body

proc.TextMode = true;

proc.TextBody = "SELECT * FROM foo";

proc.Create();

|||

It seems that the problem was with the DECLARE @.GroupID

You need to add any parameters to the stored procedure, you can't add them in the SQL

storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Username", DataType.VarChar(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Password", DataType.Binary(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Active", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Deleted", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.GroupID", DataType.Int));

//storedProc.TextBody = "DECLARE @.GroupID int" +
storedProc.TextBody = "SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")" +
"INSERT INTO gworkshop.Users" +
"(GroupID, Username, Password, Active, Deleted)" +
"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";
storedProc.Create();

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.