Compatibility Level 80, DMVs, Optimizer, Table Partitioning

Oct 11, 2007

Hi all

I cannot find this info anywhere. We have Hyperion 8.x running against SQL 2000. We will upgrade side-by-side in the coming weeks. It appears that Hyperion 8.x is assured to continue to work without issue with a 2K5 database in 80 compatibility level.

I have seen the BOL table on differences between 80 and 90 (specifically affects us - WITH on Index hints), but what I wonder are about 3 specific features.

1. Do you still get DMV capabilities in 80 mode? (I ran a test on sys.dm_db_index_usage_stats for an 80 compat DB, creating index and using query to hit index, and yes - it does look as if this DMV does get populated).

2. Can you still Table Partition in 80 mode? (Ran a test in DEV, and yes - this does look possible - any gotchas?)

3. Does the optimizer behave quite differently in 80 mode?

I have tested in DEV moving a DB from 80 to 90 and back, and this seems to work with no problems.


TIA from Grand Fenwick!! ;-)

View 1 Replies


ADVERTISEMENT

Compatibility Level

Oct 24, 2007

Hi Gurus,

I would like to know if I put the Compatibility Level in a SQL Server 2005 installation to 70 I can make afirmation that I have a full SQL Server 7.0. If the answer is "Yes" where I can find a documentation or a FAQ that explained this topic.

View 2 Replies View Related

Compatibility Level

Dec 7, 2007

Has anyone changed compatibility level from 80 to 90? Did you have any problems?

View 8 Replies View Related

Compatibility Level From 80 To 90

Feb 3, 2008

I restored the database from SQL server 2000 to 2005.The database was restored with 80 compatibility.Can i change it to 90 and what are the effects? Coz my applications are pulling data from SQL server 2000. Does 80 work for database mirroring?

View 5 Replies View Related

Compatibility Level

Feb 13, 2008

Hi there

We found interesting issue which is basically the app is being tested ok on SQL2005 by software vendor. Then we tested in our environment and we found it's not truly true. There are some compatibility issue on SQL syntax. Anyway ... the plan set the compatibility level back to 80 instead 90. Cause this thing for sure is working.

Now my question is do you know any other impacts that you know of if we are doing this setting (running SQLServer 2005 but the database set as 80)? I know that some inbuilt reporting only run 90 level but I can get around this. Performance or something? Is there any thing that I should to know?

Thanks

View 1 Replies View Related

Compatibility Level

Jul 26, 2006

Having moved over to SQL 2k5, from SQL 7.0 we have now realised that the database's need to be set to comp level 9.0 before they are found in the maintence plan wizard, we currently still access the database using an Access 2000 front end, by changing the comp level will this cause us issues writing data, I'm sure it won't but want to make sure, I'm sure that the comp level just sets what options are available to use.

Thanks

View 1 Replies View Related

Urgent! Compatibility Level

Nov 27, 2001

We upgraded from SQL 6.5 to 2000 (.384) and just look at this T-SQL and maybe somebody can tell me what's wrong.

sp_dbcmptlevel appetserv1,80

update articleentrepot set usagermodification = 'MANON' where idarticle = 1

Here is the result:
Cannot use the column prefix 'E'. This must match the object in the UPDATE clause 'ArticleEntrepot'.

When I put the compatibility level to 65, it's works. But my problem is that I need to have the compatibility level to 80 because of the DataMirror replication software and if I do that, my house application doesn't work. I really don't know what to do, I'm in a deadlock. Can you help me!
Manon Tremblay

View 2 Replies View Related

Database Compatibility Level

Feb 20, 2001

Hi!
After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65".
I checked that by executing sp_dbcmptlevel <database_name>.
I can change it to "70" but my question is how it's going to affect the application and do I have to change it?

Thank you

Lena

View 2 Replies View Related

Compatibility Level Keeps Changing

Dec 18, 2002

I have a problem where I have a software application that
needed to be updated to a newer version. In order for the
new version to work I needed to upgrade my SQL Server 7
database to SQL Server 2000. The upgrade went fine for
SQL Server 2000 and also with the application. The only
problem now is that the compatibility level for the
databases stays at 70. Even when I change the
compatibility level to 80 it will automatically go back to
80. What is wrong? Please help.

View 3 Replies View Related

Check Db Compatibility Level In SQL 7.0

Mar 23, 1999

I installed SQL 7.0 on a test server a few days ago. I want to test an application that currently runs on SQL 6.5. I have run a test database through the upgrade wizard without errors. Is there any way to see the compatibility level that the db is running on. I know I can use the sp_dbcmptlevel procedure to make it run at the 6.5 level. I want to see if it is running at that level currently, or if it is at the 7.0 level.

Any help would be appreciated.

View 1 Replies View Related

Master Compatibility Level

May 28, 2008

I installed SQL 2K5 (not upgrade from 2K) and just found that Master DB comp. level is 80. All other system DBs are 90.

1. How can I change master's to 90? Does the following work?

EXEC SP_DBCMPTLEVEL Master, 90;
go


2. Changing this, is there any possibility to impact my production?

Canada DBA

View 5 Replies View Related

Set Compatibility Level Only When It Is Greater Than 110?

Mar 5, 2015

Problem: I want to set compatibility_level only when it is greater than 110.

Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110

ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.

Here is the sql statement

IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END

What is that I am missing here ?

View 1 Replies View Related

Master Compatibility Level 80

Jan 19, 2007

Does anyone know why Master is still at Compatibility Level 80 after an upgrade from SQL 2000 to SQL 2005?

View 8 Replies View Related

Encryption And Compatibility Level 80

Jun 26, 2007

Does anyone have any insight as to whether the new data encryption features of SQL Server 2005 work when the database is set to compatibility level 80?

View 1 Replies View Related

How To Change The Compatibility Level To 9.0

Aug 23, 2006

I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL?? any help is greatly appreciated.

Emad

View 1 Replies View Related

SP2 Compatibility Level Forced To 90?

May 22, 2007

I'd heard that upgrading SQL 05 to SP2 forced any databases at SQL 2000 compatibility (80) to SQL 2005 compatibility, but I can't find any documentation that says one way or the other.



Can anyone provide insight as to whether or not this is true?



Thanks,

Lenny

View 1 Replies View Related

How Can I View My Current DB&#39;s Compatibility Level?

Oct 11, 2000

I can set the compatibility level by using :sp_dbcmptlevel
But before doing this - How can i view my current DB's compatibility level?
Any help appreciated

View 1 Replies View Related

Upgrade Compatibility Level Setting

Jun 14, 2000

Hi All,
We were upgraded our databases from 6.5 to 7.0 .After upgrading
the Compatibility level was 65 .We changed to 70 for all databases using sp_dbcmptlevel system stored proc.We were having some problems with Ansi nulls if the compatablity level is 70,We changed back to 65 that works fine--OK with ANSI NULLS.While Upgading ANSI NULLS OFF after upgrading ANSI NULLS OFF.But from the front end side ODBC settings ANSI NULLS are ON.Is that some thing to do with compatablity level or with ANSI NULLS .
If you guys have any great ideas Please let me know.

Thanks A Lot,
VJ

View 1 Replies View Related

Advantages Of Changing Compatibility Level

Jun 12, 2008

Dear All,
what are the advantages of changing compatibility level from 80 to 90? are there any disadvantages doing this on production machine? will it take any downtime?
i've searched the google but i didnt get the correct info.

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 2 Replies View Related

Compatibility Level SQL Server 2000 (80)

Jan 6, 2006

Hallo Everyone,

I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work

Any ideas?

Nigel...

View 12 Replies View Related

Does In-place Upgrade Set Compatibility Level To 9.0

Jun 19, 2007

This may be very obvious and when I get to the first test server I will find out, but I only have a new installation of SQL2K5 so far to test with. I have several databases that need to be upgraded to run on 2K5, but the application requires them to run in 8.0 compatibility. Does this mean that an in-place upgrade is not an option?

I apologize if this is a silly question, but I have not yet found the answer specifically.

Thanks very much.

View 4 Replies View Related

SQL 2005 Upgrade With Compatibility Level 80

Nov 30, 2007

Hi All,

We have upgraded our database from SQL 2000 to SQL 2005 keeping the compatibility level 80.
Now the question is -
Would microsoft support compatibility level 80 in SQL Server 2005 even after the extended support date for SQL Server 2000?

OR

If Microsoft dropped the support for SQL 2000, then would Microsoft support compatibility level 80 in SQL Server 2005?


Thanks

View 5 Replies View Related

Row Level Locking And SQL Server 6.5 Compatibility Mode

Jan 5, 2001

I'm running SQL Server 7.0. I have a DB running with 6.5 compatibility mode.

Do INSERT, UPDATE or DELETE queries use row level locking in this DB ?

(I know if I set the db compatibility mode to 7.0 row level locking will be enabled)

Thanks in advance for your help.

View 1 Replies View Related

Programmatically Checking The Database Compatibility Level?

Jun 28, 2006

When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?

View 3 Replies View Related

Misterious Change Of Compatibility Level In A Data Base

Aug 6, 2007



Hi everybody:


We have recently migrated our DDBB from SQL 2000 to SQL 2005 in several Servers. We have 2 DDBB per Server and the size of mdf files are between 10 and 40 GB.

We put Compatibility Level in 90 in SQL 2005 but when we arrive at work we see that our Maintenace Plans failed because the Compatibility Level of one of Data Bases changed to 70.


We have a Trace executing the whole day registrying the execution of stored procedure 'sp_dbcmptlevel' but in despite of Compatibility Level changes, the Trace does not registry anything.


Has anyone passed before me for this situation? Thank you in advance and greetings,


Nuria

View 4 Replies View Related

Down Time To Switch Compatibility Level On Large Database?

Aug 4, 2015

We have a reasonably large (several TB) database that was recently migrated from 2008 to a new box running 2014.  Before giving it back to the users we forgot to change the compatibility level of the DB to bring it up to date with it's new environment.

We want to do some testing with backup compression so want to change the compatibility level, but we are unsure whether making the change on such a large database would cause slowness or downtime for our users.

Does the process of changing the compatibility level simply allow options that are not available in the older version or does it make structural changes to the database that would cause the users to notice slowness or downtime?

View 7 Replies View Related

Features Not Supported In Compatibility Level 80 Of SQL Server 2005

Oct 23, 2007

Hello All,
I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.

The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features.
From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing.
Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine.
One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported

It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.

Thanks in Advance,
Mitesh

View 4 Replies View Related

How To Assess Impact Of Changing Database Compatibility Level

Aug 28, 2006

I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.

I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?

View 4 Replies View Related

SQL Server Admin 2014 :: Changing Compatibility Level Of System Databases

Jun 27, 2014

I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??

View 1 Replies View Related

Missing Compatibility Level Sql Server 2005(90) In Database Properties Options

Jul 13, 2007

I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!

-chris

View 12 Replies View Related

SQL 2012 :: Selected Subscriber Does Not Satisfy Minimum Version Compatibility Level Of Selected Publication

Feb 21, 2014

I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"

The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...

View 0 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

Partitioning A Table

Mar 27, 2008

i have a table named "user" in which user which are located at different places within a city are recorded.
i want to group user with respect to there location like users of northern region are recorded first then users of western region and so on.
tell me from horizontal and vertical partitioning wh technique is better or i should use some other technique.
thanks  for ur consideration.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved