Hi
I have a simple statement that I can run on every server to check jobs schedule and last run. It runs fine on every server, bar one. Any ide why?
I get this error:
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'With'.
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'For'.
Msg 156, Level 15, State 1, Line 114
Incorrect syntax near the keyword 'end'.
As I said it runs fine on every other server.
Thanks
G
Sure would be helpful if we could see the code ?
BEGIN
Declare @weekDay Table (
mask int
, maskValue varchar(32)
);
Insert Into @weekDay
Select 1, 'Sunday' Union All
Select 2, 'Monday' Union All
Select 4, 'Tuesday' Union All
Select 8, 'Wednesday' Union All
Select 16, 'Thursday' Union All
Select 32, 'Friday' Union All
Select 64, 'Saturday';
With myCTE
As(
Select sched.name As 'scheduleName'
, sched.schedule_id
, jobsched.job_id
, Case When sched.freq_type = 1 Then 'Once'
When sched.freq_type = 4
And sched.freq_interval = 1
Then 'Daily'
When sched.freq_type = 4
Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
When sched.freq_type = 8 Then
Replace( Replace( Replace((
Select maskValue
From @weekDay As x
Where sched.freq_interval & x.mask <> 0
Order By mask For XML Raw)
, '"/><row maskValue="', ', '), '', '')
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1
Then '; weekly'
When sched.freq_recurrence_factor <> 0 Then '; every '
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' End
When sched.freq_type = 16 Then 'On day '
+ Cast(sched.freq_interval As varchar(10)) + ' of every '
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
When sched.freq_type = 32 Then
Case When sched.freq_relative_interval = 1 Then 'First'
When sched.freq_relative_interval = 2 Then 'Second'
When sched.freq_relative_interval = 4 Then 'Third'
When sched.freq_relative_interval = 8 Then 'Fourth'
When sched.freq_relative_interval = 16 Then 'Last'
End +
Case When sched.freq_interval = 1 Then ' Sunday'
When sched.freq_interval = 2 Then ' Monday'
When sched.freq_interval = 3 Then ' Tuesday'
When sched.freq_interval = 4 Then ' Wednesday'
When sched.freq_interval = 5 Then ' Thursday'
When sched.freq_interval = 6 Then ' Friday'
When sched.freq_interval = 7 Then ' Saturday'
When sched.freq_interval = 8 Then ' Day'
When sched.freq_interval = 9 Then ' Weekday'
When sched.freq_interval = 10 Then ' Weekend'
End
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1 Then '; monthly'
When sched.freq_recurrence_factor <> 0 Then '; every '
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' End
When sched.freq_type = 64 Then 'StartUp'
When sched.freq_type = 128 Then 'Idle'
End As 'frequency'
, IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) +
Case When sched.freq_subday_type = 2 Then ' seconds'
When sched.freq_subday_type = 4 Then ' minutes'
When sched.freq_subday_type = 8 Then ' hours'
End, 'Once') As 'subFrequency'
, Replicate('0', 6 - Len(sched.active_start_time))
+ Cast(sched.active_start_time As varchar(6)) As 'startTime'
, Replicate('0', 6 - Len(sched.active_end_time))
+ Cast(sched.active_end_time As varchar(6)) As 'endTime'
, Replicate('0', 6 - Len(jobsched.next_run_time))
+ Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
, Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
From msdb.dbo.sysschedules As sched
Join msdb.dbo.sysjobschedules As jobsched
On sched.schedule_id = jobsched.schedule_id
Where sched.enabled = 1
)
Select Distinct job.name As 'Job Name'
, sched.scheduleName 'Schedule Name'
, sched.frequency 'Frequency'
, sched.subFrequency 'Sub Frequency'
, SubString(sched.startTime, 1, 2) + ':'
+ SubString(sched.startTime, 3, 2) + ' - '
+ SubString(sched.endTime, 1, 2) + ':'
+ SubString(sched.endTime, 3, 2)
As 'Schedule Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/'
+ SubString(sched.nextRunDate, 5, 2) + '/'
+ SubString(sched.nextRunDate, 7, 2) + ' '
+ SubString(sched.nextRunTime, 1, 2) + ':'
+ SubString(sched.nextRunTime, 3, 2) As 'Next Run Date / Time'
, Case jhist.run_status
When 0 Then 'Failed'
When 1 Then 'Successful'
When 2 Then 'Retry'
When 3 Then 'Canceled'
Else 'Unknown Status'
End as 'Last Run Status' ,
@@SERVERNAME AS Server_Name
From msdb.dbo.sysjobs As job
JOIN msdb.dbo.sysjobhistory as jhist on job.job_id = jhist.job_id and jhist.step_id = 0
Join myCTE As sched
On job.job_id = sched.job_id
Where job.enabled = 1
end
What happens if you add ; before the with?
Nothing, it still fails. The script works fine on every other server, just not one of them.
Check the database compatibility.
On the one that is not working I used sp_dbcmptlevel which showed:
Valid values of database compatibility level are 60, 65, 70, or 80.
On one that the query is working I get the same:
Valid values of database compatibility level are 60, 65, 70, or 80.
I've also noticed that on this server the table msdb.dbo.sysschedules is missing.
If sp_dbcmptlevel is only showing 80 as the highest then it thinks this is a SQL Server 2000 server. So WITH wouldn't be available.
Are you sure it's pointing where you think it is?
Could someone have restored a 2000 version of master or msdb?
What does SELECT @@VERSION return?
The first error says: Incorrect syntax near the keyword 'With'.
That would seem to say that it knows 'With'. Or is that an invalid conclusion to draw?
I concur with @graz. You are using SQL 2000 or lower. On my SQL 2016, the message is
Valid values of the database compatibility level are 100, 110, 120, or 130.
SQL 2005 is compatibility level 90, which is the earliest version where the WITH construct was available.
Also, if you run it with no parameters, it prints out a message such as the one you saw. You should provide the database name to the procedure as in
EXEC sp_dbcmptlevel @dbname = 'YourDatabaseNameHere'
Not quite. The keyword WITH is also used in other contexts in T-SQL. Even if that were not the case, you cannot draw the conclusion that it is aware of the keyword simply because it referred to it in the error message.
please see these options if CTE is not working
.. SQL Server doesn't have WITH/CTE. Instead, use a "Derived Table" which is nearly identical in most cases. A derived table is just like a CTE in that it's a query that's used in a FROM clause as if its result set were a table. In other words, it's a subquery in the FROM clause just like a CTE is a subquery in the WITH clause. The format is...
SELECT d.yada-yada
FROM (--===== derived table starts here
SELECT yada-yada
FROM sometable(s)
) d
In the above example, "d" is the table alias for the derived table.
As others have mentioned, WITH only came into being with SQL Server 2005.
Prior to that, for simple subqueries just use a subquery in the FROM clause
;with temp_table(userid) as
(Select distinct uid from tableOfRecords)
select * from temp_table
-- reordered in 2000 as
select *
FROM (Select distinct uid from tableOfRecords) temp_table(userid)
I just checked, yes it is only a SQL server 2000 version, that is the problem.
Many thanks for helping!
As a side note I just noticed that when I login to the actual server, open mgmt. studio, it is SQL Version 2008, and run a query it runs fine. What have I missed, how come in my own local version of mgmt. studio it is 2000, but on the server it is 2008?
Hi
Depends on what you have installed
It could be one of a few things. You may have an older version of SQL Management Studio (although this is unlikely). You can check the version from Help ->About in SSMS. You can download and install the latest version of Management Studio from here.
Next thing to check is your server version. You can do this by running the following T-SQL query
SELECT @@VERSION
I have SQL 2016, and it shows something like "Microsoft SQL Server 2016 ......"
You should also check the compatibility level of the databases. You can do this using the following query:
SELECT compatibility_level,* FROM sys.databases
90 is SQL 2005. I have SQL 2016, which shows compatibility level = 130.
If you are on versions prior to SQL 2008, sys.databases may not be available. You can use EXEC sp_dbcmptlevel @dbname = 'YourDatabaseName'; to find the compatibility level. Be sure to do that for each of your databases.
I've got 2008 and 2012 SQL on my machine, the 2008 has a Configuration Tools > 64 bit installation center link. The 2012 version has I'm mgmt. studio in it.
As far as I know... version of Ssms should not make any difference
I once had 2014 SQL server with 2008 Ssms
I could still use the advanced tsql functions of 2014 ..even though they were NOT recognized by Ssms 2008