Concatenatation with NULL

I am changing the setting od my database to put concatenate null yields null to off.....the following are the statements i run....

exec sp_dboption 'Solumina','concat null yields null','false'

SELECT 'abc' + NULL

I expect 'abc' to be returned after this....But not so..I get NULL

Can any one tell me this setting has to be changed at the connection level. if so, why has this been provided as a db option.

the following works....

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'abc' + NULL

abc

-

ODBC and SQL Query Analyzer will turn this ON by default so you need to explicitly turn the behavior OFF if you are using either of these connection mechanisms

Run this: select databaseproperty(''Solumina', 'IsNullConcat')

is the result 0? then it's set to OFF, however you have to change it from QA to make it wok in a query window

go to Tools-->options-->Connection Properties and uncheck set concat_null_yields_null

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Complicated query - select based on value

SQL2K on W2Kserver

I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)

The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
contact does not have that type then select types ('MN30D843J2',
'SC93JDL39D'). (Context - the current query selects two standard
address types "Main" and "Secondary"; we've added new data and now have
types "Alternate Main" and "Alternate Secondary". If the Contact has
Alternate addresses, I need to select those; if not, I need to select
the standard addresses. There are other address types in use, so I
must specify which types to select.)

Can anyone point me in the right direction?

Thanks very much! jamileh

CREATE TABLE [CONTACTS] (
[CONTACT_X] [char] (10),
[LONGNAME] [char] (75),
[ACTIVE] [bit])

CREATE TABLE [CONTACTADDRESSES] (
[CONTACT_X] [char] (10),
[ADDRESS_X] [char] (10),
[ADDRESSTYPE_REFX] [char] (10),
[ACTIVE] [bit])

CREATE TABLE [ADDRESSES] (
[ADDRESS_X] [char] (10),
[ADDRESSLINE1] [char] (60),
[ADDRESSLINE2] [char] (60),
[CITY] [char] (20),
[STATE] [char] (2),
[ZIPCODE] [char] (11),
[PHONE] [char] (10))

CREATE TABLE [REFERENCETABLE] (
[REFERENCETABLE_X] [char] (10),
[ADDRESS_X] [char] (10),
[DESCRIPTION] [char] (60))

CREATE TABLE [MASTERTABLE] (
[CONTACT_X] [char] (10),
[RECORDTYPE] [char] (1),
[ACTIVE] [bit])

CREATE VIEW vw_CONTACTInfo_ListLoc
AS
SELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,
CONTACTADDRESSES.ADDRESSTYPE_REFX,
Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,
CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,
ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,
ADDRESSES.ZIPCODE, ADDRESSES.PHONE
FROM CONTACTS INNER JOIN CONTACTADDRESSES ON
CONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOIN
ADDRESSES ON CONTACTADDRESSES.ADDRESS_X =
ADDRESSES.ADDRESS_X
INNER JOIN REFERENCETABLE Type_REFERENCETABLE ON
CONTACTADDRESSES.ADDRESSTYPE_REFX =
Type_REFERENCETABLE.REFERENCETABLE_X
WHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFX
IN
('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =
1)) AND
(CONTACTS.CONTACT_X IN
(SELECT CONTACT_X FROM MASTERTABLE WHERE
ACTIVE = 1 AND RECORDTYPE = 'E'))"jqq" <jqq@.myrealbox.com> wrote in message
news:1120839746.620891.96250@.f14g2000cwb.googlegro ups.com...
> SQL2K on W2Kserver
> I need some help revamping a rather complicated query. I've given the
> table and existing query information below. (FYI, changing the
> database structure is right out.)
> The current query lists addresses with two particular types
> ('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
> contact for address type 'AM39DK3KD9' and then (2) if the contact has
> type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
> contact does not have that type then select types ('MN30D843J2',
> 'SC93JDL39D'). (Context - the current query selects two standard
> address types "Main" and "Secondary"; we've added new data and now have
> types "Alternate Main" and "Alternate Secondary". If the Contact has
> Alternate addresses, I need to select those; if not, I need to select
> the standard addresses. There are other address types in use, so I
> must specify which types to select.)
> Can anyone point me in the right direction?
> Thanks very much! jamileh

<snip
The short answer is probably to see CASE in Books Online. If you need more
information, I suggest you provide some INSERT statements for sample data,
and also the output you expect - it's not very clear (to me) exactly what
your query should return.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||If the Contact has (INNER JOIN)
Alternate addresses, I need to select those;

UNION ALL
if not, NOT EXISTS(...)

I need to select
the standard addresses.|||Without better specs, this is hard. The tables had no keys; the names
of the data elements are awful, you even put physical storage and usage
into the names! You are using bit flags in SQL. There does not seem to
be any consistent design here. Clraning it up a bit, I got this:

CREATE TABLE Contacts
(contact_id CHAR(10) NOT NULL PRIMARY KEY,
long_name CHAR(75) NOT NULL);

CREATE TABLE Addresses
(address_id CHAR(10) NOT NULL PRIMARY KEY,
address_line1 CHAR(35) NOT NULL, -- usps lengths
address_line2 CHAR(35) NOT NULL,
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(9) NOT NULL,
phone_nbr CHAR(10) NOT NULL));

Your codes belong to the relationship, and not in their own tables,
something more like this

CREATE TABLE ContactAddresses
(contact_id CHAR(10) NOT NULL
REFERENCES Contacts (contact_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_id CHAR(10) NOT NULL
REFERENCES Addresses (address_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_type INTEGER NOT NULL, -- see suggestion below
PRIMARY KEY (contact_id, address_id, address_type)
contact_status CHAR(3) DEFAULT 'act' NOT NULL
CHECK (contact_status IN ('act', 'old', ..));

Views should be kept simple so they can be used in many place. And
unless they dela wiyth a Volkswagen, you do not prefix them with "vw_"
:)

CREATE VIEW ContactInfo (contact_id, long_name,
address_type, address_id,
address_line1, address_line2,
city_name, state_code, zip_code,
phone_nbr)
AS
SELECT C.contact_id, C.long_name,
CA.address_type, CA.address_id,
A.address_line1, A.address_line2,
A.city_name, A.state_code, A.zip_code,
A.phone_nbr
FROM Contacts AS C,
ContactAddresses AS CA,
Addresses AS A
WHERE CA.contact_id = C.contact_id
AND CA.address_id = A.address_id
AND CA.address_type BETWEEN 100 AND 299;

>> the current query selects two standard address types "Main" and "Secondary"; we've added new data and now have types "Alternate Main" and "Alternate Secondary". If the Contact has Alternate addresses, I need to select those; if not, I need to select the standard addresses. <<

You need a better encoding scheme than those awful ten-letter
nightmares. I read your narrative as meaning one contact can have only
one address of each type. Here is a hierarchical encoding suggestion,
with some room for growth.

100-199 = Main Address
110-119 = Alternative Main Address
200-299 = Secondary Address
210-219 = Alternative Secondary Address

The query would be something like this:

SELECT DISTINCT I1.*
FROM ContactInfo AS I1
WHERE address_type IN (200, 210) -- has secondary address
OR (address_type IN (100, 110) -- has main address
AND NOT EXISTS
(SELECT *
FROM ContactInfo AS I2
WHERE address_type IN (200, 210) -- no secondary address
AND I1.contact_id = I2.contact_id
AND I1.address_id = I2.address_id));|||My apologies for leaving out the data, I didn't want to get too long in
my original post if it wasn't needed. Please see below (including one
fix on a table).

So, the current query will pull John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "standard" addresses
on Main, Second and Third streets.

The results I need would give John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
on Fifth and Sixth streets.

I've used CASE, but not to select multiple records based on one field.
I'm not sure how to make that work and I can't find anything in BOL to
explain it.

Thanks.

ALTER TABLE [REFERENCETABLE] DROP COLUMN [ADDRESS_X]

INSERT [CONTACTS] VALUES ('A1','John Smith',1)
INSERT [CONTACTS] VALUES ('B2','Frank Doe',1)
INSERT [CONTACTS] VALUES ('C3','Jane Jones',1)
INSERT [CONTACTS] VALUES ('D4','Susan Roe',0)
INSERT [CONTACTS] VALUES ('E5','George Brown',1)

INSERT [CONTACTADDRESSES] VALUES ('A1','F1','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','G2','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','H3','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','I4','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','J5','AM39DK3KD9',0)
INSERT [CONTACTADDRESSES] VALUES ('B2','K6','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','L7','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','M8','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','N9','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','O0','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','P1','ASKD943KDI',1)
INSERT [CONTACTADDRESSES] VALUES ('C3','Q2','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('D4','R3','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('E5','S4','AM39DK3KD9',1)

INSERT [ADDRESSES] VALUES ('F1','123 Main
St','','Anytown','PA','12345','5074951548')
INSERT [ADDRESSES] VALUES ('G2','456 Second St','Apt
9','Anytown','PA','45678','5074328548')
INSERT [ADDRESSES] VALUES ('H3','789 Third
St','','Anytown','PA','45678','5074321111')
INSERT [ADDRESSES] VALUES ('I4','987 Fourth
St','','Anytown','PA','12345','5074959999')
INSERT [ADDRESSES] VALUES ('J5','654 Fifth
St','','Anytown','PA','12345','5074955555')
INSERT [ADDRESSES] VALUES ('K6','1 Main
St','','Somewhere','UT','87654','2426831234')
INSERT [ADDRESSES] VALUES ('L7','2 Second St','Suite
600','Somewhere','UT','87654','2426835678')
INSERT [ADDRESSES] VALUES ('M8','3 Third
St','','Somewhere','UT','87654','2426839876')
INSERT [ADDRESSES] VALUES ('N9','4 Fourth
St','','Somewhere','UT','87654','2426835432')
INSERT [ADDRESSES] VALUES ('O0','5 Fifth
St','','Somewhere','UT','87654','2426831111')
INSERT [ADDRESSES] VALUES ('P1','6 Sixth
St','','Somewhere','UT','87654','2426839999')
INSERT [ADDRESSES] VALUES ('Q2','123 NoGood
St','','Nowhere','AK','98765','9051875135')
INSERT [ADDRESSES] VALUES ('R3','456 NotMe
St','','Nonesuch','CA','43210','7631584625')
INSERT [ADDRESSES] VALUES ('S4','789 UhOh
St','','Noway','GA','36847','6427892462')

INSERT [REFERENCETABLE] VALUES ('MN30D843J2','Standard Main')
INSERT [REFERENCETABLE] VALUES ('SC93JDL39D','Standard Secondary')
INSERT [REFERENCETABLE] VALUES ('AM39DK3KD9','Alternate Main')
INSERT [REFERENCETABLE] VALUES ('ASKD943KDI','Alternate Secondary')
INSERT [REFERENCETABLE] VALUES ('BL2309DD3L','Billing Only')

INSERT [MASTERTABLE] VALUES ('A1','E',1)
INSERT [MASTERTABLE] VALUES ('B2','E',1)
INSERT [MASTERTABLE] VALUES ('C3','N',1)
INSERT [MASTERTABLE] VALUES ('D4','E',1)
INSERT [MASTERTABLE] VALUES ('E5','E',0)|||As I said, changing the database structure is right out. I didn't
build the beastie - it's the backend for a proprietary application.
The only thing I can do is pull data.

These views are for the exact purpose of selecting very specific sets
of data to provide to some websites. This one's not half bad, you
should see some of the others!

I did leave out PKs, etc. - sorry. The tables are actually much
bigger & I was trying to just pull the needed info for simplicity. If
there's something specific that would help, please let me know & I'll
post an update. In general, you're right and the "tablename_K" columns
are PKs and FKs.

As you can see in my last post (with data), one contact can have
multiple addresses of each type and I need to pull all addresses of the
correct types.

Any rate, thanks for the advice! j

p.s. What's wrong with bit flags?|||I read this several times without any clue what you meant, but I think
it's beginning to permeate into my poor, bleeding braincells. I'll see
what I can come up with. Thanks!|||jqq (jqq@.myrealbox.com) writes:
> p.s. What's wrong with bit flags?

Nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||jqq (jqq@.myrealbox.com) writes:
> My apologies for leaving out the data, I didn't want to get too long in
> my original post if it wasn't needed. Please see below (including one
> fix on a table).
> So, the current query will pull John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "standard" addresses
> on Main, Second and Third streets.
> The results I need would give John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
> on Fifth and Sixth streets.

This query gives the result described above, but does not match your
description in the first post. But maybe you simply messed up on all
these terrible 10-letter codes when you composed the sample data.

SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('MN30D843J2', 'SC93JDL39D')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')
AND NOT EXISTS (SELECT *
FROM CONTACTADDRESSES CA1
WHERE C.CONTACT_X = CA1.CONTACT_X
AND CA1.ADDRESSTYPE_REFX IN ('ASKD943KDI'))
UNION ALL
SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('AM39DK3KD9', 'ASKD943KDI')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||They are a proprietary, low level data type. The design is the way we
handled things with punch cards in the old days. Flags are usually
computed columns. It is usually better to invent a status code which
can be extended, or to capture the date of an event, etc.|||Wow, I went off to read up on EXISTS and came back to find the query
all done!

That's exactly what I needed *and* I learned a new trick.

Thanks very much!!

Complex copy routine, mulitple tables and changing GUIDs

hello,

I have several tables that have guids as their primary keys and the tables are related as follows:

Table1 - primary key =ServiceNo (Guid), Filter Key =CampaignNo

Table2 - primary key = CostBasisNo (Guid), Foreign Key =ServiceNo (from Table1)

Table3 - primary key = UserId, Foreign Key =ServiceNo (from table1)

Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)

what I need to do is copy all records from Table1 where CampaignNo = @.CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.

The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1

This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's forSourceServiceNo and the relatedMemberServiceNo which all would have changed.

I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.

regards

DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

INSERT INTO Table1 (CampaignNo) -- and other columns as well, I guess
SELECT @.newCampaignNo FROM Table1 WHERE ServiceNo=@.oldCampaignNo

INSERT INTO Table2 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

INSERT INTO Table3 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

I suppose table4 will be quite easy as well, but you need to describe where the ServiceNo:s come from.

|||

Hi gunteman,

thank you for replying, I'm not sure that will do what I want.

In my db the CampaignNo will never equal the ServiceNo.

Maybe if I try and explain the problem another way and only use two related tables

Lets say I have:

Table1 where the primary key =ServiceNo, and other attributes including anon primary key = CampaignNo

My first task is to copy all the rows from Table1 where an input parameter called @.CampaignNo = CampaignNo,

So my statement would be something like:

INSERT INTO Table1 (ServiceNo, attribute1, attribute2,CampaignNo)
(SELECTNEWID(), attribute1, attribute2, @.CampaignNo from Table1 WHERE
CampaignNo = @.CampaignNo)

NEWID() of course will create a new uniqueidentifier.

now this works fine, I've tried this out using a temporary table and all is good.

my problem comes when I need to copy the contents of the related table, say Table2,

Lets say Table2 hasCostBasisNo (primary key), attribute1, attribute2,ServiceNo (foreign key from Table1)

now I need to copy rows from Table2 but where the ServiceNo = the old ServiceNo from Table1 and I guess this is where the list of service numbers again = @.CampaignNo.

I guess I could say something like

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2, ServiceNo)
(SELECTNEWID(), attribute1, attribute2, ServiceNo from Table2 WHERE
?)

and this is where I hit the problem, how do I write the INSERT statement for Table2 to copy it's own records but only where the ServiceNo = the old ServiceNo from Table1 and then insert the associated new ServiceNo.

hope that's made the issue a little clearer, the other tables don't really matter cos if I can solve this the others will follow.

regards

|||DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

DECLARE @.conversion TABLE
(
oldServiceNo uniqueidentifier,
newServiceNo uniqueidentifier
)

INSERT INTO @.conversion (oldServiceNo,newServiceNo)
SELECT ServiceNo,newid() FROM Table1 WHERE CampaignNo=@.oldCampaignNo

INSERT INTO Table1 (ServiceNo,attribute1, attribute2, CampaignNo)
SELECT newServiceNo, attribute1, attribute2, @.newCampaignNo FROM @.conversion c,Table1 t WHERE c.oldServiceNo=t.ServiceNo

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2,ServiceNo)
SELECT newid(), attribute1, attribute2, newServiceNo FROM Table1 @.conversion c,Table2 t WHERE c.oldServiceNo=t.ServiceNo

..and so on.|||

Yes, that does the job very nicely indeed - thank you, it's also given me a pointer to other tables I need to copy the contents of.

nice one!

Compatibility level ?

Are there any downsides to changing it from 80 to 90 for a given database ?
Hi Rob
It depends...
Does your database have any objects with names that are now reserved
keywords in compatibility level 90?
EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
Does any of your code use the *= or =* syntax for outer joins?
Does any of your code update a view that was defined WITH NOCHECK that
includes a TOP?
If your database includes any constructs that is allowed in 80 compatibility
but is not allowed in 90, then you will have problems. Otherwise, you won't.
You can find the full list in the Books Online if you look up
sp_dbcmptlevel.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Rob" <robc1@.yahoo.com> wrote in message
news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com. ..
> Are there any downsides to changing it from 80 to 90 for a given database
> ?
>
|||Hi Kalen,
Correct me if I'm wrong, because this is how I explain compatibility level
when teaching or presenting: the compatibility only tells the query engine
how to interpret TSQL code. Thus, at 80 no new features will work, nor syntax
changes in 90.
I'm not missing something, am I?
"Kalen Delaney" wrote:

> Hi Rob
> It depends...
> Does your database have any objects with names that are now reserved
> keywords in compatibility level 90?
> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
> Does any of your code use the *= or =* syntax for outer joins?
> Does any of your code update a view that was defined WITH NOCHECK that
> includes a TOP?
> If your database includes any constructs that is allowed in 80 compatibility
> but is not allowed in 90, then you will have problems. Otherwise, you won't.
> You can find the full list in the Books Online if you look up
> sp_dbcmptlevel.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com. ..
>
>
|||This is an overgeneralization. Compatibility level is mainly concerned with
interpreting TSQL, but to say NO new features will work is not true at all.
Did you look at the page on sp_dbcmptlevel in BOL?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...[vbcol=seagreen]
> Hi Kalen,
> Correct me if I'm wrong, because this is how I explain compatibility level
> when teaching or presenting: the compatibility only tells the query engine
> how to interpret TSQL code. Thus, at 80 no new features will work, nor
> syntax
> changes in 90.
> I'm not missing something, am I?
> "Kalen Delaney" wrote:
|||Thanks, I'll hone my explanation.
"Kalen Delaney" wrote:

> This is an overgeneralization. Compatibility level is mainly concerned with
> interpreting TSQL, but to say NO new features will work is not true at all.
> Did you look at the page on sp_dbcmptlevel in BOL?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
> in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...
>
>

Compatibility level ?

Are there any downsides to changing it from 80 to 90 for a given database ?Hi Rob
It depends...
Does your database have any objects with names that are now reserved
keywords in compatibility level 90?
EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
Does any of your code use the *= or =* syntax for outer joins?
Does any of your code update a view that was defined WITH NOCHECK that
includes a TOP?
If your database includes any constructs that is allowed in 80 compatibility
but is not allowed in 90, then you will have problems. Otherwise, you won't.
You can find the full list in the Books Online if you look up
sp_dbcmptlevel.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Rob" <robc1@.yahoo.com> wrote in message
news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.co
mcast.com...
> Are there any downsides to changing it from 80 to 90 for a given database
> ?
>|||Hi Kalen,
Correct me if I'm wrong, because this is how I explain compatibility level
when teaching or presenting: the compatibility only tells the query engine
how to interpret TSQL code. Thus, at 80 no new features will work, nor synta
x
changes in 90.
I'm not missing something, am I?
"Kalen Delaney" wrote:

> Hi Rob
> It depends...
> Does your database have any objects with names that are now reserved
> keywords in compatibility level 90?
> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
> Does any of your code use the *= or =* syntax for outer joins?
> Does any of your code update a view that was defined WITH NOCHECK that
> includes a TOP?
> If your database includes any constructs that is allowed in 80 compatibili
ty
> but is not allowed in 90, then you will have problems. Otherwise, you won'
t.
> You can find the full list in the Books Online if you look up
> sp_dbcmptlevel.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.co
mcast.com...
>
>|||This is an overgeneralization. Compatibility level is mainly concerned with
interpreting TSQL, but to say NO new features will work is not true at all.
Did you look at the page on sp_dbcmptlevel in BOL?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...[vbcol=seagreen]
> Hi Kalen,
> Correct me if I'm wrong, because this is how I explain compatibility level
> when teaching or presenting: the compatibility only tells the query engine
> how to interpret TSQL code. Thus, at 80 no new features will work, nor
> syntax
> changes in 90.
> I'm not missing something, am I?
> "Kalen Delaney" wrote:
>|||Thanks, I'll hone my explanation.
"Kalen Delaney" wrote:

> This is an overgeneralization. Compatibility level is mainly concerned wit
h
> interpreting TSQL, but to say NO new features will work is not true at all
.
> Did you look at the page on sp_dbcmptlevel in BOL?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
> in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...
>
>

Compatibility level ?

Are there any downsides to changing it from 80 to 90 for a given database ?Hi Rob
It depends...
Does your database have any objects with names that are now reserved
keywords in compatibility level 90?
EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
Does any of your code use the *= or =* syntax for outer joins?
Does any of your code update a view that was defined WITH NOCHECK that
includes a TOP?
If your database includes any constructs that is allowed in 80 compatibility
but is not allowed in 90, then you will have problems. Otherwise, you won't.
You can find the full list in the Books Online if you look up
sp_dbcmptlevel.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Rob" <robc1@.yahoo.com> wrote in message
news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
> Are there any downsides to changing it from 80 to 90 for a given database
> ?
>|||Hi Kalen,
Correct me if I'm wrong, because this is how I explain compatibility level
when teaching or presenting: the compatibility only tells the query engine
how to interpret TSQL code. Thus, at 80 no new features will work, nor syntax
changes in 90.
I'm not missing something, am I?
"Kalen Delaney" wrote:
> Hi Rob
> It depends...
> Does your database have any objects with names that are now reserved
> keywords in compatibility level 90?
> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
> Does any of your code use the *= or =* syntax for outer joins?
> Does any of your code update a view that was defined WITH NOCHECK that
> includes a TOP?
> If your database includes any constructs that is allowed in 80 compatibility
> but is not allowed in 90, then you will have problems. Otherwise, you won't.
> You can find the full list in the Books Online if you look up
> sp_dbcmptlevel.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
> > Are there any downsides to changing it from 80 to 90 for a given database
> > ?
> >
>
>|||This is an overgeneralization. Compatibility level is mainly concerned with
interpreting TSQL, but to say NO new features will work is not true at all.
Did you look at the page on sp_dbcmptlevel in BOL?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...
> Hi Kalen,
> Correct me if I'm wrong, because this is how I explain compatibility level
> when teaching or presenting: the compatibility only tells the query engine
> how to interpret TSQL code. Thus, at 80 no new features will work, nor
> syntax
> changes in 90.
> I'm not missing something, am I?
> "Kalen Delaney" wrote:
>> Hi Rob
>> It depends...
>> Does your database have any objects with names that are now reserved
>> keywords in compatibility level 90?
>> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
>> Does any of your code use the *= or =* syntax for outer joins?
>> Does any of your code update a view that was defined WITH NOCHECK that
>> includes a TOP?
>> If your database includes any constructs that is allowed in 80
>> compatibility
>> but is not allowed in 90, then you will have problems. Otherwise, you
>> won't.
>> You can find the full list in the Books Online if you look up
>> sp_dbcmptlevel.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
>> > Are there any downsides to changing it from 80 to 90 for a given
>> > database
>> > ?
>> >
>>|||Thanks, I'll hone my explanation.
"Kalen Delaney" wrote:
> This is an overgeneralization. Compatibility level is mainly concerned with
> interpreting TSQL, but to say NO new features will work is not true at all.
> Did you look at the page on sp_dbcmptlevel in BOL?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
> in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...
> > Hi Kalen,
> >
> > Correct me if I'm wrong, because this is how I explain compatibility level
> > when teaching or presenting: the compatibility only tells the query engine
> > how to interpret TSQL code. Thus, at 80 no new features will work, nor
> > syntax
> > changes in 90.
> >
> > I'm not missing something, am I?
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Rob
> >>
> >> It depends...
> >>
> >> Does your database have any objects with names that are now reserved
> >> keywords in compatibility level 90?
> >> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
> >>
> >> Does any of your code use the *= or =* syntax for outer joins?
> >>
> >> Does any of your code update a view that was defined WITH NOCHECK that
> >> includes a TOP?
> >>
> >> If your database includes any constructs that is allowed in 80
> >> compatibility
> >> but is not allowed in 90, then you will have problems. Otherwise, you
> >> won't.
> >>
> >> You can find the full list in the Books Online if you look up
> >> sp_dbcmptlevel.
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Rob" <robc1@.yahoo.com> wrote in message
> >> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
> >> > Are there any downsides to changing it from 80 to 90 for a given
> >> > database
> >> > ?
> >> >
> >>
> >>
> >>
>
>