Is this T-SQL dangerous/not reliable

We are trying to create a string of column names for a
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)

Is This Possible? Defaulting of a Dimension Atrribute

I have a dimension with one visible attribute, let's call it "Status." This attribute is binded to one column from a forecast fact table that stores monthly snapshots. This column simply has a 'Yes' or 'No' value for every record in the fact table.

For a specific customer and product combination in this fact table, this "Status" column could have started off having a value of 'No' a few months ago, and now has 'Yes' for the current month. In other words, it can have both possible values.

For example, let's assume the following forecast data for the Customer A and Product B combination:

Month | Forecast $ | Status
-
November 2006 | $5 | 'No'
December 2006 | $10 | 'No'
January 2007 | $10 | 'Yes'

This "Status" attribute is meant to be used at the Page Field level in Excel, and users would like to be able to see customer forecast data based on the "Status" being filtered for either 'Yes' or 'No'.

Now, all of our measures in the cube have the following MDX ((TAIL(EXISTING [Date].[Month].MEMBERS).ITEM(0).ITEM(0), [Measure]). This defaults the measures to the current month in a report unless a time frame, whether year(s) and/or month(s), is explicityly specified or filtered at the Page Field level.

Assuming a timeframe is not explicitly specified at the Page Field level and a user is looking at the forecast data for Customer A for Product B, this customer will show up regardless of what the "Status" Page Field is filtered for. I believe this is the case because it historically has had a record associated with both 'No' and 'Yes'. What does differ hough, is whether or not Forecast $ will populate or not.

For example, Status = 'Yes', then Forecast $ will show $10. If Status = 'No', then Forecast $ will now be NULL. Ideally, if Status = 'No', this customer would not even show up for the current month.

So is this possible? Hopefully what I'm asking makes sense.

Thanks!From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.|||

Mosha Pasumansky wrote:

From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like. Would it be easier if you took a look at my solution file to see what I may have missed? I've tried this numerous times with no luck.

Once I get this behavior resolved, I'll follow up with you with the MDX.

Thanks!|||

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

|||

Mosha Pasumansky wrote:

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

My fault. My mind must have been elsewhere when I posted. I did as you suggested with no luck. I set it to false from the default of true.

I did not follow your suggestion on the LastChild semiadditve aggregation type yet, as I wanted to focus on the above since I'm not too familiar with this LastChild thing. Now that I think about it, I assume this wouldn't be available to me in Standard edition? We're running Standard Edition.|||In fact, LastChild is the only semiadditive aggregation type available in Standard Edition - so you got lucky :)|||Anyone have any other thoughts? Is what I'm looking for even possible?

The IsAggregatable property when set to false, simply removed the 'All' member and didn't do what I am seeking.

Is this possible?

hey guys,

I have a column called Error Count, which display the the error count values (Fields!error_count.value) from the dataset. And assume the report has some parameters.I want to change the values of this column by comparing the parameters value that the user specified with the values in the database. For instance, If sessions.timestamp == parameters!date.value then do something, where timestamp is a field in the sessions table and parameters!date.value is the the parameter value.

Please let me know if anybody came across this kind of situation and how you solve it.

Any idea is appreciated

Sincerely

Amde

Two approaches come to mind for accomplishing this.

The first way is to use a stored procedure, where you pass the report parameters to the stored procedure. In the stored procedure you have complete control over what values get put in the dataset.

The second approach would be to use a custom data processing extension. You could change the contents of the .Net data set returned by your sql query before returning it to reporting services. Or, another way would be to change the values on the fly when reporting services asks for a particular field from the data set.

I suggest using the first way, as it will require less code and infrastructure complexity.

is this possible

hey all,
i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.
How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record
can i do this in one stored procedure?
thanks,
rodcharWhy not use an IDENTITY column?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||Again provide DDL...
Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))
Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId
BTW, this does have issues in a multi-user environment. If two people were t
o
execute this function at exactly the same time, they'll get the same answer
and
thus a collision. A better way would be to make a small table that stores th
e
last value used.
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same
answer and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
..which would have the same issue -- what would stop two readers from
getting the value simultaneously?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||The problem is that this is an existing table in production.
"Adam Machanic" wrote:

> Why not use an IDENTITY column?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> 2
>
>|||Just increase Isolation Leel to Repeatable Read (or Serializeable) to preven
t
this issue from arising...
"Thomas" wrote:

> Again provide DDL...
> Create Table (RecordId Int, SequenceId Int
> , Constraint Primary Key (RecordId, SequenceId))
> Insert Table(RecordId, SequenceId)
> Select RecordId, Max(SequenceId) + 1
> From Table
> Group By RecordId
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same answe
r and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
>
> Thomas
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
>
>|||If you can withstand the entire table being locked during the insert process
,
this would also be a viable choice.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to prev
ent
> this issue from arising...
> "Thomas" wrote:
>|||If you use a small table that stores the next value, you can lock the table
and
increment the "next" value. In essence, serializing the retrieval of the nex
t id
value. However, it does mean you may get gaps.
Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OI6wTkcQFHA.1884@.TK2MSFTNGP15.phx.gbl...
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> to
> answer and
> the
> ...which would have the same issue -- what would stop two readers from
> getting the value simultaneously?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to
prevent
> this issue from arising...
How would that prevent issues?
QA Window 1:
--
use tempdb
go
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
QA Window 2:
--
use tempdb
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
Serializable blocks only if writes have taken place.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
> "Thomas" wrote:
>
were to
answer and
stores the
is a 2|||Adam,
Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Wait here while you run Qry WIndow 2 --
Commit Tran
-- ******************************
--Query Window 2
--
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- ---
"Adam Machanic" wrote:

> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> prevent
> How would that prevent issues?
> QA Window 1:
> --
> use tempdb
> go
> create table x(id int)
> go
> insert x values (1)
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> QA Window 2:
> --
> use tempdb
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> Serializable blocks only if writes have taken place.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> were to
> answer and
> stores the
> is a 2
>
>

Is this a BUG

SQL Server 2005 SP2.
The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
2000 or 2005. The first column (cust_code) displays correct in all the
versions but the second column (related_info2 - That has all the concatenated
stuff) displays different. In 7.0, it brings all the values and if the column
has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
the rest of the columns data.
In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
NULL value.
Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
select customer.cust_code,
customer.bus_name
+ ' '
+ customer.address3
+ ' Direct: '
+ customer.phone_direct
+ ' 800: '
+ customer.phone_800
related_info2
from customer
order by customer.cust_code
and here is the DDL:
CREATE TABLE [dbo].[customer] (
[cust_code] [int] NOT NULL ,
[bus_name] [varchar] (40) NULL ,
[address3] [varchar] (30) NULL ,
[phone_800] [varchar] (30) NULL ,
[phone_direct] [varchar] (30) NULL
) ON [PRIMARY]
GO
Thanks.
No, this is not a bug. Take a look at the CONCAT_NULL_YIELDS_NULL option in
Books Online. Probably it is set OFF on your SQL Server 7 database, or the
client API that connects sets it to off. You can check it like this:
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')
On SQL Server 2000 & 2005 it is ON by default, and most new client libraries
will set it to ON, which overrides the database settings.
You can easily get the same result by using COALESCE or ISNULL:
SELECT customer.cust_code,
COALESCE(customer.bus_name, '')
+ ' '
+ COALESCE(customer.address3, '')
+ ' Direct: '
+ COALESCE(customer.phone_direct, '')
+ ' 800: '
+ COALESCE(customer.phone_800, '')
AS related_info2
FROM customer
ORDER BY customer.cust_code
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
"Tibor Karaszi" wrote:

> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see ALTER
> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest you
> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance you
> can use ISNULL for each nullable column and convert to empty string if value is NULL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
>
|||My bet.........Sorry..........COALESCE works. I just forgot to apply it
to whole fields in the select statement and not only the sub select.
Thanks again Tibor & Plamen.
"Tibor Karaszi" wrote:

> You can't (assuming you refer to the session setting) since this isn't a sticky option (like SET
> ANSI_NULLS). So, either use the database option or adjust your code to work regardless.
>
> You lost me there. Can you explain with an example?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8D646949-DFA0-43FA-9AEA-B2D558AFC71C@.microsoft.com...
>

Is this a BUG

SQL Server 2005 SP2.
The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
2000 or 2005. The first column (cust_code) displays correct in all the
versions but the second column (related_info2 - That has all the concatenated
stuff) displays different. In 7.0, it brings all the values and if the column
has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
the rest of the columns data.
In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
NULL value.
Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
select customer.cust_code,
customer.bus_name
+ ' '
+ customer.address3
+ ' Direct: '
+ customer.phone_direct
+ ' 800: '
+ customer.phone_800
related_info2
from customer
order by customer.cust_code
and here is the DDL:
CREATE TABLE [dbo].[customer] (
[cust_code] [int] NOT NULL ,
[bus_name] [varchar] (40) NULL ,
[address3] [varchar] (30) NULL ,
[phone_800] [varchar] (30) NULL ,
[phone_direct] [varchar] (30) NULL
) ON [PRIMARY]
GO
Thanks.Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see ALTER
DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest you
adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance you
can use ISNULL for each nullable column and convert to empty string if value is NULL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
> SQL Server 2005 SP2.
> The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
> 2000 or 2005. The first column (cust_code) displays correct in all the
> versions but the second column (related_info2 - That has all the concatenated
> stuff) displays different. In 7.0, it brings all the values and if the column
> has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
> the rest of the columns data.
> In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
> NULL value.
> Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
>
> select customer.cust_code,
> customer.bus_name
> + ' '
> + customer.address3
> + ' Direct: '
> + customer.phone_direct
> + ' 800: '
> + customer.phone_800
> related_info2
> from customer
> order by customer.cust_code
> and here is the DDL:
> CREATE TABLE [dbo].[customer] (
> [cust_code] [int] NOT NULL ,
> [bus_name] [varchar] (40) NULL ,
> [address3] [varchar] (30) NULL ,
> [phone_800] [varchar] (30) NULL ,
> [phone_direct] [varchar] (30) NULL
> ) ON [PRIMARY]
> GO
>
> Thanks.|||No, this is not a bug. Take a look at the CONCAT_NULL_YIELDS_NULL option in
Books Online. Probably it is set OFF on your SQL Server 7 database, or the
client API that connects sets it to off. You can check it like this:
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')
On SQL Server 2000 & 2005 it is ON by default, and most new client libraries
will set it to ON, which overrides the database settings.
You can easily get the same result by using COALESCE or ISNULL:
SELECT customer.cust_code,
COALESCE(customer.bus_name, '')
+ ' '
+ COALESCE(customer.address3, '')
+ ' Direct: '
+ COALESCE(customer.phone_direct, '')
+ ' 800: '
+ COALESCE(customer.phone_800, '')
AS related_info2
FROM customer
ORDER BY customer.cust_code
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
"Tibor Karaszi" wrote:
> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see ALTER
> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest you
> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance you
> can use ISNULL for each nullable column and convert to empty string if value is NULL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
> > SQL Server 2005 SP2.
> >
> > The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
> > 2000 or 2005. The first column (cust_code) displays correct in all the
> > versions but the second column (related_info2 - That has all the concatenated
> > stuff) displays different. In 7.0, it brings all the values and if the column
> > has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
> > the rest of the columns data.
> >
> > In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
> > NULL value.
> >
> > Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
> >
> >
> > select customer.cust_code,
> > customer.bus_name
> > + ' '
> > + customer.address3
> > + ' Direct: '
> > + customer.phone_direct
> > + ' 800: '
> > + customer.phone_800
> > related_info2
> > from customer
> > order by customer.cust_code
> >
> > and here is the DDL:
> >
> > CREATE TABLE [dbo].[customer] (
> > [cust_code] [int] NOT NULL ,
> > [bus_name] [varchar] (40) NULL ,
> > [address3] [varchar] (30) NULL ,
> > [phone_800] [varchar] (30) NULL ,
> > [phone_direct] [varchar] (30) NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Thanks.
>|||> Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
You can't (assuming you refer to the session setting) since this isn't a sticky option (like SET
ANSI_NULLS). So, either use the database option or adjust your code to work regardless.
> COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
You lost me there. Can you explain with an example?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8D646949-DFA0-43FA-9AEA-B2D558AFC71C@.microsoft.com...
> Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
> COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
>
> "Tibor Karaszi" wrote:
>> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see
>> ALTER
>> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest
>> you
>> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance
>> you
>> can use ISNULL for each nullable column and convert to empty string if value is NULL.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
>> > SQL Server 2005 SP2.
>> >
>> > The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
>> > 2000 or 2005. The first column (cust_code) displays correct in all the
>> > versions but the second column (related_info2 - That has all the concatenated
>> > stuff) displays different. In 7.0, it brings all the values and if the column
>> > has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
>> > the rest of the columns data.
>> >
>> > In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
>> > NULL value.
>> >
>> > Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
>> >
>> >
>> > select customer.cust_code,
>> > customer.bus_name
>> > + ' '
>> > + customer.address3
>> > + ' Direct: '
>> > + customer.phone_direct
>> > + ' 800: '
>> > + customer.phone_800
>> > related_info2
>> > from customer
>> > order by customer.cust_code
>> >
>> > and here is the DDL:
>> >
>> > CREATE TABLE [dbo].[customer] (
>> > [cust_code] [int] NOT NULL ,
>> > [bus_name] [varchar] (40) NULL ,
>> > [address3] [varchar] (30) NULL ,
>> > [phone_800] [varchar] (30) NULL ,
>> > [phone_direct] [varchar] (30) NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> >
>> > Thanks.|||My bet.........Sorry..........COALESCE works. I just forgot to apply it
to whole fields in the select statement and not only the sub select.
Thanks again Tibor & Plamen.
"Tibor Karaszi" wrote:
> > Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
> You can't (assuming you refer to the session setting) since this isn't a sticky option (like SET
> ANSI_NULLS). So, either use the database option or adjust your code to work regardless.
>
> > COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
> You lost me there. Can you explain with an example?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8D646949-DFA0-43FA-9AEA-B2D558AFC71C@.microsoft.com...
> > Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
> >
> > COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see
> >> ALTER
> >> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest
> >> you
> >> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance
> >> you
> >> can use ISNULL for each nullable column and convert to empty string if value is NULL.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
> >> > SQL Server 2005 SP2.
> >> >
> >> > The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
> >> > 2000 or 2005. The first column (cust_code) displays correct in all the
> >> > versions but the second column (related_info2 - That has all the concatenated
> >> > stuff) displays different. In 7.0, it brings all the values and if the column
> >> > has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
> >> > the rest of the columns data.
> >> >
> >> > In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
> >> > NULL value.
> >> >
> >> > Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
> >> >
> >> >
> >> > select customer.cust_code,
> >> > customer.bus_name
> >> > + ' '
> >> > + customer.address3
> >> > + ' Direct: '
> >> > + customer.phone_direct
> >> > + ' 800: '
> >> > + customer.phone_800
> >> > related_info2
> >> > from customer
> >> > order by customer.cust_code
> >> >
> >> > and here is the DDL:
> >> >
> >> > CREATE TABLE [dbo].[customer] (
> >> > [cust_code] [int] NOT NULL ,
> >> > [bus_name] [varchar] (40) NULL ,
> >> > [address3] [varchar] (30) NULL ,
> >> > [phone_800] [varchar] (30) NULL ,
> >> > [phone_direct] [varchar] (30) NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> >
> >> > Thanks.
> >>
>

Is there one collation that includes all the Eastern Europen Languages and Latin 1 charset.

Can I specify a collate value for a column in a table that includes all the possible languages in the world or atleast Latin 1 and Eastern European languages.

My DB Collation is set to Latin 1 and the columns in the tables are all nvarchar or ntext, but certain hungarian characters are not displayed correctly.

What do all these collation codes represent:

SQL_EBCDIC037_CP1_CS_AS

211

SQL_EBCDIC273_CP1_CS_AS

212

SQL_EBCDIC277_CP1_CS_AS

213

SQL_EBCDIC278_CP1_CS_AS

214

SQL_EBCDIC280_CP1_CS_AS

215

SQL_EBCDIC284_CP1_CS_AS

216

SQL_EBCDIC285_CP1_CS_AS

217

SQL_EBCDIC297_CP1_CS_AS

They seem generic. Is there one collation that includes all the Eastern Europen Languages and Latin 1 charset. Please let me know.

Thanks,

Manisha

The collation affects sorting and code page for non-Unicode data. Since you are using Unicode strings (nvarchat & ntext) - the collation only affects sorting, it does not affect the character display.

If a character is not displayed correctly, it means either the data was converted to non-Unicode somewhere (check all the column types in SSIS) or the font does not support this character. You are not telling us where the character is displayed, so I have no idea which font are you using.

To answer original question - no, there is no universal collation, as each culture has its own sorting rules. But most probably the collation is not the problem here.|||The character is displayed on the webpage (jsp) where it is displayed incorrectly. However in the database when I open table it displays perfectly. I realize that there is no universal collation then it is probably a font issue as indicated, but I am being told by our front end developer that we are using Verdana font for display and that includes Hungarian Chars.|||I think the problem is not collation, but the code pages that front end developers use. The only common used encodings that includes all languages are Unicode and UTF-8, ask front end devs to generate their pages using UTF-8, and properly annotate them (I can't help you there, don't know JSP at all, seek a better forum).|||Thanks. We got this fixed yesterday by specifying the contentType on the JSP page level.

Is there any built in facility to encrypt a column data in SQLSERVER/MSDE

Hi
Is there any built in facility to encrypt a column data in SQLSERVER/MSDE

Thanks
Anji ReddyStrong, column-level encryption is possible in SQL 2005.

Thanks,
Clifford Dibble|||For a couple of examples, see:

http://blogs.msdn.com/lcris/archive/2005/06/09/427523.aspx
http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx

Laurentiu

Is there an XMLTYPE in SQL 2000?

Oracle 9 has an XMLTYPE data type.

I am interested in inserting a formatted block of XML in a SQL 2000 column to record a set of data in one column.

Is there a way to do this, or a datatype that comes close?

Thanks.As far as I know you have to use NTEXT. The new version of SQLServer due out later this year addresses the XML data type.