Passing Job to a JobStep

Back in the day of COM and DMO. You could create a COM component, create an SQL job, and using an 'AcitveX Script' job step pass the Job to the component.

The component could then lookup the job schedule using DMO to figure out how long it should run.

Now in the days of SMO and CLR. I want to pass the Job to a CLR Stored Procedure as part of a Transact SQL step... (without hard coding the JobId in the script)

any help is appreciated...

rich

Well, I guess I'll have to do some extra work.

I will automate the job creation and pass put the JobId into the Trasact SQL.

The reason I need this is that the sproc is long running and manages it's own schedule. After it executes it determines the next runtime and updates the schedule on the job

rich

sql

Passing Information between SQL Agent Job Steps

Is it possible to passing information between SQL Agent Job Steps?
Such as the following job that has 2 tsql steps.
Step 1) Trans sql select * from myTable
Step 2) send result from Step 1 using CDO.
For the example you listed, one option is on the first step you can
have the results of this output to a file. You do this by selecting
the step, go to the advanced tab and then put in the path and file
name for the Output file.
Then on Step 2, you can send the file from step 1.
-Sue
On Mon, 11 Oct 2004 17:51:02 +0100, "Robin" <robin9876@.hotmail.com>
wrote:

>Is it possible to passing information between SQL Agent Job Steps?
>Such as the following job that has 2 tsql steps.
>Step 1) Trans sql select * from myTable
>Step 2) send result from Step 1 using CDO.
>
sql

Passing Information between SQL Agent Job Steps

Is it possible to passing information between SQL Agent Job Steps?
Such as the following job that has 2 tsql steps.
Step 1) Trans sql select * from myTable
Step 2) send result from Step 1 using CDO.
Perhaps you could use a global temp table (##).
Keith
"Robin" <robin9876@.hotmail.com> wrote in message
news:uWFWrJ7rEHA.1988@.TK2MSFTNGP09.phx.gbl...
> Is it possible to passing information between SQL Agent Job Steps?
> Such as the following job that has 2 tsql steps.
> Step 1) Trans sql select * from myTable
> Step 2) send result from Step 1 using CDO.
>
|||Besides Keith's answer, you have anther possibility. You can use DTS, store
results of a T-SQL task in a global variable and send mail using Sed Mail
task or ActiveX script with CDO.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Robin" <robin9876@.hotmail.com> wrote in message
news:uWFWrJ7rEHA.1988@.TK2MSFTNGP09.phx.gbl...
> Is it possible to passing information between SQL Agent Job Steps?
> Such as the following job that has 2 tsql steps.
> Step 1) Trans sql select * from myTable
> Step 2) send result from Step 1 using CDO.
>

Passing Arguments from an Alert to a SQL Server Job

I have an alert that will execute a job. Is there a way to pass parameters
from an alert to a sql job?
Thanks,
Ken
You can not pass parameters to a job, but you can insert a number of
different tokens in the text of the jobstep. These tokens will be replaced
with items like the database name, error message, current time etc when the
job is executed by the alert. This will work for any jobstep, T-SQL, CmdExec
etc. You can find the syntax in the topic for sp_add_jobstep in Books
Online.
Jacco Schalkwijk
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:CDF1CA50-8308-42EC-9F0B-7D5DC49F2307@.microsoft.com...
>I have an alert that will execute a job. Is there a way to pass parameters
> from an alert to a sql job?
> Thanks,
> Ken

Passing Arguments from an Alert to a SQL Server Job

I have an alert that will execute a job. Is there a way to pass parameters
from an alert to a sql job?
Thanks,
KenYou can not pass parameters to a job, but you can insert a number of
different tokens in the text of the jobstep. These tokens will be replaced
with items like the database name, error message, current time etc when the
job is executed by the alert. This will work for any jobstep, T-SQL, CmdExec
etc. You can find the syntax in the topic for sp_add_jobstep in Books
Online.
Jacco Schalkwijk
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:CDF1CA50-8308-42EC-9F0B-7D5DC49F2307@.microsoft.com...
>I have an alert that will execute a job. Is there a way to pass parameters
> from an alert to a sql job?
> Thanks,
> Ken

Passing Arguments from an Alert to a SQL Server Job

I have an alert that will execute a job. Is there a way to pass parameters
from an alert to a sql job?
Thanks,
KenYou can not pass parameters to a job, but you can insert a number of
different tokens in the text of the jobstep. These tokens will be replaced
with items like the database name, error message, current time etc when the
job is executed by the alert. This will work for any jobstep, T-SQL, CmdExec
etc. You can find the syntax in the topic for sp_add_jobstep in Books
Online.
--
Jacco Schalkwijk
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:CDF1CA50-8308-42EC-9F0B-7D5DC49F2307@.microsoft.com...
>I have an alert that will execute a job. Is there a way to pass parameters
> from an alert to a sql job?
> Thanks,
> Ken

Passing a variable to a job

Is it possible to pass a global variable to a SQL Server 2000 job? Or
is there another solution that I can use - I don't want to store what
I'm passing through as plain text.
Thanks,
SaulHi
Do you mean if you call a stored procedure you'd want to pass a paremeter to
this sp from the client?
As far as I know you cannot do that , however you may want to consider
using DTS Packages which do accepts parameters
<saulmarg@.gmail.com> wrote in message
news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> Is it possible to pass a global variable to a SQL Server 2000 job? Or
> is there another solution that I can use - I don't want to store what
> I'm passing through as plain text.
> Thanks,
> Saul
>|||Hi Uri,
I'm trying to get a VBA app to call a job to call a DTS package which
calls the BCP application to transfer data between two databases. The
BCP app needs a password to access the server.
Any suggestions?
Uri Dimant wrote:
> Hi
> Do you mean if you call a stored procedure you'd want to pass a paremeter to
> this sp from the client?
> As far as I know you cannot do that , however you may want to consider
> using DTS Packages which do accepts parameters
>
>
> <saulmarg@.gmail.com> wrote in message
> news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > is there another solution that I can use - I don't want to store what
> > I'm passing through as plain text.
> >
> > Thanks,
> > Saul
> >|||Why do you call a job that activates the DTS package? You can activate
the DTS package directly from the application. If you'll activate the
DTS, you'll be able to pass it parameters. If for some reason you have
to use job between the application and the DTS package, then you can
insert you parameter into a table before you run the job. In the
begining of the job you run a select statement on that table in get the
value that was inserted into the table.
Adi
saulmarg@.gmail.com wrote:
> Hi Uri,
> I'm trying to get a VBA app to call a job to call a DTS package which
> calls the BCP application to transfer data between two databases. The
> BCP app needs a password to access the server.
> Any suggestions?
>
> Uri Dimant wrote:
> > Hi
> >
> > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > this sp from the client?
> >
> > As far as I know you cannot do that , however you may want to consider
> > using DTS Packages which do accepts parameters
> >
> >
> >
> >
> > <saulmarg@.gmail.com> wrote in message
> > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > is there another solution that I can use - I don't want to store what
> > > I'm passing through as plain text.
> > >
> > > Thanks,
> > > Saul
> > >|||Thanks Adi. I use the job because the VBA app doesn't have features to
record history of jobs etc. The only problem with putting the
parameter into a table is that it's a password which I'd prefer not to
store anywhere. Is there any built in SQL functionality to encrypt and
decrypt?
Adi wrote:
> Why do you call a job that activates the DTS package? You can activate
> the DTS package directly from the application. If you'll activate the
> DTS, you'll be able to pass it parameters. If for some reason you have
> to use job between the application and the DTS package, then you can
> insert you parameter into a table before you run the job. In the
> begining of the job you run a select statement on that table in get the
> value that was inserted into the table.
> Adi
> saulmarg@.gmail.com wrote:
> > Hi Uri,
> >
> > I'm trying to get a VBA app to call a job to call a DTS package which
> > calls the BCP application to transfer data between two databases. The
> > BCP app needs a password to access the server.
> >
> > Any suggestions?
> >
> >
> > Uri Dimant wrote:
> > > Hi
> > >
> > > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > > this sp from the client?
> > >
> > > As far as I know you cannot do that , however you may want to consider
> > > using DTS Packages which do accepts parameters
> > >
> > >
> > >
> > >
> > > <saulmarg@.gmail.com> wrote in message
> > > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > > is there another solution that I can use - I don't want to store what
> > > > I'm passing through as plain text.
> > > >
> > > > Thanks,
> > > > Saul
> > > >|||Unfortunately there isn't a built in functionality to encrypt and
decrypt in SQL Server 2000. You can try and develop an extended stored
procedure that does it, but this seems to be a to big and to
complicated task to do:-). I'm sure that there are other companies
that developed such extended stored procedure, but since I didn't need
one I can't recommend a good product (and of course this will cost you
some money). Maybe you can still use the table. You can play around
with the table's permissions and not let anyone run select on this
table. You can let the user that you use in the connection string run
only insert on the table (and not even select) and in the job run the
select statement that will get the password (I assume that the SQL
agent is configured to run under the administrator's account, so the
job would be able to run the select statement).
Adi
saulmarg@.gmail.com wrote:
> Thanks Adi. I use the job because the VBA app doesn't have features to
> record history of jobs etc. The only problem with putting the
> parameter into a table is that it's a password which I'd prefer not to
> store anywhere. Is there any built in SQL functionality to encrypt and
> decrypt?
>
> Adi wrote:
> > Why do you call a job that activates the DTS package? You can activate
> > the DTS package directly from the application. If you'll activate the
> > DTS, you'll be able to pass it parameters. If for some reason you have
> > to use job between the application and the DTS package, then you can
> > insert you parameter into a table before you run the job. In the
> > begining of the job you run a select statement on that table in get the
> > value that was inserted into the table.
> >
> > Adi
> >
> > saulmarg@.gmail.com wrote:
> > > Hi Uri,
> > >
> > > I'm trying to get a VBA app to call a job to call a DTS package which
> > > calls the BCP application to transfer data between two databases. The
> > > BCP app needs a password to access the server.
> > >
> > > Any suggestions?
> > >
> > >
> > > Uri Dimant wrote:
> > > > Hi
> > > >
> > > > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > > > this sp from the client?
> > > >
> > > > As far as I know you cannot do that , however you may want to consider
> > > > using DTS Packages which do accepts parameters
> > > >
> > > >
> > > >
> > > >
> > > > <saulmarg@.gmail.com> wrote in message
> > > > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > > > is there another solution that I can use - I don't want to store what
> > > > > I'm passing through as plain text.
> > > > >
> > > > > Thanks,
> > > > > Saul
> > > > >

Passing a variable to a job

Is it possible to pass a global variable to a SQL Server 2000 job? Or
is there another solution that I can use - I don't want to store what
I'm passing through as plain text.
Thanks,
SaulHi
Do you mean if you call a stored procedure you'd want to pass a paremeter to
this sp from the client?
As far as I know you cannot do that , however you may want to consider
using DTS Packages which do accepts parameters
<saulmarg@.gmail.com> wrote in message
news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> Is it possible to pass a global variable to a SQL Server 2000 job? Or
> is there another solution that I can use - I don't want to store what
> I'm passing through as plain text.
> Thanks,
> Saul
>|||Hi Uri,
I'm trying to get a VBA app to call a job to call a DTS package which
calls the BCP application to transfer data between two databases. The
BCP app needs a password to access the server.
Any suggestions?
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> Do you mean if you call a stored procedure you'd want to pass a paremeter
to
> this sp from the client?
> As far as I know you cannot do that , however you may want to consider
> using DTS Packages which do accepts parameters
>
>
> <saulmarg@.gmail.com> wrote in message
> news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...|||Why do you call a job that activates the DTS package? You can activate
the DTS package directly from the application. If you'll activate the
DTS, you'll be able to pass it parameters. If for some reason you have
to use job between the application and the DTS package, then you can
insert you parameter into a table before you run the job. In the
begining of the job you run a select statement on that table in get the
value that was inserted into the table.
Adi
saulmarg@.gmail.com wrote:[vbcol=seagreen]
> Hi Uri,
> I'm trying to get a VBA app to call a job to call a DTS package which
> calls the BCP application to transfer data between two databases. The
> BCP app needs a password to access the server.
> Any suggestions?
>
> Uri Dimant wrote:|||Thanks Adi. I use the job because the VBA app doesn't have features to
record history of jobs etc. The only problem with putting the
parameter into a table is that it's a password which I'd prefer not to
store anywhere. Is there any built in SQL functionality to encrypt and
decrypt?
Adi wrote:[vbcol=seagreen]
> Why do you call a job that activates the DTS package? You can activate
> the DTS package directly from the application. If you'll activate the
> DTS, you'll be able to pass it parameters. If for some reason you have
> to use job between the application and the DTS package, then you can
> insert you parameter into a table before you run the job. In the
> begining of the job you run a select statement on that table in get the
> value that was inserted into the table.
> Adi
> saulmarg@.gmail.com wrote:|||Unfortunately there isn't a built in functionality to encrypt and
decrypt in SQL Server 2000. You can try and develop an extended stored
procedure that does it, but this seems to be a to big and to
complicated task to do:-). I'm sure that there are other companies
that developed such extended stored procedure, but since I didn't need
one I can't recommend a good product (and of course this will cost you
some money). Maybe you can still use the table. You can play around
with the table's permissions and not let anyone run select on this
table. You can let the user that you use in the connection string run
only insert on the table (and not even select) and in the job run the
select statement that will get the password (I assume that the SQL
agent is configured to run under the administrator's account, so the
job would be able to run the select statement).
Adi
saulmarg@.gmail.com wrote:[vbcol=seagreen]
> Thanks Adi. I use the job because the VBA app doesn't have features to
> record history of jobs etc. The only problem with putting the
> parameter into a table is that it's a password which I'd prefer not to
> store anywhere. Is there any built in SQL functionality to encrypt and
> decrypt?
>
> Adi wrote: