Book Recommendation?

Can anyone recommend a good book or site on Full Text Searching which goes
into detail about things like full text ranked search across multiple tables
especially using phrases not single words? I find the whole thing counter
intuitive compared to normal SQL
Chris,
Not at this time... but look for http://www.SQLFTS.com to come online soon
with content as I own this URL and I'm currently developing the content. As
for a book, I just today re-submitted a new book proposal to a publisher's
Editor-in-Chief (at her request) as the overall technical book market has
been in decline and new books have been difficult to launch on this subject.
However, with the Google IPO and with various announcements from Microsoft,
MSN and Steve Ballmer -
http://www.boston.com/business/techn...llenge_google/
- to quote this article - "Meanwhile, Ballmer left no doubt that Microsoft
has targeted Internet search services for the kind of all-out competitive
push that the company once used to seize dominance in Web browser software.
Despite Google's popularity, "the search market is still quite fragmented,"
Ballmer said, and existing tools still generate lots of useless results.
Ballmer said Microsoft plans to invent new search technology that will
change this, and make life more difficult for Google and other rivals."
So, now it seems to be a good time to be writing a book on Microsoft Search
Technology, including SQL Server 2005! Anyone should feel free to post &/or
email me (mailto:jt-kane@.comcast.net) with comments, encouragements,
feedback, suggestions etc. on both website content as well as future book
content!
Thanks,
John
Looking for a book &/or information on SQL FTS?
http://www.SQLFTS.com - First To Search for SQL Full-Text Search
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:O0WRakQkEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Can anyone recommend a good book or site on Full Text Searching which goes
> into detail about things like full text ranked search across multiple
tables
> especially using phrases not single words? I find the whole thing counter
> intuitive compared to normal SQL
>

Blocking 101

Pretty new to SQL Server and support multiple applications on SQL Server.
Just encountered a situation where "blocking" was occurring.
Can someone provide me more information or a link explaining Blocking and
how I can use SQL Server Enterprise Manager to determine who and what is
causing the blocking.
Any help is appreciated and I Thank You in advance for your help.
wnfisbaINF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Typically, it's caused by long running transactions that hold row, page or
table locks.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:2DC94438-B2D4-4BEE-A781-AC16DE69A7B7@.microsoft.com...
> Pretty new to SQL Server and support multiple applications on SQL Server.
> Just encountered a situation where "blocking" was occurring.
> Can someone provide me more information or a link explaining Blocking and
> how I can use SQL Server Enterprise Manager to determine who and what is
> causing the blocking.
> Any help is appreciated and I Thank You in advance for your help.
> wnfisba

Blank values Replicating to Oracle

Replicating from SQL Server 2005 to Oracle 9i Release 2. The source table has NOT NULL specified for all columns. The table contains multiple rows with blank values in one of the NOT NULL columns. When I attempt to replicate this table to Oracle, I'm unable to replicate the rows containing the blank values.

I've followed the directions in the Oracle Subscribers page in Books Online: http://msdn2.microsoft.com/en-us/library/ms151738.aspx

Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @.creation_script parameter of sp_addarticle

Here is the segment of the Publication Script referencing sp_addarticle:

exec sp_addarticle
@.publication = N'mike_hayes_test_replication2',
@.article = N'mike_hayes_test_replication',
@.source_owner = N'bmssa',
@.source_object = N'mike_hayes_test_replication',
@.type = N'logbased',
@.description = N'',
@.creation_script = 'CREATE TABLE [bmssa].[mike_hayes_test_replication2]( [EXCELCOLUMN] [int] NULL, [TABLENAME] [varchar](40) NULL, [FIELDNUM] [int] NULL , [REFTABLEID] [int] NULL , [MODULETYPE] [int] NULL , [DATAAREAID] [varchar](3) NULL, [RECVERSION] [int] NULL, [RECID] [int] NULL, [BRAND] [varchar](20) NULL )',
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00,
@.identityrangemanagementoption = N'manual',
@.destination_table = N'mike_hayes_test_replication2',
@.destination_owner = N'bmssa',
@.vertical_partition = N'false'
GO

I added the @.creation_script value, and changed the schema_option to 0x00.

The problem is that when replication occurs, the table that is created has NOT NULL specified for all columns. I expected that the @.creation_script would be executed, (as per the instructions), but that doesn't appear to be the case.

Any ideas?

Thanks for your help.

Mike Hayes

Hi Mike, you should specify the path to the file containing the create table statement for the @.creation_script parameter instead of just the "create table" statement.

Hope that helps,

-Raymond

|||Thanks for the reply. So this should be an Oracle script? Should I refer to it on the server (i.e. c:\scripts, where c:\ refers to the SQL Server drive)?

Thanks,

Mike Hayes|||

The syntax can arguably be anything compatible with Oracle although the safer thing to do is to just modify what the snapshot agent would have generated otherwise. The path should be something that is accessible(and\or relative) to the snapshot agent process since your file will then be copied by the snapshot agent to the snapshot folder. I am actually a bit surprised that the snapshot agent didn't die a horrible death trying to copy the "weird path" so something may be amiss here.

Come to think of it, would it be easier to just modify the script generated by the snapshot agent?

-Raymond

BizRule implementation

I have a table with follwoing feilds:
1)ClientID
2)EmailAddress
3)AddressDescription
4)Primary
5)BizRuleFlag
there could be multiple address for each client ,but each client **MUST**
have atleast one address with "Primary" flag set to "Y" (which mean that one
is his Primary address) and also one of the addresses **Must** also contain
an EmailAddress.I'd like to check all the data in the table and if these
rules are met set the "BizRuleFlag" to 1 otherwise to 0.
Any helps?
Thanks a lotHi
I am not sure why you are setting the bizrule flag as it would be out of
date when then data changes. Adding the check in a trigger to update all the
rows will have a large overhead.
You may want to add an addressid column to your table so that you can
identify rows uniquely or make some other column combination the PK.
Primary for a column name may prove tedious as you will always need to
enquote it.
If your rule was that the primary address has to have an email then you can
add contraints that will do this. If does not stop them not adding a primary
address through e.g.
CREATE TABLE Addresses (
ClientID INT NOT NULL,
AddressID INT NOT NULL,
EmailAddress varchar(50),
AddressDescription varchar(50) NOT NULL,
[Primary] bit not null default 0,
BizRuleFlag bit not null default 0,
CONSTRAINT PK_Addresses PRIMARY KEY (ClientId, AddressId) ,
CONSTRAINT CK_Primary_EMAIL CHECK ([Primary] = 0 OR ([Primary] = 1 AND
EmailAddress IS NOT NULL))
)
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 1, NULL, 'address 1', DEFAULT, DEFAULT )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 2, NULL, 'address 2', 1, DEFAULT )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CK_Primary_EMAIL'.
The conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 3, 'Address With Email', 'address 3', 1, DEFAULT )
Adding constraints so that and email must exist and a primary must exist can
be done using functions, although they may have a significant impact on
performance.
You will also need to add a Primary address first with an email, but
subsequent changes can mean that they are not bound together.
e.g.
CREATE FUNCTION fnPrimaryExists (@.ClientId Int, @.addressId int)
RETURNS tinyint
AS
BEGIN
RETURN CASE WHEN EXISTS ( SELECT * FROM Addresses WHERE ClientId =
@.ClientId AND AddressId <> @.AddressID AND [Primary] = 1) THEN 1 ELSE 0 END
END
CREATE FUNCTION fnEmailExists (@.ClientId Int)
RETURNS tinyint
AS
BEGIN
RETURN CASE WHEN EXISTS ( SELECT * FROM Addresses WHERE ClientId =
@.ClientId AND [EMAILAddress] IS NOT NULL) THEN 1 ELSE 0 END
END
CREATE TABLE Addresses (
ClientID INT NOT NULL,
AddressID INT NOT NULL,
EmailAddress varchar(50),
AddressDescription varchar(50) NOT NULL,
[Primary] bit not null default 0,
BizRuleFlag bit not null default 0,
CONSTRAINT PK_Addresses PRIMARY KEY (ClientId, AddressId) ,
CONSTRAINT CK_Primary CHECK (([Primary] = 1 AND dbo.fnPrimaryExists
(ClientId, AddressId ) = 0) OR ([Primary] = 0 AND dbo.fnPrimaryExists
(ClientId, AddressId ) = 1)),
CONSTRAINT CK_Email CHECK (EmailAddress IS NOT NULL OR dbo.fnEmailExists
(ClientId) = 1)
)
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 1, NULL, 'address 1', DEFAULT, DEFAULT )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CK_Primary'. The
conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 2, NULL, 'address 2', 1, DEFAULT )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CK_Email'. The
conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 3, 'Address With Email', 'address 3', 1, DEFAULT )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 4, 'Address With Email', 'address 4', DEFAULT, DEFAULT )
UPDATE Addresses SET EmailAddress = NULL
where CLIENTID = 1 and AddressId = 3
UPDATE Addresses SET [PRIMARY] = 0
where CLIENTID = 1 and AddressId = 3
/*
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE CHECK constraint 'CK_Primary'. The
conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
You may want to make bizrule a computed column
CREATE TABLE Addresses (
ClientID INT NOT NULL,
AddressID INT NOT NULL,
EmailAddress varchar(50),
AddressDescription varchar(50) NOT NULL,
[Primary] bit not null default 0,
BizRuleFlag AS CASE WHEN [Primary] = 1 OR [EMAILAddress] IS NOT NULL THEN 1
ELSE 0 END,
CONSTRAINT PK_Addresses PRIMARY KEY (ClientId, AddressId)
)
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 1, NULL, 'address 1', DEFAULT )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 2, NULL, 'address 2', 1 )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 3, 'Address With Email', 'address 3', 1 )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 4, 'Address With Email', 'address 4', DEFAULT )
SELECT * FROM Addresses
HTH
John
"J-T" wrote:

> I have a table with follwoing feilds:
> 1)ClientID
> 2)EmailAddress
> 3)AddressDescription
> 4)Primary
> 5)BizRuleFlag
> there could be multiple address for each client ,but each client **MUST**
> have atleast one address with "Primary" flag set to "Y" (which mean that o
ne
> is his Primary address) and also one of the addresses **Must** also contai
n
> an EmailAddress.I'd like to check all the data in the table and if these
> rules are met set the "BizRuleFlag" to 1 otherwise to 0.
> Any helps?
> Thanks a lot
>
>

Bitwise or aggregate function

Hi,
I understand there are no aggregate function for ORing multiple row columns
together. We have a access control system that looks like
CREATE TABLE AccessTable
(
UserID int NOT NULL PRIMARY KEY,
EntityID int NOT NULL PRIMARY KEY,
AccessMask int NOT NULL
)
where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
etc. We would like to be able to do something like
select AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = @.EntityID
I've seen some samples of a two table solution but I just wanted to double
check if someone has a better solution. Running SQL Server 2005.
Thanks,
MansoManso wrote:
> Hi,
> I understand there are no aggregate function for ORing multiple row column
s
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
> Thanks,
> Manso
Why would you store this data as a bitmap in the first place? Try:
SELECT
MAX(AccessMask & 1)+
MAX(AccessMask & 2)+
MAX(AccessMask & 4)+
MAX(AccessMask & 8)+
MAX(AccessMask & 16)+
MAX(AccessMask & 32)+
MAX(AccessMask & 64)+
MAX(AccessMask & 128) AS aggr_orsum
FROM AccessTable
WHERE EntityID = @.EntityID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Manso" <Manso@.discussions.microsoft.com> wrote in message
news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Hi,
> I understand there are no aggregate function for ORing multiple row
> columns
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
>
Well, it so happens SQL Server 2005 supports User-Defined Aggregate
functions.
For instance
--Aggregate.cs--
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native,
IsInvariantToDuplicates=true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false)]
public struct AGGR_ORSUM
{
int accumulator;
public void Init()
{
accumulator = 0;
}
public void Accumulate(SqlInt32 Value)
{
if (Value.IsNull)
return;
accumulator |= Value.Value;
}
public void Merge(AGGR_ORSUM Group)
{
accumulator |= Group.accumulator;
}
public SqlInt32 Terminate()
{
return new SqlInt32(accumulator);
}
}
--end Aggregate.cs--
--Test.sql--
\
drop table AccessTable
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL ,
AccessMask int NOT NULL
)
insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
select dbo.AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = 1
--end Test.sql--
David|||Thanks for your prompt reply David.
Because it's a convenient way of representing access masks. I know it has a
few drawbacks but the upside is we can append new access bits without adding
columns or modifying the schema. Since we're never using the access column i
n
a where clause I find it to be the best option. We have about 13 different
access types today but that will increase.
I just managed to put together a function that does what we want:
declare @.Return int
set @.Return = 0
select @.Return = @.Return | AccessMask
from AccessTable
where where EntityID = @.EntityID
return @.Return
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> Why would you store this data as a bitmap in the first place? Try:
> SELECT
> MAX(AccessMask & 1)+
> MAX(AccessMask & 2)+
> MAX(AccessMask & 4)+
> MAX(AccessMask & 8)+
> MAX(AccessMask & 16)+
> MAX(AccessMask & 32)+
> MAX(AccessMask & 64)+
> MAX(AccessMask & 128) AS aggr_orsum
> FROM AccessTable
> WHERE EntityID = @.EntityID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> Thanks for your prompt reply David.
> Because it's a convenient way of representing access masks. I know it has
a
> few drawbacks but the upside is we can append new access bits without addi
ng
> columns or modifying the schema. Since we're never using the access column
in
> a where clause I find it to be the best option. We have about 13 different
> access types today but that will increase.
How about :
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL,
AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
PRIMARY KEY (UserID, EntityID, AccessTypeID)
)

> I just managed to put together a function that does what we want:
> declare @.Return int
> set @.Return = 0
> select @.Return = @.Return | AccessMask
> from AccessTable
> where where EntityID = @.EntityID
> return @.Return
>
That gives an undefined result because it's a multi-row assignment,
which isn't officially supported. For that reason it may break under
some conditions. Also, it isn't a general aggregation solution because
it cannot GROUP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Beautiful, David. Thanks a lot. We've been really indecisive whether to use
CLR in this project but maybe we should look into it...looks .
Thanks,
Manso
"David Browne" wrote:

> "Manso" <Manso@.discussions.microsoft.com> wrote in message
> news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Well, it so happens SQL Server 2005 supports User-Defined Aggregate
> functions.
> For instance
> --Aggregate.cs--
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
> [Serializable]
> [SqlUserDefinedAggregate(Format.Native,
> IsInvariantToDuplicates=true,
> IsInvariantToNulls = true,
> IsInvariantToOrder = true,
> IsNullIfEmpty = false)]
> public struct AGGR_ORSUM
> {
> int accumulator;
> public void Init()
> {
> accumulator = 0;
> }
> public void Accumulate(SqlInt32 Value)
> {
> if (Value.IsNull)
> return;
> accumulator |= Value.Value;
> }
> public void Merge(AGGR_ORSUM Group)
> {
> accumulator |= Group.accumulator;
> }
> public SqlInt32 Terminate()
> {
> return new SqlInt32(accumulator);
> }
>
> }
> --end Aggregate.cs--
> --Test.sql--
> \
> drop table AccessTable
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL ,
> AccessMask int NOT NULL
> )
> insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
> insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
> insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
> select dbo.AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = 1
> --end Test.sql--
> David
>
>|||I left out the check constraint for clarity.
I read somewhere else that this could give unwanted results somewhere else
but I haven't managed to find out under which circumstances. Do you know
when/why/how this could break?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> How about :
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL,
> AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
> PRIMARY KEY (UserID, EntityID, AccessTypeID)
> )
>
> That gives an undefined result because it's a multi-row assignment,
> which isn't officially supported. For that reason it may break under
> some conditions. Also, it isn't a general aggregation solution because
> it cannot GROUP.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> I left out the check constraint for clarity.
But in my example you don't need the bitmap (because I changed the
key).

> I read somewhere else that this could give unwanted results somewhere else
> but I haven't managed to find out under which circumstances. Do you know
> when/why/how this could break?
Books Online (2000 and 2005) says
"If the SELECT statement returns more than one value, the variable is
assigned the last value returned."
So if your "aggregate" solution works it is arguably a bug!
The following KB confirms that "The correct behavior for an aggregate
concatenation query is undefined." So you are definitely on thin ice if
you rely on it.
http://support.microsoft.com/kb/287515/en-us
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"The correct behavior for an aggregate concatenation query is undefined."
This classic contrived phrase was written by some still unknown fuzzball
confirmed by
one intelligent MS employee.It is meaningless and that's putting it kindly:)
Perhaps it was Celko:) My guess is that since it came from Sybase MS thinks
the less said
about it the better.Aside from the functionality,which boils down to 'it
depends',
it is another example that 'words due matter':)
For interested readers see what sybase says about it (especially in Update)
in their
enterprise db.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145543785.636307.71580@.i39g2000cwa.googlegroups.com...
> Manso wrote:
> But in my example you don't need the bitmap (because I changed the
> key).
>
> Books Online (2000 and 2005) says
> "If the SELECT statement returns more than one value, the variable is
> assigned the last value returned."
> So if your "aggregate" solution works it is arguably a bug!
> The following KB confirms that "The correct behavior for an aggregate
> concatenation query is undefined." So you are definitely on thin ice if
> you rely on it.
> http://support.microsoft.com/kb/287515/en-us
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Writing assembly language in SQL will come back and get you. Use a
normalized table design with one column for each access privilege or a
hierarchy of privileges in a nested sets model.
Also, you had two PRIMARY KEYs!