Using anthony.green's sample data table (note that posting sample data in an easily digestible format is always a big help for the people trying to help you):

; with cte as...

Because you know that you have a maximum of 20 values per ComId, you could do this without a dynamic pivot by pre-numbering the data with a row_number:

-- create a...

Ah, problem solved. Adding the new subscription is only half the job. Manually running the creation of the partition and snapshot agent job seems to do the trick:

-- create the...

Which is fair enough 🙂

Thanks for posting, I'll see if I can't do some research myself and update this thread at a later point.

Interesting. My curiosity is piqued.

The workaround is to pre-parse the column into a temp table, including the relevant keys for joins, then index, then join to this instead. This works...

Hi Grant,

The join is straight varchar to a substring of a varchar column (apologies for not being explicit about this), that is zero padded inline via replicate. There is a...

Hi John,

No restart and as far as I can tell, the agent service was running at the time as I see no termination event in the agent error log.

Thanks, Iain

Edit:...

Something like this should help:

create table #notes (

NotesId int

, NoteText varchar(100)

)

insert #notes

values (1,'This') ...

Interesting. Never seen spt_values used like that before.

Figured I'd give it a side by side comparison, hope you don't mind 🙂

My revised version (with a few errors revised out...

Unlikely if this is the key field being used to determine which is...

Hi Sunny,

This will return a list of periods plus the total (Ken, your query just returns a total). It is broken into very discrete sections at the moment, primarily to...

This is likely to be more efficient as the solution given creates a "triangular join", search this site for an explanation...

;with cte as (

select <Fields>

, row_number() over (order by <KeyField>)...

This looks like fun, am I too late? 🙂

I figured this looked like a classic example of a situation where a function might be useful. I'd prefer to farm this...

Try looking into sp_addjob:

http://msdn.microsoft.com/en-us/library/ms182079.aspx

and sp_addjobstep:

http://msdn.microsoft.com/en-us/library/ms187358.aspx

Regards, Iain

Something like this should do the trick:

select sum(case when ScaleDesc = 'Poor' then 1 else 0 end) / count(*) as Poor

, sum(case when ScaleDesc = 'Good'...