?
Solved

Sql Server Compatibility Level

Posted on 2009-05-06
19
Medium Priority
?
1,155 Views
Last Modified: 2012-08-13
I was working on a Sql Server where the compatibility level is 70.  I'm using sql server 2005.

I added a job to th Server Agent.  Previously, when the user right clicked a job, they would see "Start Job".  Now when the right click the job, they see "Start Job at Step".

Additionally, they get the following error when the run the job (this is an existing job - not the new one I added):  




TITLE: Microsoft.SqlServer.SmoEnum
------------------------------

Failed to retrieve data for this request.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Incorrect syntax near 'collate'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. (Microsoft SQL Server, Error: 325)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------



I need to correct the issue so they can run their jobs again.  Has anyone seen this?  And how could I go about correcting it?
0
Comment
Question by:hi2way
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
19 Comments
 

Author Comment

by:hi2way
ID: 24314916
Just to clarify, they get the error message on the new job I added as well as existing jobs that I did not modify.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24314985
what T-SQL do you have in job?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 24314988
If you have the compatibility level for that database set to 70, that is SQL 7.0.  That's an old compatibility level.  Do you need it there, if not, update it to 2005.  2005, is 90 in the compatibility settings.
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 1000 total points
ID: 24315005
If you will update comitability level, you might get rid of this error
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315022
you can change compatible level by

EXEC sp_dbcmptlevel databaseName, 90;
0
 

Author Comment

by:hi2way
ID: 24315114
RiteshShah,
Will that procedure have an adverse impact on anything else?
Also,
Is Exec sp_dbcmptlevel databasename, 90;  the same as right clicking the database name, selecting properties, selecting options and changing the compatibility level?
0
 

Author Comment

by:hi2way
ID: 24315137
Do you know of any reason they would have previously seen "Start Job" and now they see "Start Job at Step"?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315144
well, it shouldn't have any other adverse effect, it may affect some smaller part, not sure. BTW, it is same at the procedure you told me by right clicking,

Moreover, you can change level back to 70 if you wish but I recomend you to do it after talking full backup.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315155
actually you should be seeing

"Start Job at step" followed by "Stop Job" this is how it is in SQL Server, when job is stopped due to some reason, they might have seen "Start Job"
0
 

Author Comment

by:hi2way
ID: 24315185
When they right clicked a job, they used to see Start Job and underneath that they saw Stop Job.  Now they see Start Job at Step and underneath it they still see Stop Job.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315215
This means that, job is on right now, it will be executed automatically as soon as its time reach which you have fixed but if you want to run job right now than click on "Start Job at Step"
0
 

Author Comment

by:hi2way
ID: 24315233
I did change the compatibility level using the right click procedure and then reran the job.  But the same message appeared.  When I run the same job locally on my home machine, it runs fine - even if I change the compatibility level of my local database to 70.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315260
well in that case, you have to check user account from services->SQL Server Agent

the user you are using for running JOB, should have full access.
0
 

Author Comment

by:hi2way
ID: 24315286
They do.  They sign in as administrator and they run this job regularly.  The old job only started to fail seemingly when I a new unrelated job.
0
 

Author Comment

by:hi2way
ID: 24315501
RiteshShah,
I found this on MSDN.  Apparently "Start Job" was the option in sql 2000.  Is it possible that by me adding a job, it somehow caused their server to begin using 2005 functionality?
http://msdn.microsoft.com/en-us/library/aa177008(SQL.80).aspx 
How To (SQL Server 2000) How to start a job (Enterprise Manager)How to start a job (Enterprise Manager)
To start a job In the details pane, right-click the job, and then do of the following: Click Start Job if you are working on a single server, or working on a target server, or running a local server job on a master server.


Click Start Job, and then click Start on all targeted servers if you are working on a master server and want all targeted servers to run the job simultaneously.


Click Start Job, and then click Start on specific target servers if you are working on a master server and want to specify target servers for the job.
In the Post Download Instructions dialog box, select the These target servers check box, and then select each target server on which this job should run.

Site Feedback  
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315828
that's true, but have you checked permission of users?
0
 

Author Comment

by:hi2way
ID: 24315888
I haven't checked user permissions primarilly because there is only one user:  SA and SA has full access to everything.  The users use SA and I use SA.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315918
if you are talking backup of something than it should have rights of diskadmin.
0
 

Author Comment

by:hi2way
ID: 24316004
It's not a backup.  It's an update to a table in the database.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question