I'm newbie. Script to generate scripts (add job steps).

Hi, I want to add to my 50 single step jobs a second step with command
"execute JOB_STEP_FAIL <jobname>" where <jobname> is job name from sysjobs
table.
I want to use sp_add_jobstep .
I do not want to make this job by hand.
Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!If you have a way to identify the 50 Jobs you want to add a step to, then us
e
a cursor, or table variable, or while loop, to run sp_add_jobstep for each
one...
Say the job names all start with 'DD'
Then
Select Job_id
From msdb..sysjobs
Where name like 'DD%'
Will give you list of all jobs
Then you can use a while loop
Declare @.JobName varChar(200) Set @.JobName = ''
While Exists (Select * From msdb..sysJobs
Where name like 'DD%'
And name > @.JobName)
Begin
Select @.JobName = Min(name )
From msdb..sysJobs
Where name like 'DD%'
And name > @.JobName
-- --
Exec sp_Add_Job_Step
@.job_name = @.JobName,
@.step_id = 2,
@.Step_Name = 'FailStep',
@.subsystem = 'TSQL',
@.command = 'execute JOB_STEP_FAIL ' + @.JobName
End
But if <jobName> in your post is just the same job that just failed, it
looks like you are trying to reattempt the same job if it gets to this step,
But there is both a ReAttempt , and a Fail method on each jobStep that are
more appropriate for this purpose... You should investigate those
"andrea favero" wrote:

> Hi, I want to add to my 50 single step jobs a second step with command
> "execute JOB_STEP_FAIL <jobname>" where <jobname> is job name from sysjob
s
> table.
> I want to use sp_add_jobstep .
> I do not want to make this job by hand.
> Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!|||Thanks a lot for your fast answer.
Thanks
"CBretana" wrote:
> If you have a way to identify the 50 Jobs you want to add a step to, then
use
> a cursor, or table variable, or while loop, to run sp_add_jobstep for each
> one...
> Say the job names all start with 'DD'
> Then
> Select Job_id
> From msdb..sysjobs
> Where name like 'DD%'
> Will give you list of all jobs
> Then you can use a while loop
> Declare @.JobName varChar(200) Set @.JobName = ''
> While Exists (Select * From msdb..sysJobs
> Where name like 'DD%'
> And name > @.JobName)
> Begin
> Select @.JobName = Min(name )
> From msdb..sysJobs
> Where name like 'DD%'
> And name > @.JobName
> -- --
> Exec sp_Add_Job_Step
> @.job_name = @.JobName,
> @.step_id = 2,
> @.Step_Name = 'FailStep',
> @.subsystem = 'TSQL',
> @.command = 'execute JOB_STEP_FAIL ' + @.JobName
> End
>
> But if <jobName> in your post is just the same job that just failed, it
> looks like you are trying to reattempt the same job if it gets to this ste
p,
> But there is both a ReAttempt , and a Fail method on each jobStep that are
> more appropriate for this purpose... You should investigate those
>
> "andrea favero" wrote:
>

IIS error on New Subscription

hi. i was following the newbie's guide: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true

on the creating a new subscription i was doing okay until i get to the last part wherein i click finish and this error pops up when it starts synchronizing data..

An error has occured on the computer running IIS. Try restarting the IIS server.

i already tried restarting the server.. is there anything else i can do to make it work?

Nijen,

There are many errors that can occur when synchronizing merge replication - the first thing you want to do is get as much information as possible and a specific error code. Place your call to repl.Synchronize() in a try/catch block and catch the specific exception that the SQL Mobile Client Agent throws during the replication. To traverse the SqlCe Exception object and extract the message information, use this method:

try{ repl.Synchronize(); }

catch (SqlCeException sqlex) { DisplaySQLCEErrors(sqlex);}

////////

public void DisplaySQLCEErrors(SqlCeException ex)
{
SqlCeErrorCollection errorCollection = ex.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = ex.InnerException;

foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

foreach (int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar ) bld.Append( "\n Num. Par. : " + numPar );
}

foreach ( string errPar in err.ErrorParameters )
{
if ( String.Empty != errPar ) bld.Append( "\n Err. Par. : " + errPar );
}

MessageBox.Show( bld.ToString(), "SQL Server CE Error" );
}
}

Once you have the specific error code, you can troubleshoot your replication.

http://msdn2.microsoft.com/en-us/library/ms171849(SQL.90).aspx.

-Darren

|||

Darren,

do you have coding for catch error in VBNet?

I've got An error has occured on the computer running IIS when I want to replicate my PDA to PC.. i'm using SQL Server 2000, VsNet 2003

|||

Here is the code in VB.NET:

Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = ex.Errors
Dim bld As New StringBuilder()
Dim inner As Exception = ex.InnerException
Dim err As SqlCeError

For Each err In errorCollection

bld.Append(ControlChars.Lf + " Error Code: " + err.HResult.ToString())
bld.Append(ControlChars.Lf + " Message : " + err.Message)
bld.Append(ControlChars.Lf + " Minor Err.: " + err.NativeError.ToString())
bld.Append(ControlChars.Lf + " Source : " + err.Source)

Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If (numPar <> 0) Then
bld.Append(ControlChars.Lf + " Num. Par. : " + numPar.ToString())
End If
Next numPar
Dim errPar As String
For Each errPar In err.ErrorParameters
If (errPar <> String.Empty) Then
bld.Append(ControlChars.Lf + " Err. Par. : " + errPar)
End If
Next errPar

MessageBox.Show(bld.ToString(), "SQL Mobile Error")

Next err

End Sub

-Darren

|||

Thanks Darren,

But why I've got error at line

Dim bld As New StringBuilder -- Type 'StringBuilder' is not defined.

|||

:D I've to Imports System.Text..

THANKSSSS!!!

IIS error on New Subscription

hi. i was following the newbie's guide: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true

on the creating a new subscription i was doing okay until i get to the last part wherein i click finish and this error pops up when it starts synchronizing data..

An error has occured on the computer running IIS. Try restarting the IIS server.

i already tried restarting the server.. is there anything else i can do to make it work?

Nijen,

There are many errors that can occur when synchronizing merge replication - the first thing you want to do is get as much information as possible and a specific error code. Place your call to repl.Synchronize() in a try/catch block and catch the specific exception that the SQL Mobile Client Agent throws during the replication. To traverse the SqlCe Exception object and extract the message information, use this method:

try{ repl.Synchronize(); }

catch (SqlCeException sqlex) { DisplaySQLCEErrors(sqlex);}

////////

public void DisplaySQLCEErrors(SqlCeException ex)
{
SqlCeErrorCollection errorCollection = ex.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = ex.InnerException;

foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

foreach (int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar ) bld.Append( "\n Num. Par. : " + numPar );
}

foreach ( string errPar in err.ErrorParameters )
{
if ( String.Empty != errPar ) bld.Append( "\n Err. Par. : " + errPar );
}

MessageBox.Show( bld.ToString(), "SQL Server CE Error" );
}
}

Once you have the specific error code, you can troubleshoot your replication.

http://msdn2.microsoft.com/en-us/library/ms171849(SQL.90).aspx.

-Darren

|||

Darren,

do you have coding for catch error in VBNet?

I've got An error has occured on the computer running IIS when I want to replicate my PDA to PC.. i'm using SQL Server 2000, VsNet 2003

|||

Here is the code in VB.NET:

Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = ex.Errors
Dim bld As New StringBuilder()
Dim inner As Exception = ex.InnerException
Dim err As SqlCeError

For Each err In errorCollection

bld.Append(ControlChars.Lf + " Error Code: " + err.HResult.ToString())
bld.Append(ControlChars.Lf + " Message : " + err.Message)
bld.Append(ControlChars.Lf + " Minor Err.: " + err.NativeError.ToString())
bld.Append(ControlChars.Lf + " Source : " + err.Source)

Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If (numPar <> 0) Then
bld.Append(ControlChars.Lf + " Num. Par. : " + numPar.ToString())
End If
Next numPar
Dim errPar As String
For Each errPar In err.ErrorParameters
If (errPar <> String.Empty) Then
bld.Append(ControlChars.Lf + " Err. Par. : " + errPar)
End If
Next errPar

MessageBox.Show(bld.ToString(), "SQL Mobile Error")

Next err

End Sub

-Darren

|||

Thanks Darren,

But why I've got error at line

Dim bld As New StringBuilder -- Type 'StringBuilder' is not defined.

|||

:D I've to Imports System.Text..

THANKSSSS!!!

IIF - Newbie

Hi,

just for understanding a simple iif statement:
I like to set a numeric value to 0, if it is smaller than 0.
This expression shall be valid over all dimensions for a specific measure

like Iif([Measures].[Provision Ankauf]<0,0,[Measures].[Provision Ankauf] )

it's not working, Pivottable returns ##VALUE everywheremake sure [Measures].[Provision Ankauf] is returning a number, and is not null.

also to make sure you're not crazy try: IIF(5<0,0,5) and see what happens|||IIF is not supported in SQL Server

use CASE instead|||As Rudy pointed out, you need to translate your VB (actually Jet) syntax to:CASE
WHEN [Measures].[Provision Ankauf] < 0 THEN 0
ELSE [Measures].[Provision Ankauf]
END-PatP|||Just to be ornery:

([Measures].[Provision Ankauf]+abs([Measures].[Provision Ankauf]))/2|||Just to be ornery:Ornery ?!?! That's deviant, low down, and high smellin' !!! I LIKE it!

In reality, the CASE statement is something that the original poster needs to know how to use since it opens all kinds of other possibilities that they'll need someday, but your bit of nifty math is both kinky and effective, so it is lots of fun the play with!

-PatP|||blindman, brilliant as usual!!|||Yeah, he needs to know CASE.

For some reason, possibly (probably) unjustified, I always feel like logic statements are less efficient than formula solutions. Just my bias, though I have to agree that the CASE statement is more readable. Without a comment, somebody looking at my solution wouldn't be able to immediately see its purpose.|||actually, now that i take a closer look, dajm probablu just needs IIF

i think he/she is using access, based on Pivottable and ##VALUE, and simply posted in the wrong forum

also knowing CASE isn't that bad an idea, though, eh