PropertyGrid and SMO objects

With Visual studio 2005 I've mapped my SMO objects and a new PropertyGrid control, everything works fine (a lot thanks to Microsoft VS 2005 team for this wonderfull and power control).

However I have two problems :

1. if I make some change in my PropertyGrid how to apply these changes to related object in my database ?

2. If my user dosn't have enough permission to change my DB objects how to turn PropertyGrid to ReadOnly mode ? ReadOnly property doesn't exist and set Enabled property to false doesn't work since the user cannot navigate through properties

Any help ?

Thank youYou'll need to add an Apply button (or some other control) on your form to give you an event for committing changes. For many SMO classes, you'll be able to commit changes by simply calling the Alter() method on the object that is being displayed in the PropertyGrid.

One way to get the read-only behavior you are looking for would be to create a wrapper class for the SMO class that only exposes property getters for the SMO class properties. If there is only a property getter, the PropertyGrid makes the corresponding cell read-only. So if the user has sufficient privileges, set the SelectedObject property to the SMO object directly, otherwise set the SelectedObject to your read-only wrapper around the SMO object.|||

better put button on propertygrid...

create your own pg and on New()

.......

Dim f As ToolStrip = Me.TOOLSTRIP

Dim n As New ToolStripButton

n.Text = "ADD"

AddHandler n.Click, AddressOf cADD

f.Items.Add(n)

Dim nD As New ToolStripButton

nD.Text = "Save"

AddHandler nD.Click, AddressOf cSave

f.Items.Add(nD)

PropertyGrid and SMO objects

With Visual studio 2005 I've mapped my SMO objects and a new PropertyGrid control, everything works fine (a lot thanks to Microsoft VS 2005 team for this wonderfull and power control).

However I have two problems :

1. if I make some change in my PropertyGrid how to apply these changes to related object in my database ?

2. If my user dosn't have enough permission to change my DB objects how to turn PropertyGrid to ReadOnly mode ? ReadOnly property doesn't exist and set Enabled property to false doesn't work since the user cannot navigate through properties

Any help ?

Thank you

You'll need to add an Apply button (or some other control) on your form to give you an event for committing changes. For many SMO classes, you'll be able to commit changes by simply calling the Alter() method on the object that is being displayed in the PropertyGrid.

One way to get the read-only behavior you are looking for would be to create a wrapper class for the SMO class that only exposes property getters for the SMO class properties. If there is only a property getter, the PropertyGrid makes the corresponding cell read-only. So if the user has sufficient privileges, set the SelectedObject property to the SMO object directly, otherwise set the SelectedObject to your read-only wrapper around the SMO object.|||

better put button on propertygrid...

create your own pg and on New()

.......

Dim f As ToolStrip = Me.TOOLSTRIP

Dim n As New ToolStripButton

n.Text = "ADD"

AddHandler n.Click, AddressOf cADD

f.Items.Add(n)

Dim nD As New ToolStripButton

nD.Text = "Save"

AddHandler nD.Click, AddressOf cSave

f.Items.Add(nD)

Programmertically create and execute stored procedure in SMO

Hi all,

I need to programmertically create and execute stored procedure in SMO, without registering it on the database. I also need to be able to load a file containing a stored procedure and execute it, using SMO.

Can someone show me how? A C# sample would be greatly appreciated.

Thanks in advance.

Hi,

a simple sample would be:

StoredProcedure sp = new StoredProcedure("SomeDatabase","usp_Somesp","SomeSchema");

sp.TextBody = "SELECT 'SomeData'";

Server s = new Server("SomeServer");

s.Databases["SomeDatabase"].StoredProcedures.Add(sp);

s.ConnectionContext.ExecuteNonQuery("usp_Somesp");

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

I appreciate you help, but this is the error I get:

Error 1 'Microsoft.SqlServer.Management.Smo.StoredProcedureCollection' does not contain a definition for 'Add'

|||

Good morning ( for me 9:34 )

Look at this link http://msdn2.microsoft.com/en-us/library/ms162553.aspx

The Add is automatic when you use the Create method if you use the constructor

sp = new StoredProcedure(DataBaseName,StoredProcedureName)

Excuse me for my english

Have a nice day

|||Thank you very much.

programmatically setting package variables in job step command line

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \\Package.Variables[User::period].Value;\"" + periodEndDate + "\"";

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

So managing a job in this way seems a bit of a pain. Why not let the package go and get the values from an external location when it is required.

One example would be to use a package configuration, perhaps using a SQL Server configuration. You could update the table values, and then just design the package to use that configuration value, assigning the values to the variables as required. Read up on package configurations if you are not familiar with them.

A variation on the theme it to do the work yourself. You could use any table, not just a configuration format table. Use an Execuite SQL Task to query for the values and using the result set option, you can return values and on the results page of the task, set the output to variable values.

|||

Yes it's been a learning curve in how to do what we're trying to do. The application is driven by a web page where the user says 'run this job, use these parameters'. However, you can't have one predefined job with multiple instances on the server, each with their own set of parameters - which needs to be possible because of the application requirements.

What we are doing now that seems to work is creating a new job, setting the type to ssis, setting the command line to specify the package and parameters, and then starting the job. It is also set to auto delete upon success.

The other way we thought of but decided against was to have the job pick up its runtime parameters from a queue - but then we'd have to create and manage the queue.

The 'create new job' approach lets us run now or set a schedule to run later, all the instances are visible as jobs on the server (based on category to filter out for the UI), and they clean up themselves if they run successfully.

NB: if anyone is curious, changing the command line of an existing job requires the Alter() method to persist the change back to the server, otherwise it just runs with the original command. like this:

string cmdLine = job.JobSteps[0].Command;
cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
job.JobSteps[0].Command = cmdLine;
job.JobSteps[0].Alter();
job.Start();

However, this permanently changes the command line in the job of course and you have to deal with that.

The code that that we're using to dynamically create the job and supply the parameters is pretty close to this:

string jobName = "the name to give to the new job";
string cmdLine = "the command line to run the package and set parameters";
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server svr = new Server(svrConnection);
JobServer agent = svr.JobServer;
if (agent.Jobs.Contains(jobName))
{
agent.Jobs[jobName].Drop();
}

job = new Job(agent, jobName);
job.DeleteLevel = CompletionAction.OnSuccess;
job.Category = "Calculate";
JobStep js = new JobStep(job, "Step 1");
js.SubSystem = AgentSubSystem.Ssis;
js.Command = cmdLine;
job.Create();
js.Create();
job.ApplyToTargetServer("(local)");
job.Alter();
job.Start();