change MSDB owner: compatibility

Showing posts with label compatibility. Show all posts

Tuesday, March 27, 2012

Changing Compatibility Level on an Upgraded SQL Server 2000 db to 2005 causing delete to b

I have just upgraded my SQL Server 2000 to SQL Server 2005 and from
what I understand the upgrade will do the database conversions for you.
After the install I tried to delete a row from my Item table which is a
foreign key to about 10 other tables. A simple
Delete Item Where ItemID = 888
In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
to let it run successfully, i stop it after 5mins.
After the upgrade I changed the Compatibility Level to SQL Server 2005
(90) under Database Properties -> Options
After I changed the Compatibiliity Level back to SQL Server 2000 (80)
the query ran in approx 17 secs.
The Item table is about 8000 rows and the size of the tables
referencing the Item table varies. But why would changing the
Compatibility Level cause such a difference in performance? Did I miss
a step in upgrading? From what I understood, changing the Compatibility
Level just allows you to use new SQL Server 2005 features like MARS,
etc...
Please help.
thanks
benblan
UPDATE STATISTICS
REBUILD INDEXES
"blam" <bendlam@.gmail.com> wrote in message
news:1138810049.298299.209110@.g43g2000cwa.googlegroups.com...
>I have just upgraded my SQL Server 2000 to SQL Server 2005 and from
> what I understand the upgrade will do the database conversions for you.
> After the install I tried to delete a row from my Item table which is a
> foreign key to about 10 other tables. A simple
> Delete Item Where ItemID = 888
> In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
> to let it run successfully, i stop it after 5mins.
> After the upgrade I changed the Compatibility Level to SQL Server 2005
> (90) under Database Properties -> Options
> After I changed the Compatibiliity Level back to SQL Server 2000 (80)
> the query ran in approx 17 secs.
> The Item table is about 8000 rows and the size of the tables
> referencing the Item table varies. But why would changing the
> Compatibility Level cause such a difference in performance? Did I miss
> a step in upgrading? From what I understood, changing the Compatibility
> Level just allows you to use new SQL Server 2005 features like MARS,
> etc...
> Please help.
> thanks
> ben
>|||thanks for the reply,
I did all that last night, rebuilt the indexes, updated the statistics,
etc...this morning the query still didn't run, i had to stop it after
5mins. Then all I did was change the compatibility level to SQL Server
2000 (80) then reran the query and it took 17secs.
Any other suggestions?|||blam wrote:
> thanks for the reply,
> I did all that last night, rebuilt the indexes, updated the statistics,
> etc...this morning the query still didn't run, i had to stop it after
> 5mins. Then all I did was change the compatibility level to SQL Server
> 2000 (80) then reran the query and it took 17secs.
> Any other suggestions?
Check the query plan with both compatibility levels. That may give a clue.|||Can you compare the execution plan with compatibility level 80 with the
excution plan with compatibility level 90
I found different values in the execution plan when i change the
compatibility level between 70 / 80.|||You may need to change your queries/stored procs. After we migrated to SQL
2005, some of the stored procedures slowed down to a crawl, even after
updating the stats and rebuilding the indexes. Due to changes in the database
engine from version to version (and even from service upgrades), the query
plans may change as well. We had do minor changes to the queries and sps in
order to get it back to the way it was performing on SQL 2000.
"gshadow1" wrote:
> Can you compare the execution plan with compatibility level 80 with the
> excution plan with compatibility level 90
> I found different values in the execution plan when i change the
> compatibility level between 70 / 80.
>
>

Changing compatibility level from 65 to 80 - Whats up?

All all!

My first post! I know that with changing compatibility levels from 65 to 80 means changes have to be made to the database tables and stored procedures. The problem Im having is finding out what exactly needs to be changed in the tables and stored procs.

Can anyone help me out...what exactly will I need to change in the tables and the stored procs when I set a database from level 65 to 80??

Thanks!hello,

I'm facing that problem, do you have any experience of this problem? Did you have any issue when you change compatibility level from 65 to 80?
I appreciate if you give me some advices

Thanks and regards,

changing compatability level from80(2000) to 90(2005)

I am planning to migrate my database from sql 2000 to sql 2005

I need to know when i change the compatibility from 80 to 90

1) What are the things which i need to look out for. If i have changed every thing as per upgrade advisor will i have to make or anticipate further changes.

2) Please tell me whether if i dont use 90 compatability level will i miss out anything?

What am i missing out ?

Will it be possible to do online index re-build with compatability level set to 80.

Is there a list of new benefits/changes i will be getting when i move to sql 2005 compatability set to 90.

Please do reply .Awaiting your answers,....

Thanks in advance.

The differences are all here (table lists them all about half way down the page) - http://msdn2.microsoft.com/en-us/library/ms178653.aspx
Many other things to watch for are here - http://msdn2.microsoft.com/en-us/library/ms143179.aspx

Online indexing operations are supported in 80 compatibility (on SQL Server 2005 Enterprise as that is the only version that supports them).

|||

Thank you for your reply

I want to know whether i wont be able to use any feature of Management studio or any other feature if i am not changing to compatability level 90

Thanks in advance

|||

Couple that I can think of

You can't use Database Diagrams

You can't use custom reports (on the summary tab) - http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/22/ssms-custom-reports-backwards-compatibility-or-lack-thereof.aspx

Thursday, February 16, 2012

change precision in a table column

Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
precision of one column in a table. I scrioted the drop and recrete of the
table in EM. Made the change I needed to the precision on the one column in
the table then tested the script before running it. Got a "incorrect syntax
near Collate" when test script. Any idea why I would get this since SQL made
the script for me? If I'm recreating the table like it was save for higher
precision on one column is there anything I need to do concerning Views,
Triggers, keys, indexes, etc? I have the table data copied to another like
table in another db. I know this is probably basic type stuff but I just
know basic SQL suff and how to use EM. Any guideance would be appreciated.
ThanksSeems like EM scripting include some features which isn't available in 6.5. COLLATE is new for SQL
Server 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
> Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
> precision of one column in a table. I scrioted the drop and recrete of the
> table in EM. Made the change I needed to the precision on the one column in
> the table then tested the script before running it. Got a "incorrect syntax
> near Collate" when test script. Any idea why I would get this since SQL made
> the script for me? If I'm recreating the table like it was save for higher
> precision on one column is there anything I need to do concerning Views,
> Triggers, keys, indexes, etc? I have the table data copied to another like
> table in another db. I know this is probably basic type stuff but I just
> know basic SQL suff and how to use EM. Any guideance would be appreciated.
> Thanks|||is there any danger in changing the db compatibility to 80, makeing the
change to the table, then changing db back to 65?
"Tibor Karaszi" wrote:
> Seems like EM scripting include some features which isn't available in 6.5. COLLATE is new for SQL
> Server 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
> news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
> > Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
> > precision of one column in a table. I scrioted the drop and recrete of the
> > table in EM. Made the change I needed to the precision on the one column in
> > the table then tested the script before running it. Got a "incorrect syntax
> > near Collate" when test script. Any idea why I would get this since SQL made
> > the script for me? If I'm recreating the table like it was save for higher
> > precision on one column is there anything I need to do concerning Views,
> > Triggers, keys, indexes, etc? I have the table data copied to another like
> > table in another db. I know this is probably basic type stuff but I just
> > know basic SQL suff and how to use EM. Any guideance would be appreciated.
> > Thanks
>|||That should be fine. But why not quite simply use some search and replace to remove the non-65
compatible stuff from the script?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
news:862BBD13-1138-4840-916F-2939861A0195@.microsoft.com...
> is there any danger in changing the db compatibility to 80, makeing the
> change to the table, then changing db back to 65?
> "Tibor Karaszi" wrote:
>> Seems like EM scripting include some features which isn't available in 6.5. COLLATE is new for
>> SQL
>> Server 2000.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
>> news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
>> > Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
>> > precision of one column in a table. I scrioted the drop and recrete of the
>> > table in EM. Made the change I needed to the precision on the one column in
>> > the table then tested the script before running it. Got a "incorrect syntax
>> > near Collate" when test script. Any idea why I would get this since SQL made
>> > the script for me? If I'm recreating the table like it was save for higher
>> > precision on one column is there anything I need to do concerning Views,
>> > Triggers, keys, indexes, etc? I have the table data copied to another like
>> > table in another db. I know this is probably basic type stuff but I just
>> > know basic SQL suff and how to use EM. Any guideance would be appreciated.
>> > Thanks
>>|||I have no idea how to do that or even what that feature your refering to is.
If it removes it from script then how would I get table recreated properly
so it works with the application?
"Tibor Karaszi" wrote:
> That should be fine. But why not quite simply use some search and replace to remove the non-65
> compatible stuff from the script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
> news:862BBD13-1138-4840-916F-2939861A0195@.microsoft.com...
> > is there any danger in changing the db compatibility to 80, makeing the
> > change to the table, then changing db back to 65?
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems like EM scripting include some features which isn't available in 6.5. COLLATE is new for
> >> SQL
> >> Server 2000.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
> >> news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
> >> > Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
> >> > precision of one column in a table. I scrioted the drop and recrete of the
> >> > table in EM. Made the change I needed to the precision on the one column in
> >> > the table then tested the script before running it. Got a "incorrect syntax
> >> > near Collate" when test script. Any idea why I would get this since SQL made
> >> > the script for me? If I'm recreating the table like it was save for higher
> >> > precision on one column is there anything I need to do concerning Views,
> >> > Triggers, keys, indexes, etc? I have the table data copied to another like
> >> > table in another db. I know this is probably basic type stuff but I just
> >> > know basic SQL suff and how to use EM. Any guideance would be appreciated.
> >> > Thanks
> >>
> >>
>|||If you don't feel confident doing it, I'd recommend upping it to the higher level, run the scripts
and then lowering compat level again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
news:CDA93918-06F0-4D01-AE9D-AC1376E79D93@.microsoft.com...
>I have no idea how to do that or even what that feature your refering to is.
> If it removes it from script then how would I get table recreated properly
> so it works with the application?
> "Tibor Karaszi" wrote:
>> That should be fine. But why not quite simply use some search and replace to remove the non-65
>> compatible stuff from the script?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
>> news:862BBD13-1138-4840-916F-2939861A0195@.microsoft.com...
>> > is there any danger in changing the db compatibility to 80, makeing the
>> > change to the table, then changing db back to 65?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Seems like EM scripting include some features which isn't available in 6.5. COLLATE is new for
>> >> SQL
>> >> Server 2000.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
>> >> news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
>> >> > Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
>> >> > precision of one column in a table. I scrioted the drop and recrete of the
>> >> > table in EM. Made the change I needed to the precision on the one column in
>> >> > the table then tested the script before running it. Got a "incorrect syntax
>> >> > near Collate" when test script. Any idea why I would get this since SQL made
>> >> > the script for me? If I'm recreating the table like it was save for higher
>> >> > precision on one column is there anything I need to do concerning Views,
>> >> > Triggers, keys, indexes, etc? I have the table data copied to another like
>> >> > table in another db. I know this is probably basic type stuff but I just
>> >> > know basic SQL suff and how to use EM. Any guideance would be appreciated.
>> >> > Thanks
>> >>
>> >>
>>|||ok thanks
"Tibor Karaszi" wrote:
> If you don't feel confident doing it, I'd recommend upping it to the higher level, run the scripts
> and then lowering compat level again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
> news:CDA93918-06F0-4D01-AE9D-AC1376E79D93@.microsoft.com...
> >I have no idea how to do that or even what that feature your refering to is.
> > If it removes it from script then how would I get table recreated properly
> > so it works with the application?
> >
> > "Tibor Karaszi" wrote:
> >
> >> That should be fine. But why not quite simply use some search and replace to remove the non-65
> >> compatible stuff from the script?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
> >> news:862BBD13-1138-4840-916F-2939861A0195@.microsoft.com...
> >> > is there any danger in changing the db compatibility to 80, makeing the
> >> > change to the table, then changing db back to 65?
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Seems like EM scripting include some features which isn't available in 6.5. COLLATE is new for
> >> >> SQL
> >> >> Server 2000.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
> >> >> news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
> >> >> > Running SQL 2K sp3a, db is on compatibility level 65. I need to change the
> >> >> > precision of one column in a table. I scrioted the drop and recrete of the
> >> >> > table in EM. Made the change I needed to the precision on the one column in
> >> >> > the table then tested the script before running it. Got a "incorrect syntax
> >> >> > near Collate" when test script. Any idea why I would get this since SQL made
> >> >> > the script for me? If I'm recreating the table like it was save for higher
> >> >> > precision on one column is there anything I need to do concerning Views,
> >> >> > Triggers, keys, indexes, etc? I have the table data copied to another like
> >> >> > table in another db. I know this is probably basic type stuff but I just
> >> >> > know basic SQL suff and how to use EM. Any guideance would be appreciated.
> >> >> > Thanks
> >> >>
> >> >>
> >>
> >>
>|||Hi Brian,
Just jump in to check if Tibor 's suggestion helped you? Feel free to post
back if you still have any concerns.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: change precision in a table column
>>thread-index: AccKg2LRdt7Sq4j4TtuTHvjcqTsv3w==>>X-WBNR-Posting-Host: 162.42.230.150
>>From: =?Utf-8?B?QnJpYW4=?= <c49a36a4-2142028833@.news.postalias>
>>References: <F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com>
<uGcHmolCHHA.4024@.TK2MSFTNGP04.phx.gbl>
<862BBD13-1138-4840-916F-2939861A0195@.microsoft.com>
<O4i5hPmCHHA.4312@.TK2MSFTNGP06.phx.gbl>
<CDA93918-06F0-4D01-AE9D-AC1376E79D93@.microsoft.com>
<OcTyaxnCHHA.472@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: change precision in a table column
>>Date: Fri, 17 Nov 2006 12:03:01 -0800
>>Lines: 66
>>Message-ID: <2F3DD1CA-6CDF-4816-A58E-9B557C3B0E6A@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:451835
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>ok thanks
>>"Tibor Karaszi" wrote:
>> If you don't feel confident doing it, I'd recommend upping it to the
higher level, run the scripts
>> and then lowering compat level again.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
>> news:CDA93918-06F0-4D01-AE9D-AC1376E79D93@.microsoft.com...
>> >I have no idea how to do that or even what that feature your refering
to is.
>> > If it removes it from script then how would I get table recreated
properly
>> > so it works with the application?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> That should be fine. But why not quite simply use some search and
replace to remove the non-65
>> >> compatible stuff from the script?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
>> >> news:862BBD13-1138-4840-916F-2939861A0195@.microsoft.com...
>> >> > is there any danger in changing the db compatibility to 80,
makeing the
>> >> > change to the table, then changing db back to 65?
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Seems like EM scripting include some features which isn't
available in 6.5. COLLATE is new for
>> >> >> SQL
>> >> >> Server 2000.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Brian" <c49a36a4-2142028833@.news.postalias> wrote in message
>> >> >> news:F2328B08-F6BD-4FB5-B6BF-181880000E78@.microsoft.com...
>> >> >> > Running SQL 2K sp3a, db is on compatibility level 65. I need
to change the
>> >> >> > precision of one column in a table. I scrioted the drop and
recrete of the
>> >> >> > table in EM. Made the change I needed to the precision on the
one column in
>> >> >> > the table then tested the script before running it. Got a
"incorrect syntax
>> >> >> > near Collate" when test script. Any idea why I would get this
since SQL made
>> >> >> > the script for me? If I'm recreating the table like it was
save for higher
>> >> >> > precision on one column is there anything I need to do
concerning Views,
>> >> >> > Triggers, keys, indexes, etc? I have the table data copied to
another like
>> >> >> > table in another db. I know this is probably basic type stuff
but I just
>> >> >> > know basic SQL suff and how to use EM. Any guideance would be
appreciated.
>> >> >> > Thanks
>> >> >>
>> >> >>
>> >>
>> >>
>>

Tuesday, March 27, 2012

Changing Compatibility Level on an Upgraded SQL Server 2000 db to 2005 causing delete to b

Changing compatibility level from 65 to 80 - Whats up?

changing compatability level from80(2000) to 90(2005)

Thursday, February 16, 2012

change precision in a table column

change precision in a table column

change precision in a table column

change MSDB owner

Blog Archive

About Me