Database Compatibility Level

Hi,
I don't know if anyone can help but I'm trying to understand exactly how
setting database compatibility works. Has anyone seen a good explanation of
this (I've spent ages using google/msdn etc trying to find a detailed
description)?
I want to attach a couple of SQK 2K databases to a SQL Server 2005 cluster.
These databases are not supported by third parties on 2005. Would setting the
database compatibility to 80 (S2K) make sure they are run as a 2K database on
2005?
This would save me maintaining two installation of SQl server (2K and 2005).
I'm not sure I totally understand this functionality.
Thanks,
Jason.> I want to attach a couple of SQK 2K databases to a SQL Server 2005
> cluster.
> These databases are not supported by third parties on 2005. Would setting
> the
> database compatibility to 80 (S2K) make sure they are run as a 2K database
> on
> 2005?
That is a legal question that you should ask of your 3rd party. Even when
emulating a prior version, there is no guarantee that a particular query
will return the same results in the same way using the enhancements /
changes in the database engine. The compatibility setting will not affect
the system stored procedures - on which your 3rd party system may depend.
Even MS carefully constrains the compability claims. InBOL, MS says, "Sets
certain database behaviors to be compatible with the specified earlier
version of Microsoft® SQL ServerT. "|||Hi Jason
All databases available through a SQL 2005 server are SQL 2005 databases.
Setting a compatibility level just makes the database more compatible with
earlier versions, but they are still SQL 2005 databases.
The differences allows with the compatibility level are mainly how certain
keywords are used, and what object names are allowed. The architecture of
the database is a SQL 2005 architecture and is a 3rd party can't deal with
the architectural changes in SQL 2005, they won't be able to deal with a SQL
2005 database in a lower compatibility level.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"JayMG" <JayMG@.discussions.microsoft.com> wrote in message
news:E2D65A62-9F3D-454F-90CE-75A9C3E21F1B@.microsoft.com...
> Hi,
> I don't know if anyone can help but I'm trying to understand exactly how
> setting database compatibility works. Has anyone seen a good explanation
> of
> this (I've spent ages using google/msdn etc trying to find a detailed
> description)?
> I want to attach a couple of SQK 2K databases to a SQL Server 2005
> cluster.
> These databases are not supported by third parties on 2005. Would setting
> the
> database compatibility to 80 (S2K) make sure they are run as a 2K database
> on
> 2005?
> This would save me maintaining two installation of SQl server (2K and
> 2005).
> I'm not sure I totally understand this functionality.
> Thanks,
> Jason.
>

Database Collation setting

I have a SQL Server 2005 database which is set for

SQL_Latin1_General_Cp1_CI_AS collation (Actually when the instance was created it was set to this collation).....I have lot og objects tables,views, fks, col constraints , triggers, default etc defined....

I need to change the collation of the database and subsequently affecting the collation of every single column to SQL_Latin1_General_Cp1_CS_AS

Now when I run the following command i gives me lot of errors saying that

ALTER DATABASE <dbName> COLLATE SQL_Latin1_General_Cp1_CS_AS

The column constranint <xx> is dependent on collation .....

The funny part is that this col constraint is defined on a float column instead of varchar column.....why is it giving error when it does not have any collation associated with it?

What are the steps that I need to take before running the alter collation on the database? (mean disabling fks constraints, col constraints etc,,,)

Any pointers will be greatly appreciated.

Regards

Imtiaz

It's not just straigt forward to change collation. My best experience is to (generate) script the old database and change the collation when creating a new one. After this you can import the tables into the new database with correct collation. If you use tempdb and don't want to use the collate clause - you need to rebuild the master database and all the system databases.

Hope this helps.

DATA TYPE definitions

I am setting up columns in a data table. Where can I find the definitions and uses for all the items on the DATA TYPE drop down list such asntext andnchar? The data type list is also found under column properties general section.

SQL Server Books On Line is the best resource, so far. It shouldalready have been installed along with the database if you haveselected the option during installation. If not its a free downloadfrom MS website.
|||Thanks.
Yes, SQL is installed.
Will a book on SQL help me understand all of the other settings under column properties?
Are you talking about MS SQL?|||

When you install SQL Server you also installed the BOL(books online) the best documentation in the RDBMS(relational database management systems) business. The following are the data type and explanation based on my experience. Hope this helps.

Bigint, Int, Smallint, Tinyint and Bit(proprietry boolean) so three valued as ANSI SQL boolean true, fasle and Null(unknown) not an empty string.

Decimal and Numeric
You can set precision and scale.

Money and Smallmoney
There are known rounding issues going back to 1999 that I know better to use Decimal or Numeric

Float and Real floating point data types used for only T-SQL quantitative functions that are based on Float but cast or convert results to Decimal or Numeric to store.

Char fixed length ASCII character 8000 max, Varchar varaible length ASCII character 8000 max can overflow and Text can grow to 2gig in size ASCII text.

NChar fixed length Unicode 4000 max, NVarchar unicode variable length character 4000 max, NText unicode text can grow to 1gig in size because unicode types are multibytes.

Binary fixed length binary data 8000 max
Varbinary variable length binary data 8000 max
Image variable length binary data can grow to 2gig in size also known as BLOB(binary large objects)

Other Data Types
Cursor
A reference to a cursor.

Sql_variant

A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.

Table

A special data type used to store a result set for later processing .

Timestamp
A derived data type used by SQL Server internally cannot move with your data but you can creat fake one with datetime and trigger. A database-wide unique number that gets updated every time a row gets updated.

Uniqueidentifier/NewID
16 bytes data type to be used carefully

A globally unique identifier (GUID).

IDENTITY is a property to the column using it.

|||

Thank you for your time and info. That is helpful info.
Where can I find the books online?
I have installed Visual Web Developer Express 05 and SQL Server 05.

Thanks

|||

Hi
The links below the first holds a copy of the data type page in SQL Server 2000 and download the SQL Server 2005 BOL(books online) from the second link. Hope this helps.
http://www.mssqlcity.com/Articles/General/choose_data_type.htm

http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

|||

Yes, this helped a lot. Thanks.
I just downloaded the BOL for SQL 05.
Is there a list of other BOL for other subjects? (Especially Visual Web Developer Express 05)
I searched the MS download site, but I did not find any other books.

|||

Try the link below to download the .NET 2.0 SDK software development kit and take the visual web developer tour. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=FE6F2099-B7B4-4F47-A244-C96D69C35DEC&displaylang=en

http://msdn.microsoft.com/vstudio/express/vwd/default.aspx

|||

HAving a few problems with BLOB and Oracle, when creating new automated DataSet, VS2005 believes those are 4000 of length.

http://alpascual.com/blog/al/archive/2006/07/25/238.aspx

Any advice?

Cheers

Al

|||

The links below covers all the Oracle data types and the .NET data types with code sample to get you started. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oraclelob.aspx

http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracletype.aspx