Aggregete() function doesn't work for SQL query results! - Part 2

I am writing my own MDX query inside Reporting Services, bypassing the graphic MDX generator.

Both MDX query and SQL query return identical results and both have detail level values and high level values.

During the flattenning process, null value from MDX is still null, but null value from SQL is translated into blank.

Thus only the MDX result works for Aggregate() function, not the SQL result. Because Aggregate() function is triggered by null value, not blank.

Someone suggested to use MDX stored procedure to do custom calculations. Thus my report is calling a MDX query, not a SQL query. I have tried this approach already, but MDX is not as flexible as SQL. For example, in SQL, I can easily join, pivot, order by, group by, filter...None of these is easy in MDX.

If I know how to make reporting services treat null as null or to make Aggregate() function be triggered by a blank value, then my problem is solved.

Does Aggregate() funciton only works with MDX, not SQL?

Thanks,

Bo Dong

bo_dong@.yahoo.com

The Aggregate() function only works in combination with a data extension that implements the IDataReaderExtension interface (see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_9lit.asp).

The "Analysis Services" data extension available in RS 2005 implements this interface and internally detects aggregation rows based on nulls in hierarchies and exposes that information through the IDataReaderExtension interface.

Note: The "SQL Server" data extension available in RS 2005 does not implement this interface.

You can implement your own custom data extension (http://msdn2.microsoft.com/en-us/library/ms154655.aspx) and then implement the IDataReaderExtension interface - based on that you have full control in your custom data extension of how exactly aggregate rows from your relational query are marked.

-- Robert

Aggregate functions

Hi all,
Is there any way to create my own aggregate functions on SQl server 2000 ?
I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
any aquivalent for SQL server ?
Thanx in advance for your replies.
No, though there may be a way in SQL Server 2005. What aggregate do you
need? Some aggregates that aren't built in can still be implemented in
the current version.
Steve Kass
Drew University
imparfait wrote:

>Hi all,
>Is there any way to create my own aggregate functions on SQl server 2000 ?
>I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
>any aquivalent for SQL server ?
>
>Thanx in advance for your replies.
>
>
>
|||Hi Steve,
Thank you for ur reply.
I was surprized by the poorness of aggregation function number ( about a
dozen ).
My aim is to return for example the concatenation of some field on the
grouped by set.
here's a simplified details example:
ID | NAME
1 | foo1
2 | foo1
3 | foo2
4 | foo2
and I want to return the concatenantion of the IDs having the same NAME
something like :
select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
would return :
1,2 | foo1
3,4 | foo2
the second argument of my_own_aggregation_function is a separator ( in this
example it's a "," )
Of course I can solve this another way but creating my own aggregation
function seems to me the most evident thing to think about as it avoids to
make an additional query.
Note : I am working on a huge database ( millions of lines...)
Thanx again for your answer.
"Steve Kass" <skass@.drew.edu> a crit dans le message de
news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> No, though there may be a way in SQL Server 2005. What aggregate do you
> need? Some aggregates that aren't built in can still be implemented in
> the current version.
> Steve Kass
> Drew University
> imparfait wrote:
?
>
|||There is no feature of SQL Server specifically for this requirement,
though user-defined aggregates may be available in SQL Server 2005. It
might be well worth looking at a third-party tool such as the one at
http://www.rac4sql.net, or checking the capabilities of report writers.
Nonetheless, there are some SQL options, and here are three. They
assume that the items to be aggregated are distinct, and they all put
those items into lists in alphanumeric order.
1. If you know the maximum number of items that will appear for a
particular NAME value, you can do this, which is very efficient if there
is an index on (NAME, ID), or perhaps even on just (NAME). [Example
uses Northwind's Orders table, and itentionally shows how it can miss
too-long lists]:
use Northwind
go
select
t.CustomerID,
max(case rnk when 1 then OrderID end) +
coalesce(','+max(case rnk when 2 then OrderID end),'') +
coalesce(','+max(case rnk when 3 then OrderID end),'') +
coalesce(','+max(case rnk when 4 then OrderID end),'') +
coalesce(','+max(case rnk when 5 then OrderID end),'') +
coalesce(','+max(case rnk when 6 then OrderID end),'') +
coalesce(','+max(case rnk when 7 then OrderID end),'') +
coalesce(','+max(case rnk when 8 then OrderID end),'') +
coalesce(','+max(case rnk when 9 then OrderID end),'') +
coalesce(','+max(case rnk when 10 then OrderID end),'') +
coalesce(','+max(case rnk when 11 then OrderID end),'')
from (
select
t1.CustomerID,
cast(t1.OrderID as char(5)) as OrderID,
count(t2.OrderID) as rnk
from Orders t1, Orders t2
where t1.CustomerID = t2.CustomerID
and t1.OrderID <= t2.OrderID
group by t1.CustomerID, t1.OrderID
) t
group by CustomerID
2. If you don't know the number of items, but there is still a useful
index, a cursor is a reasonable choice:
CREATE TABLE Result (
KeyCol char(5),
List varchar(2000)
)
DECLARE C CURSOR FAST_FORWARD FOR
SELECT CustomerID as KeyCol, rtrim(OrderID) as Tag
FROM Northwind..Orders
ORDER BY 1,2
OPEN C
BEGIN TRAN
DECLARE @.currK varchar(30), @.K varchar(30),
@.nextT varchar(80), @.T varchar(2000)
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 BEGIN
SET @.T = @.nextT
SET @.currK = @.K
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 AND @.K = @.currK BEGIN
SET @.T = @.T + ',' + @.nextT
FETCH NEXT FROM C INTO @.K, @.nextT
END
INSERT INTO Result SELECT @.currK, @.T
END
COMMIT TRAN
CLOSE C
DEALLOCATE C
SELECT * FROM Result
GO
DROP TABLE Result
3. An iterative non-cursor solution is also possible:
SET NOCOUNT ON
GO
--Create a view of the data you want to aggregate
CREATE VIEW Base as
SELECT CustomerID AS KeyCol, rtrim(OrderID) AS Tag
FROM Northwind..Orders
go
--A helpful working table
SELECT A.KeyCol, A.Tag, COUNT(B.Tag) AS TagRank
INTO Working
FROM Base A JOIN Base B
ON A.Tag >= B.Tag
AND A.KeyCol = B.KeyCol
GROUP BY A.KeyCol, A.Tag
CREATE UNIQUE CLUSTERED INDEX Working_KT ON Working(KeyCol,Tag)
--The result table, ultimately, but with no list and two helpful extra
columns
SELECT KeyCol, COUNT(Tag) AS TotalTags, CAST('' AS varchar(8000)) AS TagList
INTO KeyString
FROM Working
GROUP BY KeyCol
CREATE UNIQUE CLUSTERED INDEX KeyString_KT ON KeyString(KeyCol,TotalTags)
--Put the first item in for each key
UPDATE KeyString
SET TagList = K.TagList + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND 0 < K.TotalTags
AND 1 = W.TagRank
DECLARE @.pos int
SET @.pos = 1
--Continue to put items in where there are any left
WHILE @.@.rowcount > 0 BEGIN
SET @.pos = @.pos + 1
UPDATE KeyString
SET TagList = K.TagList + ',' + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND @.pos <= K.TotalTags
AND @.pos = W.TagRank
END
--What did we get?
SELECT * FROM KeyString
ORDER BY KeyCol
--Clean up
DROP VIEW Base
DROP TABLE Working
DROP TABLE KeyString
SK
imparfait wrote:

>Hi Steve,
>Thank you for ur reply.
>I was surprized by the poorness of aggregation function number ( about a
>dozen ).
>My aim is to return for example the concatenation of some field on the
>grouped by set.
>here's a simplified details example:
>ID | NAME
>1 | foo1
>2 | foo1
>3 | foo2
>4 | foo2
>and I want to return the concatenantion of the IDs having the same NAME
>something like :
>select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
>would return :
>1,2 | foo1
>3,4 | foo2
>the second argument of my_own_aggregation_function is a separator ( in this
>example it's a "," )
>Of course I can solve this another way but creating my own aggregation
>function seems to me the most evident thing to think about as it avoids to
>make an additional query.
>Note : I am working on a huge database ( millions of lines...)
>Thanx again for your answer.
>
>"Steve Kass" <skass@.drew.edu> a crit dans le message de
>news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
>
>?
>
>
>
|||Thank you very much for ur precise and detailed answer.
"Steve Kass" <skass@.drew.edu> a crit dans le message de
news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> There is no feature of SQL Server specifically for this requirement,
> though user-defined aggregates may be available in SQL Server 2005.
|||impafait
You dont have to know a maximum number of items
Look at below soultion works for you
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"imparfait" <imparfait@.noway.noway> wrote in message
news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Thank you very much for ur precise and detailed answer.
>
> "Steve Kass" <skass@.drew.edu> a crit dans le message de
> news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
>
|||Uri,
While this works in many situations, it's not supported or documented,
and I don't recommend using it in a production environment.
SK
Uri Dimant wrote:

>impafait
>You dont have to know a maximum number of items
>Look at below soultion works for you
>create table w
>(
> id int,
> t varchar(50)
>)
>insert into w values (1,'abc')
>insert into w values (1,'def')
>insert into w values (1,'ghi')
>insert into w values (2,'ABC')
>insert into w values (2,'DEF')
>select * from w
>
>create function dbo.fn_my ( @.id int)
>returns varchar(100)
>as
>begin
>declare @.w varchar(100)
> set @.w=''
> select @.w=@.w+t+',' from w where id=@.id
> return @.w
>end
>select id,
>dbo.fn_my (dd.id)
>from
>(
> select distinct id from w
>)
>as dd
>drop function dbo.fn_my
>"imparfait" <imparfait@.noway.noway> wrote in message
>news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
>
>
>
|||Think it's documented in a white paper somewhere - I've lost the reference now.
Should be
declare @.w varchar(100)
select @.w=coalesce(@.w+',','') +t from w where id=@.id
return @.w
"Steve Kass" wrote:

> Uri,
> While this works in many situations, it's not supported or documented,
> and I don't recommend using it in a production environment.
> SK
> Uri Dimant wrote:
>
>
|||Nigel,
The use of variables is documented, but as far as I know, if a select
statement doesn't produce a result set, the only guarantee here is that
@.w will be assigned at least one value, and no guarantee that there will
be one assignment for every row in the table, let alone any guarantee
about the order of assignments. The only mention of this I know of is
http://support.microsoft.com/default...;en-us;287515, which
says "The correct behavior for an aggregate concatenation query is
undefined", and while it also says "In order to achieve the expected
results from an aggregate concatenation query, apply any Transact-SQL
function or expression to the columns in the SELECT list rather than in
the ORDER BY clause," I have my doubts whether that sole off-handed
remark in a relatively confusing KB article is a real indication that
Microsoft intends this technique to be reliable.
SK
Nigel Rivett wrote:
[vbcol=seagreen]
>Think it's documented in a white paper somewhere - I've lost the reference now.
>Should be
>declare @.w varchar(100)
> select @.w=coalesce(@.w+',','') +t from w where id=@.id
> return @.w
>
>"Steve Kass" wrote:
>

Aggregate functions

Hi all,
Is there any way to create my own aggregate functions on SQl server 2000 ?
I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
any aquivalent for SQL server ?
Thanx in advance for your replies.No, though there may be a way in SQL Server 2005. What aggregate do you
need? Some aggregates that aren't built in can still be implemented in
the current version.
Steve Kass
Drew University
imparfait wrote:

>Hi all,
>Is there any way to create my own aggregate functions on SQl server 2000 ?
>I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
>any aquivalent for SQL server ?
>
>Thanx in advance for your replies.
>
>
>|||Hi Steve,
Thank you for ur reply.
I was surprized by the poorness of aggregation function number ( about a
dozen ).
My aim is to return for example the concatenation of some field on the
grouped by set.
here's a simplified details example:
ID | NAME
1 | foo1
2 | foo1
3 | foo2
4 | foo2
and I want to return the concatenantion of the IDs having the same NAME
something like :
select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
would return :
1,2 | foo1
3,4 | foo2
the second argument of my_own_aggregation_function is a separator ( in this
example it's a "," )
Of course I can solve this another way but creating my own aggregation
function seems to me the most evident thing to think about as it avoids to
make an additional query.
Note : I am working on a huge database ( millions of lines...)
Thanx again for your answer.
"Steve Kass" <skass@.drew.edu> a crit dans le message de
news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
> No, though there may be a way in SQL Server 2005. What aggregate do you
> need? Some aggregates that aren't built in can still be implemented in
> the current version.
> Steve Kass
> Drew University
> imparfait wrote:
>
?[vbcol=seagreen]
>|||Thank you very much for ur precise and detailed answer.
"Steve Kass" <skass@.drew.edu> a crit dans le message de
news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> There is no feature of SQL Server specifically for this requirement,
> though user-defined aggregates may be available in SQL Server 2005.|||There is no feature of SQL Server specifically for this requirement,
though user-defined aggregates may be available in SQL Server 2005. It
might be well worth looking at a third-party tool such as the one at
http://www.rac4sql.net, or checking the capabilities of report writers.
Nonetheless, there are some SQL options, and here are three. They
assume that the items to be aggregated are distinct, and they all put
those items into lists in alphanumeric order.
1. If you know the maximum number of items that will appear for a
particular NAME value, you can do this, which is very efficient if there
is an index on (NAME, ID), or perhaps even on just (NAME). [Example
uses Northwind's Orders table, and itentionally shows how it can miss
too-long lists]:
use Northwind
go
select
t.CustomerID,
max(case rnk when 1 then OrderID end) +
coalesce(','+max(case rnk when 2 then OrderID end),'') +
coalesce(','+max(case rnk when 3 then OrderID end),'') +
coalesce(','+max(case rnk when 4 then OrderID end),'') +
coalesce(','+max(case rnk when 5 then OrderID end),'') +
coalesce(','+max(case rnk when 6 then OrderID end),'') +
coalesce(','+max(case rnk when 7 then OrderID end),'') +
coalesce(','+max(case rnk when 8 then OrderID end),'') +
coalesce(','+max(case rnk when 9 then OrderID end),'') +
coalesce(','+max(case rnk when 10 then OrderID end),'') +
coalesce(','+max(case rnk when 11 then OrderID end),'')
from (
select
t1.CustomerID,
cast(t1.OrderID as char(5)) as OrderID,
count(t2.OrderID) as rnk
from Orders t1, Orders t2
where t1.CustomerID = t2.CustomerID
and t1.OrderID <= t2.OrderID
group by t1.CustomerID, t1.OrderID
) t
group by CustomerID
2. If you don't know the number of items, but there is still a useful
index, a cursor is a reasonable choice:
CREATE TABLE Result (
KeyCol char(5),
List varchar(2000)
)
DECLARE C CURSOR FAST_FORWARD FOR
SELECT CustomerID as KeyCol, rtrim(OrderID) as Tag
FROM Northwind..Orders
ORDER BY 1,2
OPEN C
BEGIN TRAN
DECLARE @.currK varchar(30), @.K varchar(30),
@.nextT varchar(80), @.T varchar(2000)
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 BEGIN
SET @.T = @.nextT
SET @.currK = @.K
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 AND @.K = @.currK BEGIN
SET @.T = @.T + ',' + @.nextT
FETCH NEXT FROM C INTO @.K, @.nextT
END
INSERT INTO Result SELECT @.currK, @.T
END
COMMIT TRAN
CLOSE C
DEALLOCATE C
SELECT * FROM Result
GO
DROP TABLE Result
3. An iterative non-cursor solution is also possible:
SET NOCOUNT ON
GO
--Create a view of the data you want to aggregate
CREATE VIEW Base as
SELECT CustomerID AS KeyCol, rtrim(OrderID) AS Tag
FROM Northwind..Orders
go
--A helpful working table
SELECT A.KeyCol, A.Tag, COUNT(B.Tag) AS TagRank
INTO Working
FROM Base A JOIN Base B
ON A.Tag >= B.Tag
AND A.KeyCol = B.KeyCol
GROUP BY A.KeyCol, A.Tag
CREATE UNIQUE CLUSTERED INDEX Working_KT ON Working(KeyCol,Tag)
--The result table, ultimately, but with no list and two helpful extra
columns
SELECT KeyCol, COUNT(Tag) AS TotalTags, CAST('' AS varchar(8000)) AS TagList
INTO KeyString
FROM Working
GROUP BY KeyCol
CREATE UNIQUE CLUSTERED INDEX KeyString_KT ON KeyString(KeyCol,TotalTags)
--Put the first item in for each key
UPDATE KeyString
SET TagList = K.TagList + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND 0 < K.TotalTags
AND 1 = W.TagRank
DECLARE @.pos int
SET @.pos = 1
--Continue to put items in where there are any left
WHILE @.@.rowcount > 0 BEGIN
SET @.pos = @.pos + 1
UPDATE KeyString
SET TagList = K.TagList + ',' + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND @.pos <= K.TotalTags
AND @.pos = W.TagRank
END
--What did we get?
SELECT * FROM KeyString
ORDER BY KeyCol
--Clean up
DROP VIEW Base
DROP TABLE Working
DROP TABLE KeyString
SK
imparfait wrote:

>Hi Steve,
>Thank you for ur reply.
>I was surprized by the poorness of aggregation function number ( about a
>dozen ).
>My aim is to return for example the concatenation of some field on the
>grouped by set.
>here's a simplified details example:
>ID | NAME
>1 | foo1
>2 | foo1
>3 | foo2
>4 | foo2
>and I want to return the concatenantion of the IDs having the same NAME
>something like :
>select my_own_aggregation_function(ID,','), NAME from my_table group by NAM
E
>would return :
>1,2 | foo1
>3,4 | foo2
>the second argument of my_own_aggregation_function is a separator ( in this
>example it's a "," )
>Of course I can solve this another way but creating my own aggregation
>function seems to me the most evident thing to think about as it avoids to
>make an additional query.
>Note : I am working on a huge database ( millions of lines...)
>Thanx again for your answer.
>
>"Steve Kass" <skass@.drew.edu> a crit dans le message de
>news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
>
>?
>
>
>|||impafait
You dont have to know a maximum number of items
Look at below soultion works for you
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"imparfait" <imparfait@.noway.noway> wrote in message
news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Thank you very much for ur precise and detailed answer.
>
> "Steve Kass" <skass@.drew.edu> a crit dans le message de
> news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
>|||Uri,
While this works in many situations, it's not supported or documented,
and I don't recommend using it in a production environment.
SK
Uri Dimant wrote:

>impafait
>You dont have to know a maximum number of items
>Look at below soultion works for you
>create table w
>(
> id int,
> t varchar(50)
> )
>insert into w values (1,'abc')
>insert into w values (1,'def')
>insert into w values (1,'ghi')
>insert into w values (2,'ABC')
>insert into w values (2,'DEF')
>select * from w
>
>create function dbo.fn_my ( @.id int)
>returns varchar(100)
>as
>begin
>declare @.w varchar(100)
> set @.w=''
> select @.w=@.w+t+',' from w where id=@.id
> return @.w
>end
>select id,
>dbo.fn_my (dd.id)
>from
>(
> select distinct id from w
> )
>as dd
>drop function dbo.fn_my
>"imparfait" <imparfait@.noway.noway> wrote in message
>news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
>
>
>|||Think it's documented in a white paper somewhere - I've lost the reference n
ow.
Should be
declare @.w varchar(100)
select @.w=coalesce(@.w+',','') +t from w where id=@.id
return @.w
"Steve Kass" wrote:

> Uri,
> While this works in many situations, it's not supported or documented,
> and I don't recommend using it in a production environment.
> SK
> Uri Dimant wrote:
>
>|||Nigel,
The use of variables is documented, but as far as I know, if a select
statement doesn't produce a result set, the only guarantee here is that
@.w will be assigned at least one value, and no guarantee that there will
be one assignment for every row in the table, let alone any guarantee
about the order of assignments. The only mention of this I know of is
http://support.microsoft.com/defaul...b;en-us;287515, which
says "The correct behavior for an aggregate concatenation query is
undefined", and while it also says "In order to achieve the expected
results from an aggregate concatenation query, apply any Transact-SQL
function or expression to the columns in the SELECT list rather than in
the ORDER BY clause," I have my doubts whether that sole off-handed
remark in a relatively confusing KB article is a real indication that
Microsoft intends this technique to be reliable.
SK
Nigel Rivett wrote:
[vbcol=seagreen]
>Think it's documented in a white paper somewhere - I've lost the reference
now.
>Should be
>declare @.w varchar(100)
> select @.w=coalesce(@.w+',','') +t from w where id=@.id
> return @.w
>
>"Steve Kass" wrote:
>
>

Aggregate functions

Hi all,
Is there any way to create my own aggregate functions on SQl server 2000 ?
I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
any aquivalent for SQL server ?
Thanx in advance for your replies.No, though there may be a way in SQL Server 2005. What aggregate do you
need? Some aggregates that aren't built in can still be implemented in
the current version.
Steve Kass
Drew University
imparfait wrote:
>Hi all,
>Is there any way to create my own aggregate functions on SQl server 2000 ?
>I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
>any aquivalent for SQL server ?
>
>Thanx in advance for your replies.
>
>
>|||Hi Steve,
Thank you for ur reply.
I was surprized by the poorness of aggregation function number ( about a
dozen ).
My aim is to return for example the concatenation of some field on the
grouped by set.
here's a simplified details example:
ID | NAME
1 | foo1
2 | foo1
3 | foo2
4 | foo2
and I want to return the concatenantion of the IDs having the same NAME
something like :
select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
would return :
1,2 | foo1
3,4 | foo2
the second argument of my_own_aggregation_function is a separator ( in this
example it's a "," )
Of course I can solve this another way but creating my own aggregation
function seems to me the most evident thing to think about as it avoids to
make an additional query.
Note : I am working on a huge database ( millions of lines...)
Thanx again for your answer.
"Steve Kass" <skass@.drew.edu> a écrit dans le message de
news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
> No, though there may be a way in SQL Server 2005. What aggregate do you
> need? Some aggregates that aren't built in can still be implemented in
> the current version.
> Steve Kass
> Drew University
> imparfait wrote:
> >Hi all,
> >Is there any way to create my own aggregate functions on SQl server 2000
?
> >
> >I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
> >any aquivalent for SQL server ?
> >
> >
> >Thanx in advance for your replies.
> >
> >
> >
> >
> >
>|||There is no feature of SQL Server specifically for this requirement,
though user-defined aggregates may be available in SQL Server 2005. It
might be well worth looking at a third-party tool such as the one at
http://www.rac4sql.net, or checking the capabilities of report writers.
Nonetheless, there are some SQL options, and here are three. They
assume that the items to be aggregated are distinct, and they all put
those items into lists in alphanumeric order.
1. If you know the maximum number of items that will appear for a
particular NAME value, you can do this, which is very efficient if there
is an index on (NAME, ID), or perhaps even on just (NAME). [Example
uses Northwind's Orders table, and itentionally shows how it can miss
too-long lists]:
use Northwind
go
select
t.CustomerID,
max(case rnk when 1 then OrderID end) +
coalesce(','+max(case rnk when 2 then OrderID end),'') +
coalesce(','+max(case rnk when 3 then OrderID end),'') +
coalesce(','+max(case rnk when 4 then OrderID end),'') +
coalesce(','+max(case rnk when 5 then OrderID end),'') +
coalesce(','+max(case rnk when 6 then OrderID end),'') +
coalesce(','+max(case rnk when 7 then OrderID end),'') +
coalesce(','+max(case rnk when 8 then OrderID end),'') +
coalesce(','+max(case rnk when 9 then OrderID end),'') +
coalesce(','+max(case rnk when 10 then OrderID end),'') +
coalesce(','+max(case rnk when 11 then OrderID end),'')
from (
select
t1.CustomerID,
cast(t1.OrderID as char(5)) as OrderID,
count(t2.OrderID) as rnk
from Orders t1, Orders t2
where t1.CustomerID = t2.CustomerID
and t1.OrderID <= t2.OrderID
group by t1.CustomerID, t1.OrderID
) t
group by CustomerID
2. If you don't know the number of items, but there is still a useful
index, a cursor is a reasonable choice:
CREATE TABLE Result (
KeyCol char(5),
List varchar(2000)
)
DECLARE C CURSOR FAST_FORWARD FOR
SELECT CustomerID as KeyCol, rtrim(OrderID) as Tag
FROM Northwind..Orders
ORDER BY 1,2
OPEN C
BEGIN TRAN
DECLARE @.currK varchar(30), @.K varchar(30),
@.nextT varchar(80), @.T varchar(2000)
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 BEGIN
SET @.T = @.nextT
SET @.currK = @.K
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 AND @.K = @.currK BEGIN
SET @.T = @.T + ',' + @.nextT
FETCH NEXT FROM C INTO @.K, @.nextT
END
INSERT INTO Result SELECT @.currK, @.T
END
COMMIT TRAN
CLOSE C
DEALLOCATE C
SELECT * FROM Result
GO
DROP TABLE Result
3. An iterative non-cursor solution is also possible:
SET NOCOUNT ON
GO
--Create a view of the data you want to aggregate
CREATE VIEW Base as
SELECT CustomerID AS KeyCol, rtrim(OrderID) AS Tag
FROM Northwind..Orders
go
--A helpful working table
SELECT A.KeyCol, A.Tag, COUNT(B.Tag) AS TagRank
INTO Working
FROM Base A JOIN Base B
ON A.Tag >= B.Tag
AND A.KeyCol = B.KeyCol
GROUP BY A.KeyCol, A.Tag
CREATE UNIQUE CLUSTERED INDEX Working_KT ON Working(KeyCol,Tag)
--The result table, ultimately, but with no list and two helpful extra
columns
SELECT KeyCol, COUNT(Tag) AS TotalTags, CAST('' AS varchar(8000)) AS TagList
INTO KeyString
FROM Working
GROUP BY KeyCol
CREATE UNIQUE CLUSTERED INDEX KeyString_KT ON KeyString(KeyCol,TotalTags)
--Put the first item in for each key
UPDATE KeyString
SET TagList = K.TagList + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND 0 < K.TotalTags
AND 1 = W.TagRank
DECLARE @.pos int
SET @.pos = 1
--Continue to put items in where there are any left
WHILE @.@.rowcount > 0 BEGIN
SET @.pos = @.pos + 1
UPDATE KeyString
SET TagList = K.TagList + ',' + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND @.pos <= K.TotalTags
AND @.pos = W.TagRank
END
--What did we get?
SELECT * FROM KeyString
ORDER BY KeyCol
--Clean up
DROP VIEW Base
DROP TABLE Working
DROP TABLE KeyString
SK
imparfait wrote:
>Hi Steve,
>Thank you for ur reply.
>I was surprized by the poorness of aggregation function number ( about a
>dozen ).
>My aim is to return for example the concatenation of some field on the
>grouped by set.
>here's a simplified details example:
>ID | NAME
>1 | foo1
>2 | foo1
>3 | foo2
>4 | foo2
>and I want to return the concatenantion of the IDs having the same NAME
>something like :
>select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
>would return :
>1,2 | foo1
>3,4 | foo2
>the second argument of my_own_aggregation_function is a separator ( in this
>example it's a "," )
>Of course I can solve this another way but creating my own aggregation
>function seems to me the most evident thing to think about as it avoids to
>make an additional query.
>Note : I am working on a huge database ( millions of lines...)
>Thanx again for your answer.
>
>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
>news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
>
>>No, though there may be a way in SQL Server 2005. What aggregate do you
>>need? Some aggregates that aren't built in can still be implemented in
>>the current version.
>>Steve Kass
>>Drew University
>>imparfait wrote:
>>
>>Hi all,
>>Is there any way to create my own aggregate functions on SQl server 2000
>>
>?
>
>>I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
>>any aquivalent for SQL server ?
>>
>>Thanx in advance for your replies.
>>
>>
>>
>
>|||Thank you very much for ur precise and detailed answer.
"Steve Kass" <skass@.drew.edu> a écrit dans le message de
news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> There is no feature of SQL Server specifically for this requirement,
> though user-defined aggregates may be available in SQL Server 2005.|||impafait
You dont have to know a maximum number of items
Look at below soultion works for you
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"imparfait" <imparfait@.noway.noway> wrote in message
news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Thank you very much for ur precise and detailed answer.
>
> "Steve Kass" <skass@.drew.edu> a écrit dans le message de
> news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > There is no feature of SQL Server specifically for this requirement,
> > though user-defined aggregates may be available in SQL Server 2005.
>|||Uri,
While this works in many situations, it's not supported or documented,
and I don't recommend using it in a production environment.
SK
Uri Dimant wrote:
>impafait
>You dont have to know a maximum number of items
>Look at below soultion works for you
>create table w
>(
> id int,
> t varchar(50)
>)
>insert into w values (1,'abc')
>insert into w values (1,'def')
>insert into w values (1,'ghi')
>insert into w values (2,'ABC')
>insert into w values (2,'DEF')
>select * from w
>
>create function dbo.fn_my ( @.id int)
>returns varchar(100)
>as
>begin
>declare @.w varchar(100)
> set @.w=''
> select @.w=@.w+t+',' from w where id=@.id
> return @.w
>end
>select id,
>dbo.fn_my (dd.id)
>from
>(
> select distinct id from w
>)
>as dd
>drop function dbo.fn_my
>"imparfait" <imparfait@.noway.noway> wrote in message
>news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
>
>>Thank you very much for ur precise and detailed answer.
>>
>>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
>>news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
>>
>>There is no feature of SQL Server specifically for this requirement,
>>though user-defined aggregates may be available in SQL Server 2005.
>>
>>
>
>|||Think it's documented in a white paper somewhere - I've lost the reference now.
Should be
declare @.w varchar(100)
select @.w=coalesce(@.w+',','') +t from w where id=@.id
return @.w
"Steve Kass" wrote:
> Uri,
> While this works in many situations, it's not supported or documented,
> and I don't recommend using it in a production environment.
> SK
> Uri Dimant wrote:
> >impafait
> >You dont have to know a maximum number of items
> >Look at below soultion works for you
> >create table w
> >(
> > id int,
> > t varchar(50)
> >)
> >
> >insert into w values (1,'abc')
> >insert into w values (1,'def')
> >insert into w values (1,'ghi')
> >insert into w values (2,'ABC')
> >insert into w values (2,'DEF')
> >select * from w
> >
> >
> >create function dbo.fn_my ( @.id int)
> >returns varchar(100)
> >as
> >begin
> >declare @.w varchar(100)
> > set @.w=''
> > select @.w=@.w+t+',' from w where id=@.id
> > return @.w
> >end
> >
> >select id,
> >dbo.fn_my (dd.id)
> >from
> >(
> > select distinct id from w
> >)
> >as dd
> >
> >drop function dbo.fn_my
> >"imparfait" <imparfait@.noway.noway> wrote in message
> >news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
> >
> >
> >>Thank you very much for ur precise and detailed answer.
> >>
> >>
> >>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
> >>news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> >>
> >>
> >>There is no feature of SQL Server specifically for this requirement,
> >>though user-defined aggregates may be available in SQL Server 2005.
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>|||Nigel,
The use of variables is documented, but as far as I know, if a select
statement doesn't produce a result set, the only guarantee here is that
@.w will be assigned at least one value, and no guarantee that there will
be one assignment for every row in the table, let alone any guarantee
about the order of assignments. The only mention of this I know of is
http://support.microsoft.com/default.aspx?scid=kb;en-us;287515, which
says "The correct behavior for an aggregate concatenation query is
undefined", and while it also says "In order to achieve the expected
results from an aggregate concatenation query, apply any Transact-SQL
function or expression to the columns in the SELECT list rather than in
the ORDER BY clause," I have my doubts whether that sole off-handed
remark in a relatively confusing KB article is a real indication that
Microsoft intends this technique to be reliable.
SK
Nigel Rivett wrote:
>Think it's documented in a white paper somewhere - I've lost the reference now.
>Should be
>declare @.w varchar(100)
> select @.w=coalesce(@.w+',','') +t from w where id=@.id
> return @.w
>
>"Steve Kass" wrote:
>
>>Uri,
>> While this works in many situations, it's not supported or documented,
>>and I don't recommend using it in a production environment.
>>SK
>>Uri Dimant wrote:
>>
>>impafait
>>You dont have to know a maximum number of items
>>Look at below soultion works for you
>>create table w
>>(
>>id int,
>>t varchar(50)
>>)
>>insert into w values (1,'abc')
>>insert into w values (1,'def')
>>insert into w values (1,'ghi')
>>insert into w values (2,'ABC')
>>insert into w values (2,'DEF')
>>select * from w
>>
>>create function dbo.fn_my ( @.id int)
>>returns varchar(100)
>>as
>>begin
>>declare @.w varchar(100)
>> set @.w=''
>> select @.w=@.w+t+',' from w where id=@.id
>> return @.w
>>end
>>select id,
>>dbo.fn_my (dd.id)
>>from
>>(
>>select distinct id from w
>>)
>>as dd
>>drop function dbo.fn_my
>>"imparfait" <imparfait@.noway.noway> wrote in message
>>news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
>>
>>
>>Thank you very much for ur precise and detailed answer.
>>
>>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
>>news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
>>
>>
>>There is no feature of SQL Server specifically for this requirement,
>>though user-defined aggregates may be available in SQL Server 2005.
>>
>>
>>
>>
>>
>>
>>

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
Leila
I believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen the
strange behavior with later versions). It might also be related to whether all users use the same
Windows account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access profile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>
|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||I think that some versions of MAPI (Outlook) doesn't like several Windows accounts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They might not conflict, but
if you are logged in interactively using some other account, you might be in for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many of these problems...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...
> If this is the case, then closing the outlook must not solve the problem I think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
LeilaI believe this behavior can depend on your version of Outlook (I prefer Outl
ook 2000, I've seen the
strange behavior with later versions). It might also be related to whether a
ll users use the same
Windows account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...[v
bcol=seagreen]
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlo
ok is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on
one of client's
> computer, when outlook is open, it locks the file of profile and both SQL
Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access prof
ile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>[/vbcol]|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>|||I think that some versions of MAPI (Outlook) doesn't like several Windows ac
counts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They
might not conflict, but
if you are logged in interactively using some other account, you might be in
for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at
all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many
of these problems...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...[v
bcol=seagreen]
> If this is the case, then closing the outlook must not solve the problem I
think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>[/vbcol]

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
LeilaI believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen the
strange behavior with later versions). It might also be related to whether all users use the same
Windows account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access profile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I use Microsoft outlook as MAPI client. On my own computer, when the
>> outlook is open, both SQL Mail and Agent Mail can use the file of mail
>> profile to send email. But on one of client's computer, when outlook is
>> open, it locks the file of profile and both SQL Mail and Agent Mail fail
>> when try to connect to profile. When I close outlook, they can access
>> profile. This is strange behavior that I had never seen elsewhere.
>> Any help would be greatly appreciated.
>> Leila
>|||I think that some versions of MAPI (Outlook) doesn't like several Windows accounts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They might not conflict, but
if you are logged in interactively using some other account, you might be in for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many of these problems...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> It might also be related to whether all users use the same Windows account.
> If this is the case, then closing the outlook must not solve the problem I think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>>I believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen
>>the strange behavior with later versions). It might also be related to whether all users use the
>>same Windows account.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
>> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
>> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail
>> fail when try to connect to profile. When I close outlook, they can access profile. This is
>> strange behavior that I had never seen elsewhere.
>> Any help would be greatly appreciated.
>> Leila
>>
>

after downloading updates

after down loading updates my computer now reboots on its own. it now too
when starting up blue window comes up with "Begining dump of Physical memory"
can anyone help me fix?
You should contact Microsoft Product Support ASAP. Blue screens could be
caused by bad hardware, drivers or Windows bugs.
Adrian
"Marty" <Marty@.discussions.microsoft.com> wrote in message
news:CC1EB672-56C6-4D3A-B664-4C815A3408C8@.microsoft.com...
> after down loading updates my computer now reboots on its own. it now too
> when starting up blue window comes up with "Begining dump of Physical
> memory"
> can anyone help me fix?
|||In Most of the cases, there might be a RAM failure. Just Replace the RAM and
try rebooting the system.
thanks and regards
Chandra
"Marty" wrote:

> after down loading updates my computer now reboots on its own. it now too
> when starting up blue window comes up with "Begining dump of Physical memory"
> can anyone help me fix?

after downloading updates

after down loading updates my computer now reboots on its own. it now too
when starting up blue window comes up with "Begining dump of Physical memory
"
can anyone help me fix?You should contact Microsoft Product Support ASAP. Blue screens could be
caused by bad hardware, drivers or Windows bugs.
Adrian
"Marty" <Marty@.discussions.microsoft.com> wrote in message
news:CC1EB672-56C6-4D3A-B664-4C815A3408C8@.microsoft.com...
> after down loading updates my computer now reboots on its own. it now too
> when starting up blue window comes up with "Begining dump of Physical
> memory"
> can anyone help me fix?|||In Most of the cases, there might be a RAM failure. Just Replace the RAM and
try rebooting the system.
thanks and regards
Chandra
"Marty" wrote:

> after down loading updates my computer now reboots on its own. it now too
> when starting up blue window comes up with "Begining dump of Physical memo
ry"
> can anyone help me fix?

after downloading updates

after down loading updates my computer now reboots on its own. it now too
when starting up blue window comes up with "Begining dump of Physical memory"
can anyone help me fix?You should contact Microsoft Product Support ASAP. Blue screens could be
caused by bad hardware, drivers or Windows bugs.
Adrian
"Marty" <Marty@.discussions.microsoft.com> wrote in message
news:CC1EB672-56C6-4D3A-B664-4C815A3408C8@.microsoft.com...
> after down loading updates my computer now reboots on its own. it now too
> when starting up blue window comes up with "Begining dump of Physical
> memory"
> can anyone help me fix?|||In Most of the cases, there might be a RAM failure. Just Replace the RAM and
try rebooting the system.
thanks and regards
Chandra
"Marty" wrote:
> after down loading updates my computer now reboots on its own. it now too
> when starting up blue window comes up with "Begining dump of Physical memory"
> can anyone help me fix?