Exception

I start a job (the job successfully executes) but I notice in a trace that an
Exception occurs when the job starts. This is the output for the SQL:
BatchCompleted. Any idea why this would throw an exception? It appears as
though it is all SQL Server generated code.
create table #tmp_sp_help_category
(category_id int null, category_type tinyint null, name nvarchar(128) null)
insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
SELECT
sv.name AS [Name],
CAST(sv.enabled AS bit) AS [IsEnabled],
tshc.name AS [Category],
null AS [CurrentRunStatus],
null AS [CurrentRunStep],
null AS [HasSchedule],
null AS [HasStep],
null AS [HasServer],
null AS [LastRunDate],
null AS [NextRunDate],
null AS [LastRunOutcome],
CAST(sv.job_id AS nvarchar(100)) AS [job_id]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_id
WHERE
(sv.name=N'Trace Duration Job 1')
drop table #tmp_sp_help_category
--
Message posted via http://www.sqlmonster.comHi
You may want to look at batch/statement starting to find what is causing
this rather than what has completed.
John
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5eeb1211a50ab@.uwe...
>I start a job (the job successfully executes) but I notice in a trace that
>an
> Exception occurs when the job starts. This is the output for the SQL:
> BatchCompleted. Any idea why this would throw an exception? It appears as
> though it is all SQL Server generated code.
>
> create table #tmp_sp_help_category
> (category_id int null, category_type tinyint null, name nvarchar(128)
> null)
> insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
> SELECT
> sv.name AS [Name],
> CAST(sv.enabled AS bit) AS [IsEnabled],
> tshc.name AS [Category],
> null AS [CurrentRunStatus],
> null AS [CurrentRunStep],
> null AS [HasSchedule],
> null AS [HasStep],
> null AS [HasServer],
> null AS [LastRunDate],
> null AS [NextRunDate],
> null AS [LastRunOutcome],
> CAST(sv.job_id AS nvarchar(100)) AS [job_id]
> FROM
> msdb.dbo.sysjobs_view AS sv
> INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id => tshc.category_id
> WHERE
> (sv.name=N'Trace Duration Job 1')
> drop table #tmp_sp_help_category
> --
> Message posted via http://www.sqlmonster.com

Exception

I start a job (the job successfully executes) but I notice in a trace that a
n
Exception occurs when the job starts. This is the output for the SQL:
BatchCompleted. Any idea why this would throw an exception? It appears as
though it is all SQL Server generated code.
create table #tmp_sp_help_category
(category_id int null, category_type tinyint null, name nvarchar(128) null)
insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
SELECT
sv.name AS [Name],
CAST(sv.enabled AS bit) AS [IsEnabled],
tshc.name AS [Category],
null AS [CurrentRunStatus],
null AS [CurrentRunStep],
null AS [HasSchedule],
null AS [HasStep],
null AS [HasServer],
null AS [LastRunDate],
null AS [NextRunDate],
null AS [LastRunOutcome],
CAST(sv.job_id AS nvarchar(100)) AS [job_id]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_i
d
WHERE
(sv.name=N'Trace Duration Job 1')
drop table #tmp_sp_help_category
Message posted via http://www.droptable.comHi
You may want to look at batch/statement starting to find what is causing
this rather than what has completed.
John
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5eeb1211a50ab@.uwe...
>I start a job (the job successfully executes) but I notice in a trace that
>an
> Exception occurs when the job starts. This is the output for the SQL:
> BatchCompleted. Any idea why this would throw an exception? It appears as
> though it is all SQL Server generated code.
>
> create table #tmp_sp_help_category
> (category_id int null, category_type tinyint null, name nvarchar(128)
> null)
> insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
> SELECT
> sv.name AS [Name],
> CAST(sv.enabled AS bit) AS [IsEnabled],
> tshc.name AS [Category],
> null AS [CurrentRunStatus],
> null AS [CurrentRunStep],
> null AS [HasSchedule],
> null AS [HasStep],
> null AS [HasServer],
> null AS [LastRunDate],
> null AS [NextRunDate],
> null AS [LastRunOutcome],
> CAST(sv.job_id AS nvarchar(100)) AS [job_id]
> FROM
> msdb.dbo.sysjobs_view AS sv
> INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id =
> tshc.category_id
> WHERE
> (sv.name=N'Trace Duration Job 1')
> drop table #tmp_sp_help_category
> --
> Message posted via http://www.droptable.com

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris