Solved

Sql Server Compatibility Level

Posted on 2009-05-06
19
1,112 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
  • 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 250 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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now