Edit a step from a job

Does the logon details you're using from the client have rights to change
the job details?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sorin Popescu" <spopescu@.nospam.com> wrote in message
news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> I can't edit a step (or create a new one) form an Agent job.
> I get General acces denied error.
> On details I see at program location
>
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
> I use a client station to acces the server
> On server works fine
> Any ideas ?
>How can I verify that please ?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>|||It's the owner of job
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>|||I can't edit a step (or create a new one) form an Agent job.
I get General acces denied error.
On details I see at program location
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
I use a client station to acces the server
On server works fine
Any ideas ?|||Does the logon details you're using from the client have rights to change
the job details?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sorin Popescu" <spopescu@.nospam.com> wrote in message
news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> I can't edit a step (or create a new one) form an Agent job.
> I get General acces denied error.
> On details I see at program location
>
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
> I use a client station to acces the server
> On server works fine
> Any ideas ?
>|||How can I verify that please ?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>|||It's the owner of job
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>

Edit a step from a job

I can't edit a step (or create a new one) form an Agent job.
I get General acces denied error.
On details I see at program location
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfos()
Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
I use a client station to acces the server
On server works fine
Any ideas ?Does the logon details you're using from the client have rights to change
the job details?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sorin Popescu" <spopescu@.nospam.com> wrote in message
news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> I can't edit a step (or create a new one) form an Agent job.
> I get General acces denied error.
> On details I see at program location
>
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
> I use a client station to acces the server
> On server works fine
> Any ideas ?
>|||How can I verify that please ?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>> I can't edit a step (or create a new one) form an Agent job.
>> I get General acces denied error.
>> On details I see at program location
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
> s()
>> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
>> I use a client station to acces the server
>> On server works fine
>> Any ideas ?
>>
>|||It's the owner of job
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>> I can't edit a step (or create a new one) form an Agent job.
>> I get General acces denied error.
>> On details I see at program location
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
> s()
>> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
>> I use a client station to acces the server
>> On server works fine
>> Any ideas ?
>>
>sql

Dynamically changing a backup file name

Hi all
I have read in many posts that DTS is not usually used for doing backups (a
scheduled job is preferred). However if I want to dynamically change the
name of the backup file depending on the date e.g. mydata_september.bak
during the month of september and then mydata_november.bak in november, how
would I go about doing that?
Dale1.Simple way is use the SQL Server Maintenance plan. It
automatically appends the Year,month,Day and time with the
database name.
Example. The backup file name for pubs database will be
pubs_db_200310132200.BAK.
2. If you want to use the datetime with your format, you
have to write a program.
Sample Script:
--
Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin
Select @.dbname=db_name()
Print @.dbname
Select @.CurrentDateTime = substring(DATENAME(month, getdate
()),1,3) +
cast(DATEPART(day, GETDATE()) as
varchar(2))+
cast(DATEPART(hh, GETDATE()) as
varchar(2)) +
cast(DATEPART(mi, GETDATE()) as
varchar(2))
Select @.dbbackupname = @.dbname+@.CurrentDateTime
select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
backup database @.dbname to disk=@.dbbackupname WITH
NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS =10, FORMAT
Print @.CurrentDateTime
End
Warning: Test the script before you use. Use at your own
risk.
- SQLVarad(MDCBA-1999,MCSE-1999)
>--Original Message--
>Hi all
>I have read in many posts that DTS is not usually used
for doing backups (a
>scheduled job is preferred). However if I want to
dynamically change the
>name of the backup file depending on the date e.g.
mydata_september.bak
>during the month of september and then
mydata_november.bak in november, how
>would I go about doing that?
>Dale
>
>.
>|||Thanks, will give it a try
Dale
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message
news:01f101c398d9$706888d0$a601280a@.phx.gbl...
> 1.Simple way is use the SQL Server Maintenance plan. It
> automatically appends the Year,month,Day and time with the
> database name.
> Example. The backup file name for pubs database will be
> pubs_db_200310132200.BAK.
> 2. If you want to use the datetime with your format, you
> have to write a program.
> Sample Script:
> --
> Declare
> @.CurrentDateTime varchar(20),
> @.dbname varchar(20),
> @.dbbackupname varchar(40)
> Begin
> Select @.dbname=db_name()
> Print @.dbname
> Select @.CurrentDateTime = substring(DATENAME(month, getdate
> ()),1,3) +
> cast(DATEPART(day, GETDATE()) as
> varchar(2))+
> cast(DATEPART(hh, GETDATE()) as
> varchar(2)) +
> cast(DATEPART(mi, GETDATE()) as
> varchar(2))
> Select @.dbbackupname = @.dbname+@.CurrentDateTime
> select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
> backup database @.dbname to disk=@.dbbackupname WITH
> NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS => 10, FORMAT
> Print @.CurrentDateTime
> End
> Warning: Test the script before you use. Use at your own
> risk.
> - SQLVarad(MDCBA-1999,MCSE-1999)
> >--Original Message--
> >Hi all
> >
> >I have read in many posts that DTS is not usually used
> for doing backups (a
> >scheduled job is preferred). However if I want to
> dynamically change the
> >name of the backup file depending on the date e.g.
> mydata_september.bak
> >during the month of september and then
> mydata_november.bak in november, how
> >would I go about doing that?
> >
> >Dale
> >
> >
> >.
> >

Dynamic SQL Job

I am trying to create a sql job that has multiple steps, emails out the error on a failure, and will continue to the next step. We can not put Outlook on our servers so we can not use SQL Operators.

Basically I would like every step in the job to jump to a failure step that emails out an error message and returns to the step after the step that failed. I could have a failure step for each actual step, but this job has a number of steps and being the anal programmer I am, I don't want to have multiple steps that do the same thing.

I have figured out how to Dynamically update the current sql Job by using sp_update_jobstep and job tokens [STEPID] and [JOBID]. For example the following code will update the job so it will quit job with failure if the step finishes successfully.

declare @.tmpJobID as uniqueidentifier
declare @.tmpStepID as integer

set @.tmpJobID = (convert(uniqueidentifier, [JOBID]))
set @.tmpStepID = [STEPID]

exec sp_update_jobstep @.job_id=@.tmpJobID, @.step_id=@.tmpStepID, @.on_success_action=2

The problem is that the job is not updated until it is completed. So, the changes will not take affect until the next time it is ran. Is there a way to reload the job into memory, or force the job to use the updates?

I have also tried directly updating the sysjobsteps table but that didn't work either.

Thanks much.Well, does anyone know how to get a job to dynamically jump to a specified step?

Thanks much|||Built-in step dependency has limited complexity capabilities. You'll probably haveto merge the steps and...code away!