Correlated Subquery

Wonder if anyone can help

If I wanted 2 Values from a Correlated Subquery (same table join etc) would i have to have 2 subqueries or is there a better way to do it ?

ie.
SELECT t1.MyMainCode,
(SELECT SUM(t2.Qty) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty,
(SELECT SUM(t2.Qty2) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty2
FROM t1

Any Help appreciated

GWselect t1.MyMainCode
, sum(t2.Qty)
, sum(t2.Qty2)
from t1
left outer
join t2
on t1.MyMainCode = t2.MyMainCode
group
by t1.MyMainCode
rudy
http://r937.com/

Correlated Subquery

Hi,
Correlated subquery is written in the following syntax in Oracle.
Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from
Emplyee where sal>=X.sal)
How can this be written in SQL Server?
Please throw some light.
Thanks,
Su ManIn SQL server you use HAVING clause. For more information and the syntx
please refer to Books Online available.
thanks and regards
Chandra
"Su Man" wrote:

> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) fro
m
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>
>|||Your query is valid in SQL server also
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Su Man" <subu501@.yahoo.com> wrote in message
news:d4sl39$eqi$1@.news.mch.sbs.de...
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal))
> from
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>|||The query you posted is valid in SQL Server. This is Standard SQL syntax.
Did you try it?
David Portas
SQL Server MVP
--|||It is working. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gKOdneGg3IlLQOzfRVn-tA@.giganews.com...
> The query you posted is valid in SQL Server. This is Standard SQL syntax.
> Did you try it?
> --
> David Portas
> SQL Server MVP
> --
>

Correlated Subquery

Can you use a correlated subquery in a join expression, when you reference
the table your joining to?
Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:

> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?

Correlated Subquery

Can you use a correlated subquery in a join expression, when you reference
the table your joining to?Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:

> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?

Correlated subqueries? Please help!

Hi there,
I want to do something very simple, but in the most efficient way possible.
Let's say I have these two tables:
tblContacts(contact_id, contact_name)
tblPhones(phone_id, contact_id, phone_number, phone_rank)
When querying my contacts, I need to get the phone numbers in tblPhones
whose phone_rank is either = 1 or 2, and I want to display them in two
columns of the resultset, like this:
Name Phone1 Phone2
John Smith 555-5555 666-6666
I've tried correlated subqueries:
select contact_id, contact_name,
(select phone_number from tblPhones where phone_rank=1 and
tblPhones.contact_id = tblContacts.contact_id) as Phone1,
(select phone_number from tblPhones where phone_rank=2 and
tblPhones.contact_id = tblContacts.contact_id) as Phone2
from tblContacts
It works, but seems a bit slow...
I'm sure there are better ways to do this, but I can't figure it out and am
in a hurry...
Any hints would be appreciated!
TIA
Paul Dussault, MCPBest done client side but here's one method.
Works only if a contact can have only one phone per rank.
Untested:
select C.contact_name as [name],
(select phone_number from tblPhones where phone_rank = 1 and contact_ID =
C.contact_ID) as phone1,
(select phone_number from tblPhones where phone_rank = 2 and contact_ID =
C.contact_ID) as phone2
from tblContacts C
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:OCuGNWWYFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I want to do something very simple, but in the most efficient way
> possible.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and
> am in a hurry...
> Any hints would be appreciated!
>
> TIA
>
> Paul Dussault, MCP
>
>|||Paul Dussault wrote:

> Hi there,
> I want to do something very simple, but in the most efficient way possible
.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and a
m
> in a hurry...
> Any hints would be appreciated!
Something like this:
select contact_id, contact_name, p1.phone_number, p2.phone_number
from tblContacts c
left outer join tblPhones p1 on
p1.contact_id = c.contact_id and
p1.phone_rank = 1
left outer join tblPhones p2 on
p2.contact_id = c.contact_id and
p2.phone_rank = 2
Regards,
Berend. (-:|||Many Thanks!
I didn't think of the aliases...
Much faster!
Paul Dussault
"Berend de Boer" <berend@.xsol.com> wrote in message
news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
> Paul Dussault wrote:
>
> Something like this:
> select contact_id, contact_name, p1.phone_number, p2.phone_number
> from tblContacts c
> left outer join tblPhones p1 on
> p1.contact_id = c.contact_id and
> p1.phone_rank = 1
> left outer join tblPhones p2 on
> p2.contact_id = c.contact_id and
> p2.phone_rank = 2
> Regards,
> Berend. (-:|||Sorry if I sound ignorant, but how do aliases speed up query execution? I
always figured that, if anything, they would slightly slow down execution
since each alias has to be translated to the full path at run time (or at
least I've always assumed that that was what happened).
Chris
"Paul Dussault" wrote:

> Many Thanks!
> I didn't think of the aliases...
> Much faster!
> Paul Dussault
> "Berend de Boer" <berend@.xsol.com> wrote in message
> news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
>
>|||They don't really. What sped up your query was changing the correlated
subqueries into Left Joins.
Thomas
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:D4D67BF3-2DBB-4A7E-BD28-C84282929D1C@.microsoft.com...
<snip>sql

Correlated Subqueries?

I need to loop through several rows and find matches for values and the
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LPA few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:
>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20
2 4 B 40
3 4 C
4 9 ABC 200
5 8 X
6 8 Y
7 8 Z
8 9 XYZ 210
9 9 ABC-XYZ
10 5 X1
11 5 X2
12 12 PQ 90
13 12 P 50
14 12 Q 40
15 15 MD
16 15 M 240
17 15 D 110
18 17 D1 60
19 17 D2 70
20 16 M1 50
Ideally I want something like this.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20 470
2 4 B 40 470
3 4 C 470
4 9 ABC 200 470
5 8 X 470
6 8 Y 470
7 8 Z 470
8 9 XYZ 210 470
9 9 ABC-XYZ 470
10 5 X1 470
11 5 X2 470
12 12 PQ 90 180
13 12 P 50 180
14 12 Q 40 180
15 15 MD 530
16 15 M 240 530
17 15 D 110 530
18 17 D1 60 530
19 17 D2 70 530
20 16 M1 50 530
"Steve Kass" wrote:
> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
> >I need to loop through several rows and find matches for values and the
> >aggregate a different column. Being a VB type and not as much the sql type
> >my approach would be to loop through the rows and be done with it. But that
> >does not fit here I need to do it in stored procedure. I was told that
> >correlated sub queries are the ticket to do what I wish but I can't seem to
> >get to to work.
> >
> >My sql looks like this:
> >use Prototype_BIDW_TALX
> >--Variable Declaration
> >DECLARE @.ParentID int
> >DECLARE @.UltPID int
> >DECLARE @.UltParentName varchar (50)
> >DECLARE @.TotalEEs int
> >DECLARE @.UltTopAcct bit
> >
> >set @.ParentID = (select distinct [Parent ID] from Customers)
> >
> >SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> >sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
> >WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> >group by [Company ID] , [Name],[accttype]
> >
> >I get this error:
> >Sub query returned more than 1 value. This is not permitted when the sub
> >query follows =, !=, <, <= , >, >= or when the sub query is used as an
> >expression.
> >
> >Isn't the correlated sub query supposed to return more than one value so you
> >can basically loop through?
> >
> >What am I missing?
> >
> >LP
> >
> >
> >
> >
>|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20
>2 4 B 40
>3 4 C
>4 9 ABC 200
>5 8 X
>6 8 Y
>7 8 Z
>8 9 XYZ 210
>9 9 ABC-XYZ
>10 5 X1
>11 5 X2
>12 12 PQ 90
>13 12 P 50
>14 12 Q 40
>15 15 MD
>16 15 M 240
>17 15 D 110
>18 17 D1 60
>19 17 D2 70
>20 16 M1 50
>
>Ideally I want something like this.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20 470
>2 4 B 40 470
>3 4 C 470
>4 9 ABC 200 470
>5 8 X 470
>6 8 Y 470
>7 8 Z 470
>8 9 XYZ 210 470
>9 9 ABC-XYZ 470
>10 5 X1 470
>11 5 X2 470
>12 12 PQ 90 180
>13 12 P 50 180
>14 12 Q 40 180
>15 15 MD 530
>16 15 M 240 530
>17 15 D 110 530
>18 17 D1 60 530
>19 17 D2 70 530
>20 16 M1 50 530
>
>"Steve Kass" wrote:
>
>>LP,
>> I don't see a correlated subquery here. You have two subqueries,
>>(select distinct [Parent ID] from Customers) and (select distinct
>>[CompanyParent ID] from Customers), both of which look like standalone
>>queries, and one or the other returns more than one row, apparently. A
>>subquery is correlated if it depends on some column value from the outer
>>query, as in
>>select ...
>>from someTable as T1
>>where [condition contains a query like:] (select ... from anywhere where
>>[condition or expression in select list includes T1.somecolumn])
>>It's very hard to guess what you want here. If you could give the table
>>declarations, sample data, and show the output you are looking for, it
>>would help.
>>Steve Kass
>>Drew University
>>LP wrote:
>>
>>I need to loop through several rows and find matches for values and the
>>aggregate a different column. Being a VB type and not as much the sql type
>>my approach would be to loop through the rows and be done with it. But that
>>does not fit here I need to do it in stored procedure. I was told that
>>correlated sub queries are the ticket to do what I wish but I can't seem to
>>get to to work.
>>My sql looks like this:
>>use Prototype_BIDW_TALX
>>--Variable Declaration
>>DECLARE @.ParentID int
>>DECLARE @.UltPID int
>>DECLARE @.UltParentName varchar (50)
>>DECLARE @.TotalEEs int
>>DECLARE @.UltTopAcct bit
>>set @.ParentID = (select distinct [Parent ID] from Customers)
>>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>>group by [Company ID] , [Name],[accttype]
>>I get this error:
>>Sub query returned more than 1 value. This is not permitted when the sub
>>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>>expression.
>>Isn't the correlated sub query supposed to return more than one value so you
>>can basically loop through?
>>What am I missing?
>>LP
>>
>>
>>

Correlated Subqueries?

I need to loop through several rows and find matches for values and the
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LP
A few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>
|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:

>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>
|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.
|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20
24B40
34C
49ABC200
58X
68Y
78Z
89XYZ210
99ABC-XYZ
105X1
115X2
1212PQ90
1312P50
1412Q40
1515MD
1615M240
1715D110
1817D160
1917D270
2016M150
Ideally I want something like this.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20470
24B40470
34C470
49ABC200470
58X470
68Y470
78Z470
89XYZ210470
99ABC-XYZ470
105X1470
115X2470
1212PQ90180
1312P50180
1412Q40180
1515MD530
1615M240530
1715D110530
1817D160530
1917D270530
2016M150530
"Steve Kass" wrote:

> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
>
|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
[vbcol=seagreen]
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20
>24B40
>34C
>49ABC200
>58X
>68Y
>78Z
>89XYZ210
>99ABC-XYZ
>105X1
>115X2
>1212PQ90
>1312P50
>1412Q40
>1515MD
>1615M240
>1715D110
>1817D160
>1917D270
>2016M150
>
>Ideally I want something like this.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20470
>24B40470
>34C470
>49ABC200470
>58X470
>68Y470
>78Z470
>89XYZ210470
>99ABC-XYZ470
>105X1470
>115X2470
>1212PQ90180
>1312P50180
>1412Q40180
>1515MD530
>1615M240530
>1715D110530
>1817D160530
>1917D270530
>2016M150530
>
>"Steve Kass" wrote:
>

correlated subqueries

Where can I find some good examples of understanding how correlated
subqueries work ?Hi Hassan
How about reading
Inside Microsoft SQL Server 2005 T-SQL Querying ISBN 0735623139
Inside Microsoft SQL Server 2005 Query Tuning and Optimization ISBN 0735621969
John
"Hassan" wrote:
> Where can I find some good examples of understanding how correlated
> subqueries work ?
>
>

Correlated SUB Queries?

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoiti'm not sure where the correlation comes in

these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:

select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies

I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it

;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.

The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).

NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)

select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery

Correlated SUB Queries?

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoitplease don't cross-post

http://www.dbforums.com/showthread.php?t=1117027sql

Correlated query in SELECT clause

There is a problem when I include correlated query in the SELECT clause in
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?

Correlated and aggregate sql query

I have a table similar to the following:

ID | Name ID | Period From | Period To | Percentage |

-----------------------

Important - Each person can have more than one entry.

What I am trying to do is get the last percentage that each person obtained.

The only way I have been able to do this is by the following:

SELECT * FROM myTable

LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1

ON myTable.NameID = t1.NameID

WHERE myTable.PeriodTo = t1.PeriodTo

I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

|||This does not work. It finds the rows where the periodTo is equivalent to the maximum periodTo. I need to find the maximum periodTofor each nameID. Its is equivalent toSELECT ID, NameID, ..., MAX(PeriodTo)FROM myTableGROUP BY ID, NameID, ...|||

You can use ROW_Number function if you use SQL Server 2005:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(SELECT NameID, PeriodFrom, PeriodTo, Percentage,ROW_Number()OVER(PARTITIONBY NameIDORDERBY PeriodToDESC)as RowNum

FROM mytable)AS t

WHERE RowNum=1

|||

Can you post some sample data from the table and expected output? This makes it easier for us to see what should be achieved.

|||

if PeriodTo unique then it works

(

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

in the inner query it selects all the periodto which is max to the nameid >>>then it matches the PeriodTo from the outer query.
select Max(PeriodTo) from myTable group by NameIDthis portion returns the Max PeriodTo for each NameID which is unique

)

|||

Sorry Kamrul, but that is not what I am looking for. Maybe I have not explained the problem properly, so here is some sample data (obviously simplified).

An example of the table is (note that this is british dates - i.e. dd/mm/yy):

 ID | NameID | PeriodTo | Comments |---------------------- 1 | Mark | 01/01/01 | ComMark1 | 2 | Mark | 01/06/01 | ComMark2 | 3 | Mark | 01/11/01 | ComMark3 | 4 | Ken | 01/01/01 | ComKen1 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn1 |---------------------

And the result I am trying to get is :

 ID | NameID | PeriodTo | Comments |---------------------- 3 | Mark | 01/11/01 | ComMark3 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn |----------------------

Kamrul's code doesn't work because it will return every row.

The code I wrote does return this, but I was wondering whether there was a nice way of doing this kind of correlated aggregated query. I'm looking for the most efficient method. I should also point out that I am using sql server 2000.

Jagdip

|||

Hi,

Please try this.

SELECT *FROM MyTable OutMyTable
where PeriodTo = (select Max(PeriodTo)from MyTable InMyTableWHERE InMyTable.NameID=OutMyTable.NameID group by NameID)

Regards,

Gaurang Majithiya

|||

For SQL Server 2000:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(

SELECT NameID, PeriodFrom, PeriodTo, Percentage,(SELECTCOUNT(*)FROM mytable aWHERE a.NameID=b.NameIDAND a.PeriodTo>=b.PeriodTo)as RowNum

FROM mytable b)AS t

WHERE RowNum=1

Correction: Minor pdf-rendering error.

Hi.

We use RS intensively in our app. However, we have had several reports from our customers, that they cannot open our pdf-files on their macintosh computers. I have investigated the problem, and found that this is due to a very simple omission in the rendered pdf.

Apparently the 1.3 specs requires a "carriage return" (char decimal value 13) after the %%EOF-element. If I add this manually, the files open just fine on mac.

Is this a known issue?

Is there some way to get around it, apart from adding it manually in the code?

- Kristian

(Note: There might be some pdf-readers for mac that can read these files, but we have no way of controlling our customers ap-selection.)

I was a little fast on the trigger there.

After manually adding the carriage return, they still do not open on mac. It only made some of the unix-tools we used for testing stop complaining (pdf2ps).

Are there any known issues with pdf-files from RS and Macintosh compatibility.

|||

We found out, that if we installed Adobe Reader on the Mac, we could view our file.

So it's propably the internal (default) pdf-viewer, that doesn't understand the RS pdf format.

Correction - Interesting Behavior of Service Broker Queues

Hello All:

I've been experimenting with the new SQL Server Service Broker, and I think I've discovered some interesting behavior. Service Broker relies on "Queues" to store messages that need to be processed. Service Broker operates by sending a message from one Queue (the INITIATOR Quque) to another Queue (the TARGET Queue). A Queue can have an "Activation Stored Procedure" associated with it. This procedure is what actually processes the messages in the Queue.

The first behavior I obeserved related to the setting of a Queue's RETENTION parameter. The RETENTION parameter indicates whether or not the Queue will retain a copy of the messages it receives. By default, the parameter's value is "OFF" (meaning it will not retain messages). In the Activation Stored Procedure of my TARGET Queue, I used "sp_send_dbmail" to send an e-mail message. I wanted to capture the "conversation_handle" (a uniqueidentifier that identifies a particular message) and include it in the body of the e-mail. I was unable to capture it, because the Queue's RETENTION parameter was "OFF". When I tried to capture the conversation_handle from the INITIATOR queue (whose RETENTION parameter was "ON"), I was successful. The moral of the story is you apparently need to have RETENTION = "ON" if you need to capture information from a Queue.

The second behavior I observed relates to the setting of a Queue's MAX_QUEUE_READERS setting. This setting allows you to automatically invoke multiple instances of the Activation Stored Procedure. As messages come into the Queue, the Queue creates an additional instance of the Activation Stored Procedure up to the number you specified in the MAX_QUEUE_READERS setting. This allows parallel processing of messages in the Queue. There is also a programming technique called a "RECEIVE LOOP" which is used for processing messages. In the RECEIVE LOOP, you have a parameter called WAITFOR which tells the Queue whether it should stay on constant alert for new messages or whether it should time out after a specified number of seconds.

If you have the Queue wait indefinitely (by not specifying a TIMEOUT value in the WAITFOR statement) and you have invoked multiple copies of the Activation Stored Procedure, the procedure will remain in memory indefinitely. Therefore, if you make a change to the code of the Activation Stored Procedure, the change will NOT be reflected in the Activation Stored Procedure until you change the STATUS of the Queue. I had changed my procedure so that it would not send an e-mail, but the e-mails kept coming. The e-mails did not stop until I executed an ALTER QUEUE statement. I ran "ALTER QUEUE queue_name WITH STATUS = OFF;" and then I ran "ALTER QUEUE queue_name WITH STATUS = ON;" After that, the changes were reflected in the procedure.

Be aware of this behavior as you design your Queues.

"The moral of the story is you apparently need to have RETENTION = ON if you need to capture information from a Queue."

I wrong about this. The reason I wasn't capturing the information the first time around was because I didn't alter the queue to reset the STATUS. I have since been able to turn off RETENTION while having the ability to capture information from the queue.

|||

Ken, some necessary corrections:

- conversation_handle identifies conversations endpoints, not messages. Initiator and target have distinct conversation_handles. Conversation_id is identifies conversations and is shared by initiator and target. A message is unique by conversation_handle AND message sequence number.

- RETENTION has nothing to do with the capability to 'capture' conversation handles. To obtain the the conversation handle on the initiator side one must get it from the output of BEGIN DIALOG. To obtain the conversation handle on the target side one must put the conversation_handle into the RECEIVE projection list. RETENTION is totally unrelated to this, it is intended for helping application implement compensation logic on failed dialogs. RETENTION keeps a copy of sent and received messages for the duration of the conversation. These messages are visible for the SELECT statement, but not for the RECEIVE statement.

- any stored proc that has an infinite loop, once launched, will stay in memory until killed (explicitly or implicitly) or until server shutdown. This is not specific to activation, is general SQL Server behavior. The only difference is that when this happens by invocation from a user connection it is usually killed implicitly, by that connection being closed or by the batch being aborted. Turning the queue OFF will cause the infinite WAITFOR(RECEIVE...) in the procedure to error and thus exit the RECEIVE loop. One could just as easily KILL the procedure (lookup the SPID in sys.dm_broker_activated_tasks). Needless to say, activated procedures with infinite loops are a very bad practice. It leads to unnecessary consumtion of server resources (threads at the very least) when there are not messages to receive.

- altering the code of an stored proc does not affect the exisiting running instances of that stored proc. This behavior is general in SQL Server, not particular to activated stored procs. Only new invocations of the stored proc will detect the change.

I hope this clarifies some of the behavior you observed.
HTH,

~ Remus

|||

Thanks for the clarifications. I wasn't thinking of the RECEIVE LOOP as an infinite loop, since you don't see the timer on the Query Editor window keep running, but it obviously is one. I also wasn't clear about the circumstances under which a stored procedure stays in memory, so thanks for that information as well.

Now I want to show you a code sample to clarify what I am doing. Is this what you mean by "put the conversation_handle into the RECEIVE projection list"?

<code>

USE ServiceBrokerTest

GO

/*

DROP PROCEDURE usp_FTPService

*/

ALTER PROCEDURE usp_FTPService

AS

BEGIN

DECLARE @.conversationHandle UNIQUEIDENTIFIER

DECLARE @.messageBody NVARCHAR(MAX)

DECLARE @.messageTypeName SYSNAME

DECLARE @.eMailBody NVARCHAR(MAX)

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

WAITFOR (

RECEIVE @.messageTypeName=message_type_name,

@.conversationHandle=conversation_handle,

@.messageBody=message_body

FROM [FTPQueue])

-- If a message was received, process it, else skip

IF (@.@.rowcount <= 0)

BREAK;

-- Process ObjectFTPStartRequest message

IF @.messageTypeName = N'ObjectFTPStartRequest'

BEGIN

SET @.eMailBody = 'FTP job for Object conversation ' + CAST(@.conversationHandle AS NVARCHAR(MAX)) + ' completed'

EXEC msdb.dbo.sp_send_dbmail @.profile_name = 'Ken',

@.recipients = 'username@.domain.com',

@.subject = 'FTP Service Response (ASP)',

@.body = @.eMailBody;

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [LookupRaceFTPStartResponse]

(N'<ObjectFTPStartResponse>

FTP job for Object completed

</ObjectFTPStartResponse>')

END CONVERSATION @.conversationHandle

END

COMMIT

END

COMMIT

END

</code>

If that's what you meant, then that's what I was doing. The reason I was having a problem was solely due to the fact that the first version of the procedure was still running in memory.

|||

To put the conversation_handle into the projection list it simply means to have the conversation_handle in the list of columns you RECEIVE. You are doing this fine.

The code you posted needs two changes:
- the WAITFOR(RECEIVE ...) must have a timeout. Typically, the value should be a few seconds. The ideea is that the procedure should linger a bit, in hope a new message arrives and it can be processed. Too short interval and the procedure is gonna miss new messages, exiting and being re-launched, which is a somewhat expensive operation. Too long and your procedure is going to consume server resources w/o doing anything.
- You must add a TOP(1) clause to the RECEIVE. Otherwise you will receive multiple messages, but only process the last one.

HTH,

~ Remus

|||

I don't think it's proper to say the TIMEOUT is required since the T-SQL language does not require a TIMEOUT with the WAITFOR clause. If it were required I would get a syntax or runtime error upon execution of the code.

As for whether it's a good idea to use a TIMEOUT, here's what Roger Wolter (Microsoft's Group Program Manager for SQL Server Service Broker) says in his book "The Rational Guide to SQL Server 2005 Service Broker" (Page 56):

"A WAITFOR with no timeout means the RECEIVE will wait for a message no matter how long it takes. Waiting for a message to appear on the queue is generally more efficient than polling the queue by using the RECEIVE command periodically. The main exception to this would be a low priority queue that is only polled for messages periodically when higher priority queues are empty."

As for the use of the TOP clause, I quote Roger again (again on Page 56):

"Retreiving all the messages available utilizes fewer server resources than multiple RECEIVE commands, so you should avoid the TOP clause whenever possible. The main reason to use the TOP clause is in stored procedures where receiving one message at a time into TSQL variables simplifies programming, or when there's a chance that a RECEIVE command will retrieve more messages than you want to process in a single transaction."

|||

Indeed, the TIMEOUT is not syntactically required. But no TIMEOUT in activated stored proc loop practicaly turns it into an infinite loop and you'll run into the problems you encountered.

Assignment in RECEIVE projection list (e.g. @.conversation_handle = conversation_hanlde) requires a TOP(1). It is true that using the TOP clause is less efficient, but otherwise the procedure gets significantly more complex. You'd have to RECEIVE INTO @.tableVariable, open a cursor over the table variable and scan this cursor. That is what the "simplifies programming" part of he's quote reffers to. You either use a simple T-SQL program (i.e. @.variables), but pay the penalty of the TOP clause, or you use a more complex T-SQL program (i.e. @.tablevariable and cursor). Roger himself mentions this, see the two examples of RECEIVE on page 57.
Note that if 99% of your RECEIVEs would return only one message anyway (as is often the case), then the TOP is not expensive at all.

See the Receive Loop example on page 63, you'll see that Roger uses both a TOP(1) clause and a 5s TIMEOUT.

HTH,
~ Remus

sql

Correction

I forgot to mention that I was refering to merge replication
Shmuel
The 'disturbed' batch will be redone and subsequently further batches will
be done.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you,
Shmuel
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ezk0eMZKFHA.2748@.TK2MSFTNGP09.phx.gbl...
> The 'disturbed' batch will be redone and subsequently further batches will
> be done.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Correcting old data

Hi,
What's the best way to handle data reloads when you have implemented Type 2
slowly changing dimension?
For example, you might have an Account dimension, with a status of open or
closed. Someone accidentally closes the account, which we then extract and
update, creating a new record. They then realise the mistake and re-open the
account. The next extract would be create a third record, with the original
value.
On top of this, fact data may have been loaded against the second (invalid)
record.
If the source system doesn't keep a history of these changes, then we can't
redo the extract because it will contain a mix of valid and invalid changes.
Thanks,
Wreck.Hello Wreck,
Have you considered type 1 for dimensions that are affected by this
issue? This way the transactions are not effected. You can have a
mixture of type 1 and 2 in your stars.
Check out:
http://bi-on-sql-server.blogspot.co...r.blogspot.com/|||Hi Wreck,
you are entering 'religious territory' ground.....
There are two religions here:
1. Go back and correct data 'known to be incorrect'.
2. Retain data known to be incorrect and enter new transactions which
update the DW such that it now represents the data 'known' to be
correct. (Which is really just 'thought to be correct now'.)
I come down in the camp of retain the incorrect data and enter new
transactions such the data is now set to the 'thought to be correct
value'. In your case, the account would be 're-opened' and it would
occur as 're-opened'.
Why?
Because going back and updating data is a very slippery slope. Where
does it end? And if decisions were made against data that was presented
at a certain point in time and then the underlying data is 'corrected'
and people come along to review those decisions and the data says
'these decisions are not well founded on the data' what does one do?
One must remember that data is never, ever 'true' or 'correct' it is
only ever 'thought to be correct/true' which means that data that we
have realised is incorrect and we now believe to be true has the same
likelyhood of being incorrect as the data we just changed....
Whereas, if we retain the fact that we found data to be incorrect and
we enter transactions to place new transactions to correct that data we
can see the information on which decisions were taken at the time. We
can also see how often data is being corrected....perhaps there are
underlying issues for a high rate of data corrections occurring such as
lack of proper training in the call centers taking calls and entering
data? It happens...
In some cases it is a legal requirement not to correct historical data.
For example, in most countries it is illegal to update the financial
statements of a company after the close of the annual accounts...yet
clearly there have been some very 'incorrect' entries in the financials
of some very large companies over the last few years.
Best Regards
Peter Nolan
www.peternolan.com|||Thanks for your feedback guys.
Myles, the system currently has Type I changes across the board, and
naturally doesn't track history very well. I want to implement Type II, but
the data in the source systems has its issues, hence my concerns.
There are steps being taken to improve the data quality in the source system
s.
Peter, I'm inclined to agree with you that we don't correct anything,
especially once it's been reported on. However, the business is still fairly
new to the whole warehouse concept - prior to this, it was the usual story o
f
lots of Access and Excel apps extracting and manipulating data for reporting
.
When these data errors are found, they don't really understand why it's so
difficult to make a correction.
Thanks,
Wreck.
"Peter Nolan" wrote:

> Hi Wreck,
> you are entering 'religious territory' ground.....
> There are two religions here:
> 1. Go back and correct data 'known to be incorrect'.
> 2. Retain data known to be incorrect and enter new transactions which
> update the DW such that it now represents the data 'known' to be
> correct. (Which is really just 'thought to be correct now'.)
> I come down in the camp of retain the incorrect data and enter new
> transactions such the data is now set to the 'thought to be correct
> value'. In your case, the account would be 're-opened' and it would
> occur as 're-opened'.
> Why?
> Because going back and updating data is a very slippery slope. Where
> does it end? And if decisions were made against data that was presented
> at a certain point in time and then the underlying data is 'corrected'
> and people come along to review those decisions and the data says
> 'these decisions are not well founded on the data' what does one do?
> One must remember that data is never, ever 'true' or 'correct' it is
> only ever 'thought to be correct/true' which means that data that we
> have realised is incorrect and we now believe to be true has the same
> likelyhood of being incorrect as the data we just changed....
> Whereas, if we retain the fact that we found data to be incorrect and
> we enter transactions to place new transactions to correct that data we
> can see the information on which decisions were taken at the time. We
> can also see how often data is being corrected....perhaps there are
> underlying issues for a high rate of data corrections occurring such as
> lack of proper training in the call centers taking calls and entering
> data? It happens...
> In some cases it is a legal requirement not to correct historical data.
> For example, in most countries it is illegal to update the financial
> statements of a company after the close of the annual accounts...yet
> clearly there have been some very 'incorrect' entries in the financials
> of some very large companies over the last few years.
> Best Regards
> Peter Nolan
> www.peternolan.com
>|||Hi Wreck,
I have been doing DWing for 15 years...and I have seen a great many
cases of significant destruction of value within a company on the basis
that 'the business users do not understand'.......and I have seen
even more because 'the IT people do not understand'.!!!... ;-)
Indeed, it seems that those who 'do not understand' seem to think the
onus is on those who 'do understand' to explain to them what it is that
is not understood to persuade them not to destroy value in their
company. Those who 'do not understand' do not seem to think the onus
is on them to make an effort to understand to assist them create value
in their company...and so value is commonly destroyed....it would be
funnier if it was not so common...(LOL)!!
You are lucky in that today there are tons of materials available on
reasonable practices for DW development.
However, what is published is never the 'leading edge' and is rarely
'best practice'.
'Leading edge' and 'Best Practice' are retained within companies for
competitive advantage...;-)
Best Regards
Peter Nolan
www.peternolan.com

Correcting old data

Hi,
What's the best way to handle data reloads when you have implemented Type 2
slowly changing dimension?
For example, you might have an Account dimension, with a status of open or
closed. Someone accidentally closes the account, which we then extract and
update, creating a new record. They then realise the mistake and re-open the
account. The next extract would be create a third record, with the original
value.
On top of this, fact data may have been loaded against the second (invalid)
record.
If the source system doesn't keep a history of these changes, then we can't
redo the extract because it will contain a mix of valid and invalid changes.
Thanks,
Wreck.
Hello Wreck,
Have you considered type 1 for dimensions that are affected by this
issue? This way the transactions are not effected. You can have a
mixture of type 1 and 2 in your stars.
Check out:
http://bi-on-sql-server.blogspot.com...-changing.html
I wrote a blog on type 2 and type 1.
Type 2 only works at the point of time for the extracts as you
mentioned. One way of resolving this problem with dirty changes is to
take more frequent extracts to capture more of the changes.
Another option is to remove dirty changes from the dimension if there
are no corresponding fact transactions.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi Wreck,
you are entering 'religious territory' ground.....
There are two religions here:
1. Go back and correct data 'known to be incorrect'.
2. Retain data known to be incorrect and enter new transactions which
update the DW such that it now represents the data 'known' to be
correct. (Which is really just 'thought to be correct now'.)
I come down in the camp of retain the incorrect data and enter new
transactions such the data is now set to the 'thought to be correct
value'. In your case, the account would be 're-opened' and it would
occur as 're-opened'.
Why?
Because going back and updating data is a very slippery slope. Where
does it end? And if decisions were made against data that was presented
at a certain point in time and then the underlying data is 'corrected'
and people come along to review those decisions and the data says
'these decisions are not well founded on the data' what does one do?
One must remember that data is never, ever 'true' or 'correct' it is
only ever 'thought to be correct/true' which means that data that we
have realised is incorrect and we now believe to be true has the same
likelyhood of being incorrect as the data we just changed....
Whereas, if we retain the fact that we found data to be incorrect and
we enter transactions to place new transactions to correct that data we
can see the information on which decisions were taken at the time. We
can also see how often data is being corrected....perhaps there are
underlying issues for a high rate of data corrections occurring such as
lack of proper training in the call centers taking calls and entering
data? It happens...
In some cases it is a legal requirement not to correct historical data.
For example, in most countries it is illegal to update the financial
statements of a company after the close of the annual accounts...yet
clearly there have been some very 'incorrect' entries in the financials
of some very large companies over the last few years.
Best Regards
Peter Nolan
www.peternolan.com
|||Thanks for your feedback guys.
Myles, the system currently has Type I changes across the board, and
naturally doesn't track history very well. I want to implement Type II, but
the data in the source systems has its issues, hence my concerns.
There are steps being taken to improve the data quality in the source systems.
Peter, I'm inclined to agree with you that we don't correct anything,
especially once it's been reported on. However, the business is still fairly
new to the whole warehouse concept - prior to this, it was the usual story of
lots of Access and Excel apps extracting and manipulating data for reporting.
When these data errors are found, they don't really understand why it's so
difficult to make a correction.
Thanks,
Wreck.
"Peter Nolan" wrote:

> Hi Wreck,
> you are entering 'religious territory' ground.....
> There are two religions here:
> 1. Go back and correct data 'known to be incorrect'.
> 2. Retain data known to be incorrect and enter new transactions which
> update the DW such that it now represents the data 'known' to be
> correct. (Which is really just 'thought to be correct now'.)
> I come down in the camp of retain the incorrect data and enter new
> transactions such the data is now set to the 'thought to be correct
> value'. In your case, the account would be 're-opened' and it would
> occur as 're-opened'.
> Why?
> Because going back and updating data is a very slippery slope. Where
> does it end? And if decisions were made against data that was presented
> at a certain point in time and then the underlying data is 'corrected'
> and people come along to review those decisions and the data says
> 'these decisions are not well founded on the data' what does one do?
> One must remember that data is never, ever 'true' or 'correct' it is
> only ever 'thought to be correct/true' which means that data that we
> have realised is incorrect and we now believe to be true has the same
> likelyhood of being incorrect as the data we just changed....
> Whereas, if we retain the fact that we found data to be incorrect and
> we enter transactions to place new transactions to correct that data we
> can see the information on which decisions were taken at the time. We
> can also see how often data is being corrected....perhaps there are
> underlying issues for a high rate of data corrections occurring such as
> lack of proper training in the call centers taking calls and entering
> data? It happens...
> In some cases it is a legal requirement not to correct historical data.
> For example, in most countries it is illegal to update the financial
> statements of a company after the close of the annual accounts...yet
> clearly there have been some very 'incorrect' entries in the financials
> of some very large companies over the last few years.
> Best Regards
> Peter Nolan
> www.peternolan.com
>
|||Hi Wreck,
I have been doing DWing for 15 years...and I have seen a great many
cases of significant destruction of value within a company on the basis
that 'the business users do not understand'.......and I have seen
even more because 'the IT people do not understand'.!!!... ;-)
Indeed, it seems that those who 'do not understand' seem to think the
onus is on those who 'do understand' to explain to them what it is that
is not understood to persuade them not to destroy value in their
company. Those who 'do not understand' do not seem to think the onus
is on them to make an effort to understand to assist them create value
in their company...and so value is commonly destroyed....it would be
funnier if it was not so common...(LOL)!!
You are lucky in that today there are tons of materials available on
reasonable practices for DW development.
However, what is published is never the 'leading edge' and is rarely
'best practice'.
'Leading edge' and 'Best Practice' are retained within companies for
competitive advantage...;-)
Best Regards
Peter Nolan
www.peternolan.com

correct way to update a db from a dataset using sqlxml

I have a dataset populated correctly from a mapping XSD (which maps a many to
many rel. from the db).
but on using the SQLXMLDataAdapter.update() method gives me Invalid column
errors. Even with a MS produced northwind schema. Is there a standard code
example of how to do an update like this?
thankyou,
Gordon Doherty
Hi Gordy:
Here is a kb article which has several links to address what you're looking
for: http://support.microsoft.com/default...b;en-us;313483
Regards,
John Cross
This posting is provided "AS IS" with no warranties, and confers no rights.

Correct Way to Reboot a SQL Server

When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!We have always just used the system shut down from the
start or ctl alt del option... and no problems..
>--Original Message--
>When you guys perform a regular reboot of your production
SQL Servers,
>do you just use the Start button and Shutdown/Restart?
Or do you stop
>SQL Server first, then reboot the server?
>Stopping SQL Server first, then rebooting the server, has
always
>seemed obvious to me. My boss wants to just use the
Start button and
>reboot the boxes, because this has never caused us any
problems, and
>will save the time of taking the extra steps to stop SQL
Server first.
>Now that I'm looking for it, I can't find one clear bit
of Best
>Practice documentation on this!
>Thanks much!
>.
>|||I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> > When you guys perform a regular reboot of your production SQL Servers,
> > do you just use the Start button and Shutdown/Restart? Or do you stop
> > SQL Server first, then reboot the server?
> >
> > Stopping SQL Server first, then rebooting the server, has always
> > seemed obvious to me. My boss wants to just use the Start button and
> > reboot the boxes, because this has never caused us any problems, and
> > will save the time of taking the extra steps to stop SQL Server first.
> >
> > Now that I'm looking for it, I can't find one clear bit of Best
> > Practice documentation on this!
> >
> > Thanks much!|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> > You can just reboot the server. When Windows does a normal shutdown, it
> > first stops all running services, including SQL server.
> >
> > "James" <throwawaythrowaway@.hotmail.com> wrote in message
> > news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> > > When you guys perform a regular reboot of your production SQL Servers,
> > > do you just use the Start button and Shutdown/Restart? Or do you stop
> > > SQL Server first, then reboot the server?
> > >
> > > Stopping SQL Server first, then rebooting the server, has always
> > > seemed obvious to me. My boss wants to just use the Start button and
> > > reboot the boxes, because this has never caused us any problems, and
> > > will save the time of taking the extra steps to stop SQL Server first.
> > >
> > > Now that I'm looking for it, I can't find one clear bit of Best
> > > Practice documentation on this!
> > >
> > > Thanks much!sql

Correct Way to Reboot a SQL Server

When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2
976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]