Problem with SP vs QA

I have a strange issue. When I execute an SP and pass it
the single parameter (an int) that it expects, it takes 6
minutes to run. It's just a select statement with the
parameter in the where clause. When I copy this to QA and
declare a variable as int, set it to the same parameter I
passed to the SP and use it in the where clause, it takes
4 seconds to run in QA. Not sure why this is happening.
SQL 2000, SP3a. I'm running these on the same server.
Here's the SP, just so you can see what it looks like:
CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
(
@.AdvRecordIDint
)
AS
/************************************************** ********
********************
* Name: usp_ATLAS_HistoricalAdvanceData_Select
* Desc: A stored procedure to return the latest modified
Advance record from the history.
*
* Return values:
*
* Called by:
*
* Parameters:
*Input
*--
*@.AdvRecordIDint
*
*
* Date: 11/7/2003
************************************************** *********
********************
* Change History
************************************************** *********
********************
* Date:Author:Description:
* ---
************************************************** *********
********************/
BEGIN -- main procedure
SET NOCOUNT ON
Select
hst.AdvRecordID,
hst.CustomerID,
adt.AdvTypeID,
hst.AdvTypeCode,
hst.AdvanceID,
hst.SpecialOfferID,
hst.PrePayOptionsCode,
hst.Term,
tmu.TermUnitID,
hst.TermUnitName as TermUnit,
hst.MatDate,
hst.TradeDate,
hst.SettleDate,
hst.TransDate,
hst.Amt,
st1.ApprovalStatusID,
st2.ActivationStatusID,
hst.TransContactFirstName,
hst.TransContactLastName,
dbo.ufn_ATLAS_GetSwapNumbers
(hst.AdvRecordID) as SwapNum,
dbo.ufn_ATLAS_GetARBNumber
(hst.AdvRecordID, 1) AS ARBNumber,
adr.AdvRate as CurrentRate,
hst.UsesLondonHoliday,
hst.AuthorizedContactFlag,
hst.ReissueFlag,
ppo.PrePayOptionsID,
ppo.PrePayOptionsDesc as PrepaymentOption,
cst.CustID AS BusinessCustomerID,
cst.CustomerName AS CustomerFullName,
cst.Address,
cst.City,
cst.State AS StateCode,
cst.Zip,
hst.ModByLogin
From GUTSHistoryAdvances hst
Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
= 1
And adt.AdvTypeCode = hst.AdvTypeCode
Inner Join vw_ATLAS_CustomerInfo cst ON
hst.CustomerID = cst.CustomerID
Inner Join GUTSApprovalStatuses st1 On
st1.ActiveFlag = 1
And st1.ApprovalStatusCode =
hst.ApprovalStatusCode
Inner Join GUTSActivationStatuses st2 On
st2.ActiveFlag = 1
And st2.ActivationStatusCode =
hst.ActivationStatusCode
Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
= 1
And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
And tmu.TermUnitName = hst.TermUnitName
left outer JOin GUTSAdvanceRates adr On
adr.ActiveFlag = 1
And hst.AdvRecordID = adr.AdvRecordID
And Not adr.AdvRate Is Null
And adr.RateEndDate Is Null
Where hst.ActiveFlag = 1
And hst.AdvRecordID = @.AdvRecordID
And hst.ToDate = (select max(ToDate) from
GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID =
hst.AdvRecordID)
RETURN(0)
END -- main procedure
As you can see, @.AdvRecordID is the only parameter. From
QA with the select statement pasted and @.AdvRecordID
declared as int and used in the where clause this takes 4
seconds. With the same AdvRecordID used in the SP (called
in QA) it takes 6 minutes.
Any thoughts?
Thanks,
Van
Although unlikely to cause such a difference, it might be a problem of an
out-of-date execution plan compiled into the sp in cache. Can you try
running sp_recompile usp_ATLAS_HistoricalAdvanceData_Select then run it
twice and check the second running time.
Regards,
Paul Ibison
|||Van,
take your set nocount on out of the begin ... end block. In fact you should
remove the begin/end altogether -- it has no reason to be there. See
whether this works. It should not be due to recompilation since that is
what you do in the QA when first time running the query.
Quentin
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b84d01c43790$d48a0590$a101280a@.phx.gbl...
> I have a strange issue. When I execute an SP and pass it
> the single parameter (an int) that it expects, it takes 6
> minutes to run. It's just a select statement with the
> parameter in the where clause. When I copy this to QA and
> declare a variable as int, set it to the same parameter I
> passed to the SP and use it in the where clause, it takes
> 4 seconds to run in QA. Not sure why this is happening.
> SQL 2000, SP3a. I'm running these on the same server.
> Here's the SP, just so you can see what it looks like:
> CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
> (
> @.AdvRecordID int
> )
> AS
> /************************************************** ********
> ********************
> * Name: usp_ATLAS_HistoricalAdvanceData_Select
> * Desc: A stored procedure to return the latest modified
> Advance record from the history.
> *
> * Return values:
> *
> * Called by:
> *
> * Parameters:
> * Input
> * --
> * @.AdvRecordID int
> *
> *
> * Date: 11/7/2003
> ************************************************** *********
> ********************
> * Change History
> ************************************************** *********
> ********************
> * Date: Author: Description:
> * -- -- --
> --
> ************************************************** *********
> ********************/
> BEGIN -- main procedure
> SET NOCOUNT ON
> Select
> hst.AdvRecordID,
> hst.CustomerID,
> adt.AdvTypeID,
> hst.AdvTypeCode,
> hst.AdvanceID,
> hst.SpecialOfferID,
> hst.PrePayOptionsCode,
> hst.Term,
> tmu.TermUnitID,
> hst.TermUnitName as TermUnit,
> hst.MatDate,
> hst.TradeDate,
> hst.SettleDate,
> hst.TransDate,
> hst.Amt,
> st1.ApprovalStatusID,
> st2.ActivationStatusID,
> hst.TransContactFirstName,
> hst.TransContactLastName,
> dbo.ufn_ATLAS_GetSwapNumbers
> (hst.AdvRecordID) as SwapNum,
> dbo.ufn_ATLAS_GetARBNumber
> (hst.AdvRecordID, 1) AS ARBNumber,
> adr.AdvRate as CurrentRate,
> hst.UsesLondonHoliday,
> hst.AuthorizedContactFlag,
> hst.ReissueFlag,
> ppo.PrePayOptionsID,
> ppo.PrePayOptionsDesc as PrepaymentOption,
> cst.CustID AS BusinessCustomerID,
> cst.CustomerName AS CustomerFullName,
> cst.Address,
> cst.City,
> cst.State AS StateCode,
> cst.Zip,
> hst.ModByLogin
>
> From GUTSHistoryAdvances hst
> Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
> = 1
> And adt.AdvTypeCode = hst.AdvTypeCode
> Inner Join vw_ATLAS_CustomerInfo cst ON
> hst.CustomerID = cst.CustomerID
> Inner Join GUTSApprovalStatuses st1 On
> st1.ActiveFlag = 1
> And st1.ApprovalStatusCode =
> hst.ApprovalStatusCode
> Inner Join GUTSActivationStatuses st2 On
> st2.ActiveFlag = 1
> And st2.ActivationStatusCode =
> hst.ActivationStatusCode
> Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
> = 1
> And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
> Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
> And tmu.TermUnitName = hst.TermUnitName
> left outer JOin GUTSAdvanceRates adr On
> adr.ActiveFlag = 1
> And hst.AdvRecordID = adr.AdvRecordID
> And Not adr.AdvRate Is Null
> And adr.RateEndDate Is Null
> Where hst.ActiveFlag = 1
> And hst.AdvRecordID = @.AdvRecordID
> And hst.ToDate = (select max(ToDate) from
> GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID =
> hst.AdvRecordID)
> RETURN(0)
> END -- main procedure
>
>
> As you can see, @.AdvRecordID is the only parameter. From
> QA with the select statement pasted and @.AdvRecordID
> declared as int and used in the where clause this takes 4
> seconds. With the same AdvRecordID used in the SP (called
> in QA) it takes 6 minutes.
> Any thoughts?
> Thanks,
> Van
>
|||That could have been it. I eventually dropped and
recreated the SP and it runs fine now...strange. I didn't
get a change to do the sp_recomplile, but I'll keep that
in mind incase in comes up again.

>--Original Message--
>Although unlikely to cause such a difference, it might be
a problem of an
>out-of-date execution plan compiled into the sp in cache.
Can you try
>running sp_recompile
usp_ATLAS_HistoricalAdvanceData_Select then run it
>twice and check the second running time.
>Regards,
>Paul Ibison
>
>.
>
|||"Quentin Ran" <ab@.who.com> wrote in message
news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there. See
Quentin,
Why would moving the SET NOCOUNT out of the block change the runtime of
the query? Just curious because I also use BEGIN/END blocks in my stored
procedures with SET NOCOUNT within the blocks, and I've never had a problem
with them. I use them because I occasionally need to script out databases
into single text files and I find that the BEGIN/END blocks make it easier
for humans to navigate through the massive amount of text. I guess a
comment at the beginning and end would accomplish the same thing... But
habits die hard
|||> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there.
Why do you think that removing SET NOCOUNT ON will improve performance?
Also, while it is purely subjective, I am surprised that so many people
don't see the benefits of using a BEGIN / END block around the procedure
body. I find it immensely useful when looking at a properly-indented script
comprising multiple stored procedures...
A
|||Quentin,
that's true, but the distinction is that rerunning the sp will pull an old
compiled plan from cache, which is potentially inaccurate, while Van was
comparing this to a newly compiled plan from QA which was quicker.
Regards,
Paul Ibison
|||Paul,
I agree with the outdated exec plan part -- I missed it. What I was saying
was that a recompilation is effectively the same when you run the query in
the QA the first time -- the query will be compiled there.
Quentin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:en$K8a5NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Quentin,
> that's true, but the distinction is that rerunning the sp will pull an old
> compiled plan from cache, which is potentially inaccurate, while Van was
> comparing this to a newly compiled plan from QA which was quicker.
> Regards,
> Paul Ibison
>
|||Adam,
I was not saying "this will work". What I was saying was "see whether this
works" (I know my post looks bad together with the miss of the "outdated
exec plan"). The reason for my uncertainty is what I saw with not having
"set nocount on" -- it is not a simple performance degrader. It sometimes
takes WAY longer than what's needed to just return the "count". I had a
case where with "set nocount on" the proc runs for seconds, and without it
it runs for minutes. I don't know what it is, and privately communicated
with a recognized SQL expert and we were not able to get a plausible
conclusion. It was so frustrating for me that I want to share this even
though I do not know the mechanism of the problem. I certainly agree when
you need the count, you put it there (but make sure it does not make problem
for you). In the original post, it is not needed.
Quentin
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:#Ta1BY5NEHA.628@.TK2MSFTNGP11.phx.gbl...
> "Quentin Ran" <ab@.who.com> wrote in message
> news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> should
> Quentin,
> Why would moving the SET NOCOUNT out of the block change the runtime
of
> the query? Just curious because I also use BEGIN/END blocks in my stored
> procedures with SET NOCOUNT within the blocks, and I've never had a
problem
> with them. I use them because I occasionally need to script out databases
> into single text files and I find that the BEGIN/END blocks make it easier
> for humans to navigate through the massive amount of text. I guess a
> comment at the beginning and end would accomplish the same thing... But
> habits die hard
>
|||If your SP will cause such a problems on permanent basis then you should
think about adding WITH RECOMPILE option onto it
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b89a01c43795$04dbe070$a101280a@.phx.gbl...[vbcol=seagreen]
> That could have been it. I eventually dropped and
> recreated the SP and it runs fine now...strange. I didn't
> get a change to do the sp_recomplile, but I'll keep that
> in mind incase in comes up again.
> a problem of an
> Can you try
> usp_ATLAS_HistoricalAdvanceData_Select then run it

Problem with SP vs QA

I have a strange issue. When I execute an SP and pass it
the single parameter (an int) that it expects, it takes 6
minutes to run. It's just a select statement with the
parameter in the where clause. When I copy this to QA and
declare a variable as int, set it to the same parameter I
passed to the SP and use it in the where clause, it takes
4 seconds to run in QA. Not sure why this is happening.
SQL 2000, SP3a. I'm running these on the same server.
Here's the SP, just so you can see what it looks like:
CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
(
@.AdvRecordID int
)
AS
/ ****************************************
******************
********************
* Name: usp_ATLAS_HistoricalAdvanceData_Select
* Desc: A stored procedure to return the latest modified
Advance record from the history.
*
* Return values:
*
* Called by:
*
* Parameters:
* Input
* --
* @.AdvRecordID int
*
*
* Date: 11/7/2003
****************************************
*******************
********************
* Change History
****************************************
*******************
********************
* Date: Author: Description:
* -- -- --
--
****************************************
*******************
********************/
BEGIN -- main procedure
SET NOCOUNT ON
Select
hst.AdvRecordID,
hst.CustomerID,
adt.AdvTypeID,
hst.AdvTypeCode,
hst.AdvanceID,
hst.SpecialOfferID,
hst.PrePayOptionsCode,
hst.Term,
tmu.TermUnitID,
hst.TermUnitName as TermUnit,
hst.MatDate,
hst.TradeDate,
hst.SettleDate,
hst.TransDate,
hst.Amt,
st1.ApprovalStatusID,
st2.ActivationStatusID,
hst.TransContactFirstName,
hst.TransContactLastName,
dbo.ufn_ATLAS_GetSwapNumbers
(hst.AdvRecordID) as SwapNum,
dbo.ufn_ATLAS_GetARBNumber
(hst.AdvRecordID, 1) AS ARBNumber,
adr.AdvRate as CurrentRate,
hst.UsesLondonHoliday,
hst.AuthorizedContactFlag,
hst.ReissueFlag,
ppo.PrePayOptionsID,
ppo.PrePayOptionsDesc as PrepaymentOption,
cst.CustID AS BusinessCustomerID,
cst.CustomerName AS CustomerFullName,
cst.Address,
cst.City,
cst.State AS StateCode,
cst.Zip,
hst.ModByLogin
From GUTSHistoryAdvances hst
Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
= 1
And adt.AdvTypeCode = hst.AdvTypeCode
Inner Join vw_ATLAS_CustomerInfo cst ON
hst.CustomerID = cst.CustomerID
Inner Join GUTSApprovalStatuses st1 On
st1.ActiveFlag = 1
And st1.ApprovalStatusCode =
hst.ApprovalStatusCode
Inner Join GUTSActivationStatuses st2 On
st2.ActiveFlag = 1
And st2.ActivationStatusCode =
hst.ActivationStatusCode
Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
= 1
And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
And tmu.TermUnitName = hst.TermUnitName
left outer JOin GUTSAdvanceRates adr On
adr.ActiveFlag = 1
And hst.AdvRecordID = adr.AdvRecordID
And Not adr.AdvRate Is Null
And adr.RateEndDate Is Null
Where hst.ActiveFlag = 1
And hst.AdvRecordID = @.AdvRecordID
And hst.ToDate = (select max(ToDate) from
GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID =
hst.AdvRecordID)
RETURN(0)
END -- main procedure
As you can see, @.AdvRecordID is the only parameter. From
QA with the select statement pasted and @.AdvRecordID
declared as int and used in the where clause this takes 4
seconds. With the same AdvRecordID used in the SP (called
in QA) it takes 6 minutes.
Any thoughts?
Thanks,
VanAlthough unlikely to cause such a difference, it might be a problem of an
out-of-date execution plan compiled into the sp in cache. Can you try
running sp_recompile usp_ATLAS_HistoricalAdvanceData_Select then run it
twice and check the second running time.
Regards,
Paul Ibison|||Van,
take your set nocount on out of the begin ... end block. In fact you should
remove the begin/end altogether -- it has no reason to be there. See
whether this works. It should not be due to recompilation since that is
what you do in the QA when first time running the query.
Quentin
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b84d01c43790$d48a0590$a101280a@.phx.gbl...
> I have a strange issue. When I execute an SP and pass it
> the single parameter (an int) that it expects, it takes 6
> minutes to run. It's just a select statement with the
> parameter in the where clause. When I copy this to QA and
> declare a variable as int, set it to the same parameter I
> passed to the SP and use it in the where clause, it takes
> 4 seconds to run in QA. Not sure why this is happening.
> SQL 2000, SP3a. I'm running these on the same server.
> Here's the SP, just so you can see what it looks like:
> CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
> (
> @.AdvRecordID int
> )
> AS
> / ****************************************
******************
> ********************
> * Name: usp_ATLAS_HistoricalAdvanceData_Select
> * Desc: A stored procedure to return the latest modified
> Advance record from the history.
> *
> * Return values:
> *
> * Called by:
> *
> * Parameters:
> * Input
> * --
> * @.AdvRecordID int
> *
> *
> * Date: 11/7/2003
> ****************************************
*******************
> ********************
> * Change History
> ****************************************
*******************
> ********************
> * Date: Author: Description:
> * -- -- --
> --
> ****************************************
*******************
> ********************/
> BEGIN -- main procedure
> SET NOCOUNT ON
> Select
> hst.AdvRecordID,
> hst.CustomerID,
> adt.AdvTypeID,
> hst.AdvTypeCode,
> hst.AdvanceID,
> hst.SpecialOfferID,
> hst.PrePayOptionsCode,
> hst.Term,
> tmu.TermUnitID,
> hst.TermUnitName as TermUnit,
> hst.MatDate,
> hst.TradeDate,
> hst.SettleDate,
> hst.TransDate,
> hst.Amt,
> st1.ApprovalStatusID,
> st2.ActivationStatusID,
> hst.TransContactFirstName,
> hst.TransContactLastName,
> dbo.ufn_ATLAS_GetSwapNumbers
> (hst.AdvRecordID) as SwapNum,
> dbo.ufn_ATLAS_GetARBNumber
> (hst.AdvRecordID, 1) AS ARBNumber,
> adr.AdvRate as CurrentRate,
> hst.UsesLondonHoliday,
> hst.AuthorizedContactFlag,
> hst.ReissueFlag,
> ppo.PrePayOptionsID,
> ppo.PrePayOptionsDesc as PrepaymentOption,
> cst.CustID AS BusinessCustomerID,
> cst.CustomerName AS CustomerFullName,
> cst.Address,
> cst.City,
> cst.State AS StateCode,
> cst.Zip,
> hst.ModByLogin
>
> From GUTSHistoryAdvances hst
> Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
> = 1
> And adt.AdvTypeCode = hst.AdvTypeCode
> Inner Join vw_ATLAS_CustomerInfo cst ON
> hst.CustomerID = cst.CustomerID
> Inner Join GUTSApprovalStatuses st1 On
> st1.ActiveFlag = 1
> And st1.ApprovalStatusCode =
> hst.ApprovalStatusCode
> Inner Join GUTSActivationStatuses st2 On
> st2.ActiveFlag = 1
> And st2.ActivationStatusCode =
> hst.ActivationStatusCode
> Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
> = 1
> And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
> Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
> And tmu.TermUnitName = hst.TermUnitName
> left outer JOin GUTSAdvanceRates adr On
> adr.ActiveFlag = 1
> And hst.AdvRecordID = adr.AdvRecordID
> And Not adr.AdvRate Is Null
> And adr.RateEndDate Is Null
> Where hst.ActiveFlag = 1
> And hst.AdvRecordID = @.AdvRecordID
> And hst.ToDate = (select max(ToDate) from
> GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID =
> hst.AdvRecordID)
> RETURN(0)
> END -- main procedure
>
>
> As you can see, @.AdvRecordID is the only parameter. From
> QA with the select statement pasted and @.AdvRecordID
> declared as int and used in the where clause this takes 4
> seconds. With the same AdvRecordID used in the SP (called
> in QA) it takes 6 minutes.
> Any thoughts?
> Thanks,
> Van
>|||That could have been it. I eventually dropped and
recreated the SP and it runs fine now...strange. I didn't
get a change to do the sp_recomplile, but I'll keep that
in mind incase in comes up again.

>--Original Message--
>Although unlikely to cause such a difference, it might be
a problem of an
>out-of-date execution plan compiled into the sp in cache.
Can you try
>running sp_recompile
usp_ATLAS_HistoricalAdvanceData_Select then run it
>twice and check the second running time.
>Regards,
>Paul Ibison
>
>.
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there. See
Quentin,
Why would moving the SET NOCOUNT out of the block change the runtime of
the query? Just curious because I also use BEGIN/END blocks in my stored
procedures with SET NOCOUNT within the blocks, and I've never had a problem
with them. I use them because I occasionally need to script out databases
into single text files and I find that the BEGIN/END blocks make it easier
for humans to navigate through the massive amount of text. I guess a
comment at the beginning and end would accomplish the same thing... But
habits die hard |||> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there.
Why do you think that removing SET NOCOUNT ON will improve performance?
Also, while it is purely subjective, I am surprised that so many people
don't see the benefits of using a BEGIN / END block around the procedure
body. I find it immensely useful when looking at a properly-indented script
comprising multiple stored procedures...
A|||Quentin,
that's true, but the distinction is that rerunning the sp will pull an old
compiled plan from cache, which is potentially inaccurate, while Van was
comparing this to a newly compiled plan from QA which was quicker.
Regards,
Paul Ibison|||Paul,
I agree with the outdated exec plan part -- I missed it. What I was saying
was that a recompilation is effectively the same when you run the query in
the QA the first time -- the query will be compiled there.
Quentin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:en$K8a5NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Quentin,
> that's true, but the distinction is that rerunning the sp will pull an old
> compiled plan from cache, which is potentially inaccurate, while Van was
> comparing this to a newly compiled plan from QA which was quicker.
> Regards,
> Paul Ibison
>|||Adam,
I was not saying "this will work". What I was saying was "see whether this
works" (I know my post looks bad together with the miss of the "outdated
exec plan"). The reason for my uncertainty is what I saw with not having
"set nocount on" -- it is not a simple performance degrader. It sometimes
takes WAY longer than what's needed to just return the "count". I had a
case where with "set nocount on" the proc runs for seconds, and without it
it runs for minutes. I don't know what it is, and privately communicated
with a recognized SQL expert and we were not able to get a plausible
conclusion. It was so frustrating for me that I want to share this even
though I do not know the mechanism of the problem. I certainly agree when
you need the count, you put it there (but make sure it does not make problem
for you). In the original post, it is not needed.
Quentin
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:#Ta1BY5NEHA.628@.TK2MSFTNGP11.phx.gbl...
> "Quentin Ran" <ab@.who.com> wrote in message
> news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> should
> Quentin,
> Why would moving the SET NOCOUNT out of the block change the runtime
of
> the query? Just curious because I also use BEGIN/END blocks in my stored
> procedures with SET NOCOUNT within the blocks, and I've never had a
problem
> with them. I use them because I occasionally need to script out databases
> into single text files and I find that the BEGIN/END blocks make it easier
> for humans to navigate through the massive amount of text. I guess a
> comment at the beginning and end would accomplish the same thing... But
> habits die hard
>|||If your SP will cause such a problems on permanent basis then you should
think about adding WITH RECOMPILE option onto it
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b89a01c43795$04dbe070$a101280a@.phx.gbl...[vbcol=seagreen]
> That could have been it. I eventually dropped and
> recreated the SP and it runs fine now...strange. I didn't
> get a change to do the sp_recomplile, but I'll keep that
> in mind incase in comes up again.
>
> a problem of an
> Can you try
> usp_ATLAS_HistoricalAdvanceData_Select then run it

Problem with SP vs QA

I have a strange issue. When I execute an SP and pass it
the single parameter (an int) that it expects, it takes 6
minutes to run. It's just a select statement with the
parameter in the where clause. When I copy this to QA and
declare a variable as int, set it to the same parameter I
passed to the SP and use it in the where clause, it takes
4 seconds to run in QA. Not sure why this is happening.
SQL 2000, SP3a. I'm running these on the same server.
Here's the SP, just so you can see what it looks like:
CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
(
@.AdvRecordID int
)
AS
/**********************************************************
********************
* Name: usp_ATLAS_HistoricalAdvanceData_Select
* Desc: A stored procedure to return the latest modified
Advance record from the history.
*
* Return values:
*
* Called by:
*
* Parameters:
* Input
* --
* @.AdvRecordID int
*
*
* Date: 11/7/2003
***********************************************************
********************
* Change History
***********************************************************
********************
* Date: Author: Description:
* -- -- --
--
***********************************************************
********************/
BEGIN -- main procedure
SET NOCOUNT ON
Select
hst.AdvRecordID,
hst.CustomerID,
adt.AdvTypeID,
hst.AdvTypeCode,
hst.AdvanceID,
hst.SpecialOfferID,
hst.PrePayOptionsCode,
hst.Term,
tmu.TermUnitID,
hst.TermUnitName as TermUnit,
hst.MatDate,
hst.TradeDate,
hst.SettleDate,
hst.TransDate,
hst.Amt,
st1.ApprovalStatusID,
st2.ActivationStatusID,
hst.TransContactFirstName,
hst.TransContactLastName,
dbo.ufn_ATLAS_GetSwapNumbers
(hst.AdvRecordID) as SwapNum,
dbo.ufn_ATLAS_GetARBNumber
(hst.AdvRecordID, 1) AS ARBNumber,
adr.AdvRate as CurrentRate,
hst.UsesLondonHoliday,
hst.AuthorizedContactFlag,
hst.ReissueFlag,
ppo.PrePayOptionsID,
ppo.PrePayOptionsDesc as PrepaymentOption,
cst.CustID AS BusinessCustomerID,
cst.CustomerName AS CustomerFullName,
cst.Address,
cst.City,
cst.State AS StateCode,
cst.Zip,
hst.ModByLogin
From GUTSHistoryAdvances hst
Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
= 1
And adt.AdvTypeCode = hst.AdvTypeCode
Inner Join vw_ATLAS_CustomerInfo cst ON
hst.CustomerID = cst.CustomerID
Inner Join GUTSApprovalStatuses st1 On
st1.ActiveFlag = 1
And st1.ApprovalStatusCode = hst.ApprovalStatusCode
Inner Join GUTSActivationStatuses st2 On
st2.ActiveFlag = 1
And st2.ActivationStatusCode = hst.ActivationStatusCode
Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
= 1
And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
And tmu.TermUnitName = hst.TermUnitName
left outer JOin GUTSAdvanceRates adr On
adr.ActiveFlag = 1
And hst.AdvRecordID = adr.AdvRecordID
And Not adr.AdvRate Is Null
And adr.RateEndDate Is Null
Where hst.ActiveFlag = 1
And hst.AdvRecordID = @.AdvRecordID
And hst.ToDate = (select max(ToDate) from
GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID = hst.AdvRecordID)
RETURN(0)
END -- main procedure
As you can see, @.AdvRecordID is the only parameter. From
QA with the select statement pasted and @.AdvRecordID
declared as int and used in the where clause this takes 4
seconds. With the same AdvRecordID used in the SP (called
in QA) it takes 6 minutes.
Any thoughts?
Thanks,
VanAlthough unlikely to cause such a difference, it might be a problem of an
out-of-date execution plan compiled into the sp in cache. Can you try
running sp_recompile usp_ATLAS_HistoricalAdvanceData_Select then run it
twice and check the second running time.
Regards,
Paul Ibison|||Van,
take your set nocount on out of the begin ... end block. In fact you should
remove the begin/end altogether -- it has no reason to be there. See
whether this works. It should not be due to recompilation since that is
what you do in the QA when first time running the query.
Quentin
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b84d01c43790$d48a0590$a101280a@.phx.gbl...
> I have a strange issue. When I execute an SP and pass it
> the single parameter (an int) that it expects, it takes 6
> minutes to run. It's just a select statement with the
> parameter in the where clause. When I copy this to QA and
> declare a variable as int, set it to the same parameter I
> passed to the SP and use it in the where clause, it takes
> 4 seconds to run in QA. Not sure why this is happening.
> SQL 2000, SP3a. I'm running these on the same server.
> Here's the SP, just so you can see what it looks like:
> CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
> (
> @.AdvRecordID int
> )
> AS
> /**********************************************************
> ********************
> * Name: usp_ATLAS_HistoricalAdvanceData_Select
> * Desc: A stored procedure to return the latest modified
> Advance record from the history.
> *
> * Return values:
> *
> * Called by:
> *
> * Parameters:
> * Input
> * --
> * @.AdvRecordID int
> *
> *
> * Date: 11/7/2003
> ***********************************************************
> ********************
> * Change History
> ***********************************************************
> ********************
> * Date: Author: Description:
> * -- -- --
> --
> ***********************************************************
> ********************/
> BEGIN -- main procedure
> SET NOCOUNT ON
> Select
> hst.AdvRecordID,
> hst.CustomerID,
> adt.AdvTypeID,
> hst.AdvTypeCode,
> hst.AdvanceID,
> hst.SpecialOfferID,
> hst.PrePayOptionsCode,
> hst.Term,
> tmu.TermUnitID,
> hst.TermUnitName as TermUnit,
> hst.MatDate,
> hst.TradeDate,
> hst.SettleDate,
> hst.TransDate,
> hst.Amt,
> st1.ApprovalStatusID,
> st2.ActivationStatusID,
> hst.TransContactFirstName,
> hst.TransContactLastName,
> dbo.ufn_ATLAS_GetSwapNumbers
> (hst.AdvRecordID) as SwapNum,
> dbo.ufn_ATLAS_GetARBNumber
> (hst.AdvRecordID, 1) AS ARBNumber,
> adr.AdvRate as CurrentRate,
> hst.UsesLondonHoliday,
> hst.AuthorizedContactFlag,
> hst.ReissueFlag,
> ppo.PrePayOptionsID,
> ppo.PrePayOptionsDesc as PrepaymentOption,
> cst.CustID AS BusinessCustomerID,
> cst.CustomerName AS CustomerFullName,
> cst.Address,
> cst.City,
> cst.State AS StateCode,
> cst.Zip,
> hst.ModByLogin
>
> From GUTSHistoryAdvances hst
> Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
> = 1
> And adt.AdvTypeCode = hst.AdvTypeCode
> Inner Join vw_ATLAS_CustomerInfo cst ON
> hst.CustomerID = cst.CustomerID
> Inner Join GUTSApprovalStatuses st1 On
> st1.ActiveFlag = 1
> And st1.ApprovalStatusCode => hst.ApprovalStatusCode
> Inner Join GUTSActivationStatuses st2 On
> st2.ActiveFlag = 1
> And st2.ActivationStatusCode => hst.ActivationStatusCode
> Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
> = 1
> And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
> Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
> And tmu.TermUnitName = hst.TermUnitName
> left outer JOin GUTSAdvanceRates adr On
> adr.ActiveFlag = 1
> And hst.AdvRecordID = adr.AdvRecordID
> And Not adr.AdvRate Is Null
> And adr.RateEndDate Is Null
> Where hst.ActiveFlag = 1
> And hst.AdvRecordID = @.AdvRecordID
> And hst.ToDate = (select max(ToDate) from
> GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID => hst.AdvRecordID)
> RETURN(0)
> END -- main procedure
>
>
> As you can see, @.AdvRecordID is the only parameter. From
> QA with the select statement pasted and @.AdvRecordID
> declared as int and used in the where clause this takes 4
> seconds. With the same AdvRecordID used in the SP (called
> in QA) it takes 6 minutes.
> Any thoughts?
> Thanks,
> Van
>|||That could have been it. I eventually dropped and
recreated the SP and it runs fine now...strange. I didn't
get a change to do the sp_recomplile, but I'll keep that
in mind incase in comes up again.
>--Original Message--
>Although unlikely to cause such a difference, it might be
a problem of an
>out-of-date execution plan compiled into the sp in cache.
Can you try
>running sp_recompile
usp_ATLAS_HistoricalAdvanceData_Select then run it
>twice and check the second running time.
>Regards,
>Paul Ibison
>
>.
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there. See
Quentin,
Why would moving the SET NOCOUNT out of the block change the runtime of
the query? Just curious because I also use BEGIN/END blocks in my stored
procedures with SET NOCOUNT within the blocks, and I've never had a problem
with them. I use them because I occasionally need to script out databases
into single text files and I find that the BEGIN/END blocks make it easier
for humans to navigate through the massive amount of text. I guess a
comment at the beginning and end would accomplish the same thing... But
habits die hard :)|||> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there.
Why do you think that removing SET NOCOUNT ON will improve performance?
Also, while it is purely subjective, I am surprised that so many people
don't see the benefits of using a BEGIN / END block around the procedure
body. I find it immensely useful when looking at a properly-indented script
comprising multiple stored procedures...
A|||Quentin,
that's true, but the distinction is that rerunning the sp will pull an old
compiled plan from cache, which is potentially inaccurate, while Van was
comparing this to a newly compiled plan from QA which was quicker.
Regards,
Paul Ibison|||Paul,
I agree with the outdated exec plan part -- I missed it. What I was saying
was that a recompilation is effectively the same when you run the query in
the QA the first time -- the query will be compiled there.
Quentin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:en$K8a5NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Quentin,
> that's true, but the distinction is that rerunning the sp will pull an old
> compiled plan from cache, which is potentially inaccurate, while Van was
> comparing this to a newly compiled plan from QA which was quicker.
> Regards,
> Paul Ibison
>|||Adam,
I was not saying "this will work". What I was saying was "see whether this
works" (I know my post looks bad together with the miss of the "outdated
exec plan"). The reason for my uncertainty is what I saw with not having
"set nocount on" -- it is not a simple performance degrader. It sometimes
takes WAY longer than what's needed to just return the "count". I had a
case where with "set nocount on" the proc runs for seconds, and without it
it runs for minutes. I don't know what it is, and privately communicated
with a recognized SQL expert and we were not able to get a plausible
conclusion. It was so frustrating for me that I want to share this even
though I do not know the mechanism of the problem. I certainly agree when
you need the count, you put it there (but make sure it does not make problem
for you). In the original post, it is not needed.
Quentin
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:#Ta1BY5NEHA.628@.TK2MSFTNGP11.phx.gbl...
> "Quentin Ran" <ab@.who.com> wrote in message
> news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > take your set nocount on out of the begin ... end block. In fact you
> should
> > remove the begin/end altogether -- it has no reason to be there. See
> Quentin,
> Why would moving the SET NOCOUNT out of the block change the runtime
of
> the query? Just curious because I also use BEGIN/END blocks in my stored
> procedures with SET NOCOUNT within the blocks, and I've never had a
problem
> with them. I use them because I occasionally need to script out databases
> into single text files and I find that the BEGIN/END blocks make it easier
> for humans to navigate through the massive amount of text. I guess a
> comment at the beginning and end would accomplish the same thing... But
> habits die hard :)
>|||If your SP will cause such a problems on permanent basis then you should
think about adding WITH RECOMPILE option onto it
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b89a01c43795$04dbe070$a101280a@.phx.gbl...
> That could have been it. I eventually dropped and
> recreated the SP and it runs fine now...strange. I didn't
> get a change to do the sp_recomplile, but I'll keep that
> in mind incase in comes up again.
> >--Original Message--
> >Although unlikely to cause such a difference, it might be
> a problem of an
> >out-of-date execution plan compiled into the sp in cache.
> Can you try
> >running sp_recompile
> usp_ATLAS_HistoricalAdvanceData_Select then run it
> >twice and check the second running time.
> >Regards,
> >Paul Ibison

Problem with single quotes

Hi,
I've a column (type: varchar) in a table. I need to add single quotes in the
value while inserting the rows. But I get an error. Please see below:
create table #t1 (address varchar(20))
go
insert into #t1 values ('St John's street')
Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')
Since the database is on hosting providers server, I cannot change the
settings of the server. Please help me by providing any suitable solution.
Also, note that I've an ASP script which insert rows into the table.
Thanks in advance.
-VenkatHi Venkat,
To solve the problem you need to generate insert statement like following in
your ASP script:
insert into #t1 values ('St John''s street')
i.e. replace single quote ( ' ) by two single quotes ( '' )
Krish
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq#aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
the
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||Hi
A single quote within a string can be escapped with a second quote.
insert into #t1 (address) values ('St John''s street')
John
"G.V.Reddy" wrote:

> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in t
he
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>
>|||Thank you very much Krish and John.
Since the address is entered by the visitors on the web site, do we need to
check each and every value entered/inserted into the varchar field for the
single quotes? In case the answer is Yes, I think we can do it by writing a
function which replaces a single quote with adding another quote. Is there
any other simple method to integrate this functionality (escaping with
another single quote) into the ASP code?
Thanks in advance.
-Venkat
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
> the value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||G.V.Reddy wrote:
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we
> need to check each and every value entered/inserted into the varchar
> field for the single quotes? In case the answer is Yes, I think we
> can do it by writing a function which replaces a single quote with
> adding another quote. Is there any other simple method to integrate
> this functionality (escaping with another single quote) into the ASP
> code?
> Thanks in advance.
> -Venkat
>
Look into the use of "parameters". I don't know the exact details for *asp*,
but they are something like:
* provide placeholders in you sql string :
insert into #t1 values (?)
* create a parameter, fill it with the "plain" value ("St John's street"), n
o need
to escape quotes, then add that parameter to the command object
* execute the query
Hans Kesting|||Hi
For ASP/ADO check out the SQL Server samples
http://msdn.microsoft.com/library/d...
5ym.asp
John|||replace(strParam, "'","''")|||Yes, a function to "double up" the single quote items will help. It would be
even better to use parameterized command objects instead of concatenated SQL
strings for communicating with the database (I am guessing you are using ADO
in your ASP application). Building concatenated SQL strings leaves you
application open to SQL Injection attacks, which is a severe security issue.
For information on ADO Command objects and parameters, see:
http://msdn.microsoft.com/library/d...rsreference.asp
For information about SQL injection attacks, see:
http://search.microsoft.com/search/.../>
0&s=1&swc=0
http://www.google.com/search?hl=en&q=sql+injection
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:OxZF4q$aFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we need
to
> check each and every value entered/inserted into the varchar field for the
> single quotes? In case the answer is Yes, I think we can do it by writing
a
> function which replaces a single quote with adding another quote. Is there
> any other simple method to integrate this functionality (escaping with
> another single quote) into the ASP code?
> Thanks in advance.
> -Venkat
>
> "G.V.Reddy" <vreddyg@.go.com> wrote in message
> news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
below:
solution.
>
>sql

Problem with ROLAP dimension updates

Simple scenario:
Cube based on a single table. (FactContacts)
One HOLAP count of rows measure.
One ROLAP fact dimension with an attribute based on a nullable string column. (Status)

Everything works fine as long as I process the cube manually through VS. However, I often get the following error if I rely on the cube to update automatically as my source table changes. (Interestingly the error doesn't always occur.)

The query could not be processed:
o Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_FactContacts, Column: Status, Value:aaaa.
o Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

My first guess was that things are getting updated in the wrong sequence (measure before dimension), so I've tried changing the ProcessingPriority properties on the dimension and on the measure group but that didn't have any effect.

Any ideas?
I should probably also clarify I have proactive caching enabled, both on the dimension and on the measure group. My notification method is SQL Server.

I'd really like to know if this could possibly be a bug of SSAS, or if I'm just missing something. This problem is pretty much a showstopper for my project, as real-time data is a requirement.

Problem With Result Set in SQL Task

Hello,

I have a SQL Task configured to return a single row, and a single column value. The SQL Statement looks like this;

SELECT MAX(InvoiceDate) AS InvoiceDate
FROM dbo.DailySettlementData

The statement parses without a problem. In the 'Result Set' of the SQL Task I have the following;

Result Name; InvoiceDate, Variable Name; LatestTableDate

'LatestTableDate' is of type 'DateTime' (and I'm wondering now if this needs to be of type 'Object')

I need this MAX(InvoiceDate) value in a later step that checks this value against another date type variable.

I'm not getting the result I expect. Do I have the variable for the result set in the SQL Task set up correctly?

Thank you for your help!

cdun2

I assume that the variable name is "User::LatestTableDate", right?

Otherwise that looks fine. It doesn't need to be an object datatype. You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.|||

Phil Brammer wrote:

You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.

One of my variables needed to have 'EvaluateAsExpression' set to True. Now it works fine.

A couple of questions related to working with SSIS in Visual Studio;

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Thank you again for your help.

cdun2

|||

cdun2 wrote:

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Close them before saving the project and closing it. It will open whatever you left open the previous time.|||

cdun2 wrote:

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250573&SiteID=1

Problem with Report Services Integration in MOSS 2007

Specifics:
First machine: SharePoint single server Farm
Second machine: SQL 2005 with Report Services
Installed SP2 and RS Add-in, added SQL pc to farm, and followed steps to set
up integration, including changing SQL service accounts to domain accounts.
Everything went smoothly until we clicked on â'Set Server Defaultsâ' under
Reporting Services in SharePoint Central Admin and we got an error message:
An unexpected error occurred while connecting to the report server. Verify
that the report server is available and configured for SharePoint integrated
mode. --> Server was unable to process request. --> The request failed with
HTTP status 401: Unauthorized.
Nothing in the Event Log on the MOSS server but on the SQL server there is
an Event ID 529 â' unknown user name or bad password. This error message also
shows that the Logon Process and the Authentication Package are Kerberos.
We have beaten our heads against the wall trying to solve. Any help would be
appreciated.Did you check your Service Accounts in IIS and the Reporting Services setup
in SQL server. We had the same issue and it turned out to be we had missed
some of the accounts in Central Admin and IIS.
"AlisonZ" wrote:
> Specifics:
> First machine: SharePoint single server Farm
> Second machine: SQL 2005 with Report Services
> Installed SP2 and RS Add-in, added SQL pc to farm, and followed steps to set
> up integration, including changing SQL service accounts to domain accounts.
> Everything went smoothly until we clicked on â'Set Server Defaultsâ' under
> Reporting Services in SharePoint Central Admin and we got an error message:
> An unexpected error occurred while connecting to the report server. Verify
> that the report server is available and configured for SharePoint integrated
> mode. --> Server was unable to process request. --> The request failed with
> HTTP status 401: Unauthorized.
> Nothing in the Event Log on the MOSS server but on the SQL server there is
> an Event ID 529 â' unknown user name or bad password. This error message also
> shows that the Logon Process and the Authentication Package are Kerberos.
> We have beaten our heads against the wall trying to solve. Any help would be
> appreciated.
>