[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Sql Server Compatibility Level

Posted on 2009-05-06
19
Medium Priority
?
1,166 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

650 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