A couple of weeks ago we had upgraded our SQL Server 6.5
to ver. 7.0. We've just observed that for all of our
databases, the database compatibility level remains at 65.
My concern here is... can we change this compatibility
level to 70 on the fly? Would this incur an outage of any
sort? Are there any precautions that I should be aware of
before set the database compatibility level to 70?
Thanks in advance for your help and suggestions.You can certainly change on the fly, but there will be some semantic =changes to the SQL that may catch out your application. BOL has a =summary of the differences, but it is things like:
SELECT 'a' + '' + 'b' -- Nothe there is no space between the middle pair =of single quotes -
At 65 will give 'a b' - ie the zerolength string is interpreted as a =single space.
At 7 will give 'ab' - which I would say is right.
Others such as =3D NULL behaving non-standard at 65 but fixed at 7 can =catch you if the app uses it.
BOL has a description of all the differences.
Mike John
"Rob" <rhchin@.hotmail.com> wrote in message =news:01fc01c3425c$c76bc8a0$a301280a@.phx.gbl...
> A couple of weeks ago we had upgraded our SQL Server 6.5 > to ver. 7.0. We've just observed that for all of our > databases, the database compatibility level remains at 65.
> > My concern here is... can we change this compatibility > level to 70 on the fly? Would this incur an outage of any > sort? Are there any precautions that I should be aware of > before set the database compatibility level to 70?
> > Thanks in advance for your help and suggestions.
Showing posts with label upgraded. Show all posts
Showing posts with label upgraded. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
Database at 9.0.3152 but SSMS at 9.0.3042 - Problem?
I've got a SQL 2K5 installation that I have upgraded to 9.0.3152. In SSMS, I see the SQL Server instance showing with the correct version - 9.0.3152. But SSMS (Help->About) shows a version of 9.00.3042. Is this expected or should "Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available" (http://support.microsoft.com/default.aspx/kb/933097/) have upgraded all these to 3152, as well?
Thanks!
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
There are several ways to check the version level of various components. The easiest seems to be by using Add/Remove Programs. Click on your SQL entry, then uninstall, and then Change. This pops up a nice report showing your machine at the component level and the associated versions. If SSMS is at 3042, you can apply hotfix 3152 and bring it up to date. My guess is that SSMS might not show the hotfix level (only the SP level) and that your box is updated already.
Thanks,
Sam Lester (MSFT)
|||Really it may not be the easiest way for remote server.
Iа I were you (or if you were I), I would have preferred
SERVERPROPERTY function.
SELECT SERVERPROPERTY('productversion') AS ProductVersion,
SERVERPROPERTY ('ResourceVersion') As ResourceVersion ,
SERVERPROPERTY ('productlevel') AS ProductLevel,
SERVERPROPERTY ('edition') As Edition,
SERVERPROPERTY ('ResourceLastUpdateDateTime') As ResourceLastUpdateDateTime
Shows for me
ProductVersion ResourceVersion ProductLevel Edition ResourceLastUpdateDateTime
9.00.3054.00 9.00.3042 SP2 Developer Edition 2007-02-10 00:39:35.480
Database at 9.0.3152 but SSMS at 9.0.3042 - Problem?
I've got a SQL 2K5 installation that I have upgraded to 9.0.3152. In SSMS, I see the SQL Server instance showing with the correct version - 9.0.3152. But SSMS (Help->About) shows a version of 9.00.3042. Is this expected or should "Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available" (http://support.microsoft.com/default.aspx/kb/933097/) have upgraded all these to 3152, as well?
Thanks!
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
There are several ways to check the version level of various components. The easiest seems to be by using Add/Remove Programs. Click on your SQL entry, then uninstall, and then Change. This pops up a nice report showing your machine at the component level and the associated versions. If SSMS is at 3042, you can apply hotfix 3152 and bring it up to date. My guess is that SSMS might not show the hotfix level (only the SP level) and that your box is updated already.
Thanks,
Sam Lester (MSFT)
|||Really it may not be the easiest way for remote server.
Iа I were you (or if you were I), I would have preferred
SERVERPROPERTY function.
SELECT SERVERPROPERTY('productversion') AS ProductVersion,
SERVERPROPERTY ('ResourceVersion') As ResourceVersion ,
SERVERPROPERTY ('productlevel') AS ProductLevel,
SERVERPROPERTY ('edition') As Edition,
SERVERPROPERTY ('ResourceLastUpdateDateTime') As ResourceLastUpdateDateTime
Shows for me
ProductVersion ResourceVersion ProductLevel Edition ResourceLastUpdateDateTime
9.00.3054.00 9.00.3042 SP2 Developer Edition 2007-02-10 00:39:35.480
Friday, February 24, 2012
Data Types
Hello, Is there any document that tells you the pro/cons for using one data
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
DonIf you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nvarchar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one data
> type over another? I just recently upgraded from Access to SQL Server 2000.
> I used the Access upgrade wizard and was wondering how it determines when to
> assign types to ntext or text. Also, if the character in the text field are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
DonIf you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nvarchar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one data
> type over another? I just recently upgraded from Access to SQL Server 2000.
> I used the Access upgrade wizard and was wondering how it determines when to
> assign types to ntext or text. Also, if the character in the text field are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
Data Types
Hello, Is there any document that tells you the pro/cons for using one data
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
DonIf you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nv
archar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one dat
a
> type over another? I just recently upgraded from Access to SQL Server 200
0.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field a
re
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
DonIf you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nv
archar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one dat
a
> type over another? I just recently upgraded from Access to SQL Server 200
0.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field a
re
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
Data Types
Hello, Is there any document that tells you the pro/cons for using one data
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
Don
If you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nvarchar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one data
> type over another? I just recently upgraded from Access to SQL Server 2000.
> I used the Access upgrade wizard and was wondering how it determines when to
> assign types to ntext or text. Also, if the character in the text field are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>
|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com ...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com ...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
Don
If you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nvarchar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one data
> type over another? I just recently upgraded from Access to SQL Server 2000.
> I used the Access upgrade wizard and was wondering how it determines when to
> assign types to ntext or text. Also, if the character in the text field are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>
|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com ...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com ...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
Subscribe to:
Posts (Atom)