How to get the value of System::TaskName of a higher up level?

Hi there,

I was wondering how can I get the value of System::TaskName of a higher scope when I have a Master Package that have several sequence task, data flow tasks and execute package tasks. For each task inside this Master Package on the Post-Execute event handler I have a script task that logs the execution of each task.

After running this master package I saw in my db that I have a row for every single tasks executed in the process and not only the tasks that exist in the master package. For instance, for simplicity let's say my master package looks like this:

SQL Task Script
|
Exec Package 2
|
Exec Package 3
|
Exec Package 4

Package 2,3 and 4 have a SQL Task Script with name Execute SQL Task Package 2, Execute SQL Task Package 3, Execute SQL Task Package 4.

Here's what I got in my db:

Execute SQL Task
Execute SQL Task Package 2
Package2
Execute Package 2
Execute SQL Task Package 3
Package3
Execute Package 3
Execute SQL Task Package 4
Package4
Execute Package 4

I see two TaskName variables in the Variable window, one with OnPostExec scope and the other with Execute Package 2 for instance. I want to get the value of System::TaskName with Execute Package 2 scope.

I want to see in my db only the tasks in bold. Any ideas of how can I do this? I hope you understand what I'm trying to achieve.

Thanks!

Each eventhandler has a variable called @.[System:ropogate] which should be all that you need.

System Variables

(http://msdn2.microsoft.com/en-us/library/ms141788.aspx)

I could tell you how to use it but you'll probably learn more by fiddling with it yourself. its fairly self-explanatory.

-Jamie

|||Hi Jamie,

I knew about the Propagate variable and I tried setting it to false for every post-execute event handler in my master package. I still got multiple records in my table.

Then I tried with False at the package level on every subpackage my master package is calling but I got the same results.

Finally, I remove all my post-execute event handlers from my master package and leave just one, at the master package level and set the Propagate to false and I still got all the subtasks logged into my table.

Could you tell me how to use this variable?

Thanks!|||

You need to set it on the eventhandlers that are scoped to the tasks for which you don't want to do any logging. So, if you have a task called "Ricardo's Execute SQL Task" in a child package that you don't want to capture information for, set that task's eventhandlers to ahve System:ropogate=FALSE.

-Jamie

|||Hi Jamie,

Well, I guess I'm doing something wrong because I set to False the Propagate variable on the Execute SQL Task of my first child package and also at the package level. So now, the Post-Execute event handler for these two objects have nothing but I changed the value of Propagate from True to False.

In my parent package, I disabled all the post-execute event handlers except for the Execute Package Task that calls Package 2.

I got 3 rows inserted in my table:

Taskname = Script Task Post-Exec Package 2 - SourceName = Execute SQL Task Package 2
Taskname = Script Task Post-Exec Package 2 - SourceName = Package2
Taskname = Script Task Post-Exec Package 2 - SourceName = Execute Package 2

the values from above are from System::TaskName and System:ourceName

Thanks!

How to get the Task name inside your custom task

I have a custom task, and during the Execute method I'd like to get hold of the task's name. It appears that this is held on the TaskHost, but I cannot see how to obtain that from within the task itself. Anyone got any ideas?

Thanks

DarrenSystem::TaskName|||or better still, System::SourceName |||Kirk

really?

You go out to the variables collection to get your own name?

Could you explain the reasoning?
Allan|||Hello KirkHaselden@.discussions.microsoft.com, Thanks Kirk. I saw that the property only appears on the TaskHost but thought it a little long winded to get your own name. Thanks Allan
> Well, yes. Since Name is on the TaskHost, it's not available anywhere
> else.
> K|||Well, yes. Since Name is on the TaskHost, it's not available anywhere else.
K|||Jamie.

I think SourceName is only added to the Variables list if your package has Event Handlers.
Allan

How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)

|||SELECT * FROM TABLE WHERE CONDITION ORDER BY DESC FIELDNAME LIMIT 1,1sql

How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)

How to Get the Output Column in OLE DB Command Transformation

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @.OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = ?

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

|||

It sounds as tho you are using the wrong component. To source stuff use the OLE DB Source Adapter, not the OLE DB Command.

-Jamie

|||

Dear Jamie,

Thanks for such a quick reply.

U Mean OLEDB Source From DataFlow Sources.

Actually the My dataflow task contains one OLEDB source component which is having connection to one table, from that table i am getting the orderID column, Then i am passing this OrderID column values to the query Which will get the serialnumber in the SerialNumbers table based on this OrderID. And my problem is i cant able to get this selected serialnumber column in the output column tree view,so i that column is not accessable for futher transformations.

Please give me some solution.

Thanks in advance.

- Dhivya

|||

You need the LOOKUP transform. That s exactly what it does.

-Jamie

|||

Dear Jamie,

That also i tried,the table contains multiple values(for same OrderID multiple serial numbers) and the lookup transform will take only the first value and map the same to the others.

-Dhivya

|||

So its a many-to-many?

Then you should use the MERGE JOIN component!

-Jamie

|||

Good advice, Jamie.

Dhivya, remember that the Merge Join needs a sorted input, so you'll also need to use sort components. Alternatively, use ORDER BY in the source queries, and set the IsSorted property of the source adapter output to True.

Donald

|||

I tried merge join, the problem is order ID is not unique in my source table and in transaction table. so if i put some inner or left joins i am not getting the values what i want.

-Dhivya

|||

Merge Join worked for me. I did right outer join.

Thank u Jamie and donald.

But still my question is, we cant get the output columns in OLE DB Command Component if we use select command?

|||

No. That's not what its for!

-Jamie

|||

OK. Thanks a lot

-Dhivya

how to get the all the column names of a table?

Can't figure out...

Given a database table name, how do I get all the column names of the table? Actually my task is more than that, what I want is the percentage of null values for each column. But there too many columns in the table and I don't want to type the column name one by one. How to achieve it?

For example, suppose I have a table TB which contains 50 columns C1 to C50. What I want to produce a stored-procedure that table the table name as input parameter and output the percentage of null values for each column C1 to C50, something like:

ColumnName NullPercentage
C1 14.28%

....
C50 6.89%

Thank you!

You can get the column names from system table SYSCOLUMNS.

You can use them to construct a dynamic SQL statement, and then use EXEC(@.string_variable) to run the query.

If you need further assistance, I can try to hunt up some code where I did this sort of thing to estimate the space consumed by a table. (Now with SQL Server 2005 you can find such space information, both for the table and its indexes, with "Properties" after right-clicking on the table name in SSMS.)

HTH.

Dan

|||I am fairy new to sql. If you could have some sample script that would be great! Thanks.
|||

I'll see if I can post the code tomorrow, Friday. Perhaps others here may beat me to it.

Dan

|||

Yes.. Here you go...

Hey Dan I never thought to beat you Buddy ..

Code Snippet

Declare @.Cols as Varchar(8000);

Declare @.PreparedCols as Varchar(8000);

Declare @.TableName as NVarchar(1000);

Select @.TableName = 'Orders'

Select

@.PreparedCols = ',Cast(Sum(Case When ? is null Then 1 Else 0 End)/Sum(1.0) * 100 as Numeric(5,2)) as [?]',

@.Cols = ''

Select

@.Cols = @.Cols + replace(@.PreparedCols, '?', name)

From

syscolumns

Where

id=Object_Id(@.TableName)

Exec ('Select ''NULL Value %'' as Comment' + @.Cols + ' From ' + @.TableName)

|||

Manivannen,

Not only did you beat me, but you provided a much-more compact solution!

I have never before built a dynamic SQL statement using a SELECT to append to an ever-growing SQL statement.

I like it!

Thanks for the education!

Where I tried it, though, I would like to note that I had to change @.COL to VARCHAR(MAX); it must have changed @.COL to NVARCHAR, since I could only print 4000 characters of it. It worked fine when I changed to VARCHAR(MAX). (Without the change the limit is around 40 columns, depending on the lengths of the column names.)

Dan

P.S. Is that enough exclamation points?

|||Beautiful!! Save me tons of time to check nulls.

I had to change to NVarchar(MAX) too, otherwise some of the tables will not work.

I marked both posts as answer.

Thanks!
|||

Thanks for your question, too!

I learn a lot by reading answers in this Forum.

Dan

How to get status for every individual Task in a package

Hi,

I have a package in which there are four tasks. I have to get the status of those four tasks and finally send a mail. I am unable to get the status for individual task.

Has anyone tried this? Please help.

Thanks in advance.

Hi,

Create 'OnError' and 'OnPostExecute' event handlers for each task. In the 'OnError' event, set the error message to a variable. In the 'OnPostExecute' event, check the variable. If is it empty set the status as 'Success' or else set it as 'Failure' with the error message. In that event itself, u write the status to a file in append mode (first task should open the file in overwrite mode).

Hope it would help.

How to get source by execution oracle sp using ref cursor

Hi,

I need to get recordset returned by oracle sp in execute sql task to process futher in For Each Loop container and on same lines i want to use oracle sp for extraction data in Data Flow Task. Could anybody suggest if it how we could do it in SSIS?

All suggestions will be highly appreciated.

Thanks,

Lalit

You should be able to do this. Is there a specific problem you are encountering?|||There is no variable type in SSIS that maps to refcursor type in Oracle. I don't think a variable of type object can be used for this either. So I guess you cannot execute Oracle SP using Execute SQL task to get the recordset.

How to get DMO to schedule a Stored Procedure as a Job?

I have what is a simple task. Using C# I want to schedule a SQL Server
Stored Procedure to run at a specified date & time.
I've seen plenty of examples of running SQL code like this but none about
running a stored procedure. The closest I've come is this:
http://msdn.microsoft.com/library/d... />
j_9uwk.asp
Can anyone point me to an example that would help me accomplish what I'm
trying to do?
Robert W.
Vancouver, BC
www.mwtech.comHi,
Below is an example I knocked together of calling SQLDMO from C#. It's
by no means perfect but it should be enough to get you started. You'll
probably need to clean up some formatting as the action of posting
probably ruined all of it. Just a few things to note:
1) You will need to reference the appropriate DLL for SQLDMO in your C#
project
2) You can add a "using SQLDMO;" statement to your code if you don't
want to prefix SQLDMO objects with "SQLDMO".
Hope this helps a bit
========================================
=======
public static void createNewJob()
{
/* Variable declaration */
SQLDMO.SQLServer theServer; //Object representing the connection to a
DB server
SQLDMO.Job jobToBeCreated; //Object representing the job to be created
on the server
SQLDMO.JobSchedule scheduleForCreatedJob; //Schedule of job to create
SQLDMO.JobStep stepForCreatedJob; //What the job will do
/*
* Process done in this order:
*
* 1) Connect to the database server
* 2) Create a Job object representing the job we want to add
* 3) Create a JobStep object representing what we want this job to do
(e.g. execute some T-SQL)
* 4) Create a JobSchedule object representing when we want this job
executed
* 5) Add the JobStep and JobSchedule objects to the Job object
* 6) Add the Job object to the jobs currently defined on the server
*
* NOTE: We need to connect to the server first because some methods
used to define a job require that
* we be connected to a database server
*/
theServer = new SQLDMO.SQLServerClass();
theServer.LoginSecure = false; //Use SQL Server Authentication
theServer.Login = "sa"; //Change to whatever SQL Server login you want
theServer.Password = ""; //Change to the password associated with the
user set previously
theServer.Connect("N-0U9LWEXW762ZV\\NATHAN", theServer.Login,
theServer.Password);
jobToBeCreated = new SQLDMO.JobClass();
jobToBeCreated.Name = "Example Job";
theServer.JobServer.Jobs.Add(jobToBeCreated);
stepForCreatedJob = new SQLDMO.JobStepClass();
stepForCreatedJob.Name = "Stored procedure execution";
stepForCreatedJob.StepID = 1;
stepForCreatedJob.Command = "exec sp_monitor"; //Change stored proc as
appropriate
scheduleForCreatedJob = new SQLDMO.JobScheduleClass();
scheduleForCreatedJob.Name = "Example schedule";
scheduleForCreatedJob.Enabled = true;
jobToBeCreated.JobSchedules.Add(scheduleForCreatedJob);
/*
* Configure a "one-shot" (i.e. the job will execute once {and only
once})
*/
scheduleForCreatedJob.Schedule.ActiveStartDate = 20060513; //Equivalent
to 05/13/2006 in dd/mm/yyyy
scheduleForCreatedJob.Schedule.ActiveStartTimeOfDay = 233000;
//Equivalent to hh:mm:ss
scheduleForCreatedJob.Schedule.ActiveEndDate = 20070513; //Value
irrelevant as the job is "one-shot"
scheduleForCreatedJob.Schedule.ActiveEndTimeOfDay = 230000; //Value
irrelevant as the job is "one-shot"
scheduleForCreatedJob.Schedule.FrequencyType =
SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_OneTime; //One shot
jobToBeCreated.JobSteps.Add(stepForCreatedJob);
jobToBeCreated.StartStepID = 1; //Step ID for the step we defined
previously
jobToBeCreated.ApplyToTargetServer("(local)"); //Job runs on server
running the SQL Server Agent
theServer.DisConnect();
}|||Nate,
Thank you so much! I will review your code and see if I can adapt it my
specific needs!!
Robert W.
Vancouver, BC
www.mwtech.com
"nate.vu@.gmail.com" wrote:

> Hi,
> Below is an example I knocked together of calling SQLDMO from C#. It's
> by no means perfect but it should be enough to get you started. You'll
> probably need to clean up some formatting as the action of posting
> probably ruined all of it. Just a few things to note:
> 1) You will need to reference the appropriate DLL for SQLDMO in your C#
> project
> 2) You can add a "using SQLDMO;" statement to your code if you don't
> want to prefix SQLDMO objects with "SQLDMO".
> Hope this helps a bit
> ========================================
=======
> public static void createNewJob()
> {
> /* Variable declaration */
> SQLDMO.SQLServer theServer; //Object representing the connection to a
> DB server
> SQLDMO.Job jobToBeCreated; //Object representing the job to be created
> on the server
> SQLDMO.JobSchedule scheduleForCreatedJob; //Schedule of job to create
> SQLDMO.JobStep stepForCreatedJob; //What the job will do
> /*
> * Process done in this order:
> *
> * 1) Connect to the database server
> * 2) Create a Job object representing the job we want to add
> * 3) Create a JobStep object representing what we want this job to do
> (e.g. execute some T-SQL)
> * 4) Create a JobSchedule object representing when we want this job
> executed
> * 5) Add the JobStep and JobSchedule objects to the Job object
> * 6) Add the Job object to the jobs currently defined on the server
> *
> * NOTE: We need to connect to the server first because some methods
> used to define a job require that
> * we be connected to a database server
> */
> theServer = new SQLDMO.SQLServerClass();
> theServer.LoginSecure = false; //Use SQL Server Authentication
> theServer.Login = "sa"; //Change to whatever SQL Server login you want
> theServer.Password = ""; //Change to the password associated with the
> user set previously
> theServer.Connect("N-0U9LWEXW762ZV\\NATHAN", theServer.Login,
> theServer.Password);
> jobToBeCreated = new SQLDMO.JobClass();
> jobToBeCreated.Name = "Example Job";
> theServer.JobServer.Jobs.Add(jobToBeCreated);
> stepForCreatedJob = new SQLDMO.JobStepClass();
> stepForCreatedJob.Name = "Stored procedure execution";
> stepForCreatedJob.StepID = 1;
> stepForCreatedJob.Command = "exec sp_monitor"; //Change stored proc as
> appropriate
> scheduleForCreatedJob = new SQLDMO.JobScheduleClass();
> scheduleForCreatedJob.Name = "Example schedule";
> scheduleForCreatedJob.Enabled = true;
> jobToBeCreated.JobSchedules.Add(scheduleForCreatedJob);
> /*
> * Configure a "one-shot" (i.e. the job will execute once {and only
> once})
> */
> scheduleForCreatedJob.Schedule.ActiveStartDate = 20060513; //Equivalent
> to 05/13/2006 in dd/mm/yyyy
> scheduleForCreatedJob.Schedule.ActiveStartTimeOfDay = 233000;
> //Equivalent to hh:mm:ss
> scheduleForCreatedJob.Schedule.ActiveEndDate = 20070513; //Value
> irrelevant as the job is "one-shot"
> scheduleForCreatedJob.Schedule.ActiveEndTimeOfDay = 230000; //Value
> irrelevant as the job is "one-shot"
> scheduleForCreatedJob.Schedule.FrequencyType =
> SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_OneTime; //One shot
> jobToBeCreated.JobSteps.Add(stepForCreatedJob);
> jobToBeCreated.StartStepID = 1; //Step ID for the step we defined
> previously
> jobToBeCreated.ApplyToTargetServer("(local)"); //Job runs on server
> running the SQL Server Agent
> theServer.DisConnect();
> }
>