Do you know why the Jobs section caused it to fail? i am using sqlsvr2000.
I have a pretty good idea of what is wrong. It is either this:
((CONVERT(INT, CONVERT(VARBINARY(50), LEFT(CONVERT(VARCHAR(36), sj.job_id), 3))) * 100) + sjs.step_id) AS 'ObjectId'
although I don't really think so. It is more likely this:
sj.name + ' :: ' + sjs.step_name AS 'ObjectName'
The issue is that the ObjectName field in #Objects is defined as a SYSNAME datatype which is an alias for nvarchar(128). However, sysjobs.name and sysjobsteps.step_name are both SYSNAME and so they could potentially combine, if both are maxed out, to be a NVARCHAR(256). The JobSteps portion of this search is the only part that combines two strings to create an ObjectName so I guess I assumed (incorrectly) that the combination of those two would not likely overflow the 128 bytes of SYSNAME. So this is an easy fix: just edit the definition of #ObjectNames towards the top and define [ObjectName] as a NVARCHAR(260) instead of SYSNAME. I got the 260 from combining two SYSNAMEs (which are each NVARCHAR(128)) and adding 4 characters for the ' :: ' that separates those names in the concatenation shown above.
Lemme know if that works.
SQL# - http://www.SQLsharp.com/