Problem with SQL SQLCECommand - Missing Parameter

Have a nice day. I'm developing for PocketPC using VB2005 and SQLMobile and need some help to solve the following issue:

At runtime the following code throws an exception #25950 SSCE_M_QP_MISSINGPARAMETER

Missing Parameter [Parameter ordinal = 1]

Everything looks fine, but it still fails. In the same app I have a datagrid connected via a dataset and it works OK. Please give me directions!!

Dim conn As SqlCeConnection = Nothing

Try

conn = New SqlCeConnection("Data Source =" & _

(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\db.sdf;"))

conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand

cmd.CommandText = " INSERT t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.ToString)

Finally

conn.Close()

End Try

Shouldn't it be "INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "|||I first tried with it, but still raises the error. I've tried with all the possible syntax of the INSERT clause with the same result. Not yet resolved.|||

Hi ELugoL,

The Sql statement is supported to be : INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT').

Could you please debug step by step to see which line raise this exception? It seems like it may be other issue other than sql statement.

Thanks,

Zero Dai - MSFT

|||

Hi Zero Dai, have a nice day.

Running the code on debug mode throws the exception in the instruction

cmd.ExecuteNonQuery()

and the exception details are:

HResult: -2147217904

Message: "Parameter missing. [ Parameter ordinal = 1 ]"

NativeError:25950

Source: "SQL Server 2005 Mobile Edition ADO.NET Data Provider"

StackTrace:

en System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
en SvitaMovil2.frmDetalleCliente.cmdGuardar_Click()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en SvitaMovil2.frmSplash.Main()

I will appreciate any help you can provide to me. Thanks in advance.

Edgar Lugo L.

|||

Hi Edgar,

Sorry for delaying reply.

After trying several times, I still cannot reproduce your issue. The code and SQL statement SHOULD be right with no exception thrown when executing. So, could you please check if the field in your sql is correct? Or is it the right table name?

And I move to Sql Server 2005 Compact forum, where increases the chances for getting your question answered.

Thanks,

Zero Dai - MSFT

|||delete the "[" "]" symbols from the query and thats all

Problem with SQL SQLCECommand - Missing Parameter

Have a nice day. I'm developing for PocketPC using VB2005 and SQLMobile and need some help to solve the following issue:

At runtime the following code throws an exception #25950 SSCE_M_QP_MISSINGPARAMETER

Missing Parameter [Parameter ordinal = 1]

Everything looks fine, but it still fails. In the same app I have a datagrid connected via a dataset and it works OK. Please give me directions!!

Dim conn As SqlCeConnection = Nothing

Try

conn = New SqlCeConnection("Data Source =" & _

(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\db.sdf;"))

conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand

cmd.CommandText = " INSERT t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.ToString)

Finally

conn.Close()

End Try

Shouldn't it be "INSERTINTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "|||I first tried with it, but still raises the error. I've tried with all the possible syntax of the INSERT clause with the same result. Not yet resolved.|||

Hi ELugoL,

The Sql statement is supported to be : INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT').

Could you please debug step by step to see which line raise this exception? It seems like it may be other issue other than sql statement.

Thanks,

Zero Dai - MSFT

|||

Hi Zero Dai, have a nice day.

Running the code on debug mode throws the exception in the instruction

cmd.ExecuteNonQuery()

and the exception details are:

HResult: -2147217904

Message: "Parameter missing. [ Parameter ordinal = 1 ]"

NativeError:25950

Source: "SQL Server 2005 Mobile Edition ADO.NET Data Provider"

StackTrace:

en System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
en SvitaMovil2.frmDetalleCliente.cmdGuardar_Click()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en SvitaMovil2.frmSplash.Main()

I will appreciate any help you can provide to me. Thanks in advance.

Edgar Lugo L.

|||

Hi Edgar,

Sorry for delaying reply.

After trying several times, I still cannot reproduce your issue. The code and SQL statement SHOULD be right with no exception thrown when executing. So, could you please check if the field in your sql is correct? Or is it the right table name?

And I move to Sql Server 2005 Compact forum, where increases the chances for getting your question answered.

Thanks,

Zero Dai - MSFT

Problem with sp_executesql

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @.au_lname
',
N'@.au_lname nVarChar',
@.au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

ThanksChange your code as follows:

N'@.au_lname nVarChar', -->>> N'@.au_lname nVarChar(5)',|||Thank you very much for feedback!

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 Select Top ....(incorrect syntax near '@p')

hello,
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:

> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>

Problem with Scope parameter for SUM function

Is it possible to use a string expression for the scope parameter of the SUM
function?
I am using conditional grouping in my report and do not know the name of the
group I need to SUM for until the report is run.
I need to use an expression like this: = SUM(Fields!Field1.Value,
Code.GetMaxGroupNameUsed()), where I am determining the name of the
containing group in custom code.
Unfortunately I get this error:
The value expression for the textbox 'textbox18' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
Any ideas?
Thanks,
TessaScope names have to be string constants.
I'm not sure why you need dynamic scopes, but you can simulate them.
Assuming you have a function that returns a number between 1 and N, use an
expression similar to this:
=Choose( Code.DetermineScope(), Sum(Fields!F1.Value), Sum(Fields!F1.Value,
"Group1"), Sum(Fields!F1.Value, "Group2"), ......, Sum(Fields!F1.Value,
"DataRegionName"), Sum(Fields!F1.Value, "DataSetName") )
Notes:
* you can only use scope names of containing groups, and data regions (e.g.
table, list, matrix)
* for huge reports (i.e. with many data rows in the dataset) this approach
of simulating dynamic scopes will degrade performance
* documentation on the Choose function is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tessa" <nospam@.thanks> wrote in message
news:eXcayiYWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Is it possible to use a string expression for the scope parameter of the
SUM
> function?
> I am using conditional grouping in my report and do not know the name of
the
> group I need to SUM for until the report is run.
> I need to use an expression like this: = SUM(Fields!Field1.Value,
> Code.GetMaxGroupNameUsed()), where I am determining the name of the
> containing group in custom code.
> Unfortunately I get this error:
> The value expression for the textbox 'textbox18' has a scope parameter
that
> is not valid for an aggregate function. The scope parameter must be set to
a
> string constant that is equal to either the name of a containing group,
the
> name of a containing data region, or the name of a data set.
> Any ideas?
> Thanks,
> Tessa
>

Problem with Reporting Service SQL 2000 and Sybase

Hi,
The connection to Sybase with OLEDB and ODBC have the same problem when the
query have parameter. The query below don´t execute:
select prod_cod,cod_empresa from estatistica.dbo.zomba
where vida = @.vida
OLEDB error: "An error occurred while executing the query. The given type
name was unrecognized"
versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol driver][SQL
Server] must declare variable @.vida"
versions used: 04.10.0049 (provided by Sybase support)
Someone have the same problem?
Thanks,
LandryI work extensively with Sybase. The issue you are seeing is that query
variables for ODBC have to be a ? (unnamed parameter). I suggest you stick
with the ODBC driver. I have had issues with OLEDB.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landry" <landry@.dsai.com.br.NEWS> wrote in message
news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> The connection to Sybase with OLEDB and ODBC have the same problem when
> the
> query have parameter. The query below don´t execute:
> select prod_cod,cod_empresa from estatistica.dbo.zomba
> where vida = @.vida
> OLEDB error: "An error occurred while executing the query. The given type
> name was unrecognized"
> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
> driver][SQL
> Server] must declare variable @.vida"
> versions used: 04.10.0049 (provided by Sybase support)
> Someone have the same problem?
> Thanks,
> Landry
>
>|||Hi,
Thanks.
I test ? with OLEDB and ODBC and have the same error!
select prod_cod,cod_empresa from estatistica.dbo.zomba
where vida = ?
I test Stored Procedures with parameters and have the same error.
Landry
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
news:u%23sw%23m7RFHA.3704@.TK2MSFTNGP12.phx.gbl...
>I work extensively with Sybase. The issue you are seeing is that query
>variables for ODBC have to be a ? (unnamed parameter). I suggest you stick
>with the ODBC driver. I have had issues with OLEDB.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
> news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> The connection to Sybase with OLEDB and ODBC have the same problem when
>> the
>> query have parameter. The query below don´t execute:
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = @.vida
>> OLEDB error: "An error occurred while executing the query. The given
>> type
>> name was unrecognized"
>> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
>> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
>> driver][SQL
>> Server] must declare variable @.vida"
>> versions used: 04.10.0049 (provided by Sybase support)
>> Someone have the same problem?
>> Thanks,
>> Landry
>>
>>
>|||The best thing to do is as you are doing, first get a query and then move on
to stored procedures. That is the data type of vida?
Also, when are you getting this error? From the data tab clicking on the ! ?
Or from the preview?
Let's concentrate on ODBC. What error do you get with ODBC (it can't be the
same as before because at that point you had this error: must declare
variable @.vida).
I do all my queries from the generic query window. Try that (the button is
to the right of the ... to switch to generic query designer).
Also, what version of Sysbase. I am using 12.5.2 client and have used that
against both an 11.x (I don't remember the exact version) and 12.5.1
servers.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landry" <landry@.dsai.com.br.NEWS> wrote in message
news:e4V1j7BSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Thanks.
> I test ? with OLEDB and ODBC and have the same error!
> select prod_cod,cod_empresa from estatistica.dbo.zomba
> where vida = ?
> I test Stored Procedures with parameters and have the same error.
> Landry
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
> news:u%23sw%23m7RFHA.3704@.TK2MSFTNGP12.phx.gbl...
>>I work extensively with Sybase. The issue you are seeing is that query
>>variables for ODBC have to be a ? (unnamed parameter). I suggest you stick
>>with the ODBC driver. I have had issues with OLEDB.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
>> news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> The connection to Sybase with OLEDB and ODBC have the same problem when
>> the
>> query have parameter. The query below don´t execute:
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = @.vida
>> OLEDB error: "An error occurred while executing the query. The given
>> type
>> name was unrecognized"
>> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
>> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
>> driver][SQL
>> Server] must declare variable @.vida"
>> versions used: 04.10.0049 (provided by Sybase support)
>> Someone have the same problem?
>> Thanks,
>> Landry
>>
>>
>>
>|||Hi Bruce,
Thanks, the problem is ODBC version, now work fine with cliente version
12.5.3, the most recent.
The Sybase suport will analyze the OLEDB to correct the error.
Thanks,
Landry
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
news:urhgPMCSFHA.3336@.TK2MSFTNGP09.phx.gbl...
> The best thing to do is as you are doing, first get a query and then move
> on to stored procedures. That is the data type of vida?
> Also, when are you getting this error? From the data tab clicking on the !
> ? Or from the preview?
> Let's concentrate on ODBC. What error do you get with ODBC (it can't be
> the same as before because at that point you had this error: must declare
> variable @.vida).
> I do all my queries from the generic query window. Try that (the button is
> to the right of the ... to switch to generic query designer).
> Also, what version of Sysbase. I am using 12.5.2 client and have used that
> against both an 11.x (I don't remember the exact version) and 12.5.1
> servers.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
> news:e4V1j7BSFHA.1348@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Thanks.
>> I test ? with OLEDB and ODBC and have the same error!
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = ?
>> I test Stored Procedures with parameters and have the same error.
>> Landry
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
>> news:u%23sw%23m7RFHA.3704@.TK2MSFTNGP12.phx.gbl...
>>I work extensively with Sybase. The issue you are seeing is that query
>>variables for ODBC have to be a ? (unnamed parameter). I suggest you
>>stick with the ODBC driver. I have had issues with OLEDB.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
>> news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> The connection to Sybase with OLEDB and ODBC have the same problem when
>> the
>> query have parameter. The query below don´t execute:
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = @.vida
>> OLEDB error: "An error occurred while executing the query. The given
>> type
>> name was unrecognized"
>> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
>> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
>> driver][SQL
>> Server] must declare variable @.vida"
>> versions used: 04.10.0049 (provided by Sybase support)
>> Someone have the same problem?
>> Thanks,
>> Landry
>>
>>
>>
>>
>

Problem with querystringparameter

Hi, I'm having problems with the querystring parameter in a SQLDataSource, I think the SelectCommand is not getting the value of the Querystringparameter, here is the code:

<asp:SqlDataSourceID="SqlDataSource1"

runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"

SelectCommand="SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @.nombre + '%'">

<SelectParameters><asp:QueryStringParameterName="Nombre"QueryStringField="Nombre"/></SelectParameters></asp:SqlDataSource>

I've tried many things like...

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @.nombre"

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' & @.nombre & '%'"

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @.nombre + '%'"

And nothing work, where's the problem?, I send the value of the SelectCommand of the DataSource and the @.name is not replaced by any value.

Use

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @.nombre"

and set the value of @.nombre as

@.nombre = '%' + yourValue + '%'

|||The third option should work.

Problem with popup calendar in RS2005

Hi!

I have report with datetime parameter. Report has Language property set to Polish (Poland) but popup calendar displays Sunday as the first day of week. Here in Poland it should be Monday. Only this is inconsistent with Polish settings, the rest is ok - calendar uses Polish names for months and weekdays, and inserted date is also in Polish format (yyyy-mm-dd).

Is it possible to change settings of popup calendar?

Greg

Currently there is no way to change this. We are aware of the issue and it could show up in a future release. If there is significant business impact for you I would suggest contacting customer support to see if you can have the issue resolved.

problem with parameters in report url

I'm am trying to produce a report which has a parameter value in its url.
as I understand it
http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details&job_name=PAPERLESS
should produce the job_details report for the job named "PAPERLESS". but all
I get is the exact same page as I get if I use
http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details
I am using reporting servces 2005, can anyone see what I am doing wrong?
Thanks in advance
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1update, I found out I should have user the following url
http://reports.server.local/Reportserver/Pages/ReportViewer.aspx?%2fdrift%2fjob_details&rs%3aCommand=Render&job_navn=PAPERLESS
tvb wrote:
>I'm am trying to produce a report which has a parameter value in its url.
>as I understand it
>http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details&job_name=PAPERLESS
>should produce the job_details report for the job named "PAPERLESS". but all
>I get is the exact same page as I get if I use
>http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details
>I am using reporting servces 2005, can anyone see what I am doing wrong?
>Thanks in advance
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1

Problem with parameterized query.

I'm trying to use a parameterized query where the parameter is the table I'm querying from (i.e. "select * from @.datatable). Whenever I try to execute the report, I get an error stating that I "... must declare the table variable @.datatable". I think that's because my sql is not legal -- I haven't defined the variable to be an acutal table structure. Is that correct?

If so is there any other way to select from a table name that is dynamic using the paramteter structure that RDL supports? I have been able to do what I want by making the value of the commandtext element an expression that evaluates to the proper sql. Parameters help cut down on possible malicious SQL and I'm hoping to be able to make use of the supported parameter system in RS if possible.

Any help is appreciated.

You can't substitute a table name using standard parameterization. Basically, you would either need to write some sort of stored proc that constructed the SQL and used an EXEC statement or you can use an expression string for the query.

="SELECT * FROM [" & Parameters!TableName.Value & "]"

You need to be careful here about SQL injection. If someone passes in "FOO GO DROP DATABASE XXX" as the parameter, you are in for trouble. You would want to write some sort of function to vaildate the input.

Problem with parameter selection.

i have a first parameter where user can select either office or hometown selection. based on this selection i have two more paramters in which only one should be populated and the other should be disabled.

i was able to manage to do it, but when i veiw it in the report viewer the problem is its not populating the values for other one which is supposed to be at the same time it says select a value in that combo and report doesn't execute bcoz of this.

any help.

parameter1 choices : office, hometown.

parameter2: will be populated if office is selected

parameter3: will be populated if hometown is selected.

is there a way to disable completely upon selection of the first one.

Thanks

Kishore.

Hi,

for the second parameter if you are using the stored procedure then pass the first parameter value i.e Parameters!Param1.Value,to do this in the dataset of second parameter(ds2) click on Parameters tab,value:Parameters!Param1.Value.This will automatically populate the second parameter.And coming to the Report->Parameters->Param2->Available values From ds2.

Hope this helps

I

|||

Thanks for the reply,

I am able to do that. but the thing thats bothering me is in the report viewer, i cant run the report without selecting all the parameters, in my case here what ever the user selects by home or office.. accordingly the combo box is populated and the other one shouldn't and its doing it but the problem is when i run the rep in viewer, its asking me to select a value for the no populated box. the report wont execute if i leave it like that.

thnks.

Problem with parameter default when redeploying report

I've written a rss script file to automate publishing of reports to
our server. I've encountered a problem when republishing reports that
have a default value set for a parameter.
If I change the default value of the parameter in the report rdl and
then try to republish, that parameter's default value is not getting
changed on the server. If I add or remove parameters then the server
gets updated correctly. It's only if I change the default value that
the update is not happening.
I've tried with both the CreateReport and SetReportDefinition
functions. The only way I've gotten this to work is to delete the
report and then republish but this is not an acceptable solution
because it also deletes report history and subscriptions.
I'm using RS2005.
Any help is appreciated.Default values for parameters are a little like datasources, in that you
have to explicitly indicate that you want to override a previous definition
of a datasource when you re-publish a report to a server. Basically the idea
is that your testbed, from which you publish, may not be the same as the
server environment, and you want to keep those things separate, and I'm
saying that parameters' defaults are treated like datasources in this
respect.
OK so far?
If you were handling this interactively using the Report Manager interface,
and assuming you have appropriate rights, you know that you can see the Data
Sources and configure them from the Properties tab of a report. Again, the
assumption is not made that the data source information for this report is
re-deployable and automatically written from your test bed.
Similarly, if a report has parameters, when you have selected the Properties
tab, you should see a Parameters item in the left-hand menu along with Data
Sources. Here you can set the default values differently from how they are
currently set -- I don't really understand whether "Override default" works
all the time or not, you will see it in the dialog, though. Never mind.
Here is where you can fix whatever you don't like about how the report got
re-published.
However, you say "I've tried with both the CreateReport and
SetReportDefinition functions", indicating that you are using web services
rather than interactively publishing. I understand this -- just consider
the above explanation a way to conceptualize *why* the parameters work the
way they do and require a separate step, rather than the way you expected.
I think that you may need to use the .SetReportParameters method here,
explicitly providing the new information to indicate that, yes, you want to
change the default values. If not, it may be the .SetProperties method.
I hope this works for you -- if not, you may be able to use the explanation
above to figure out the correct web service approach <s>.
>L<
<bruce42@.gmail.com> wrote in message
news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
> I've written a rss script file to automate publishing of reports to
> our server. I've encountered a problem when republishing reports that
> have a default value set for a parameter.
> If I change the default value of the parameter in the report rdl and
> then try to republish, that parameter's default value is not getting
> changed on the server. If I add or remove parameters then the server
> gets updated correctly. It's only if I change the default value that
> the update is not happening.
> I've tried with both the CreateReport and SetReportDefinition
> functions. The only way I've gotten this to work is to delete the
> report and then republish but this is not an acceptable solution
> because it also deletes report history and subscriptions.
> I'm using RS2005.
> Any help is appreciated.
>|||So, from what you're saying, it's intentional that the parameter
defaults are not being overriden. You mention an "override defaults",
can you be more specific? I can see an "OverwriteDataSources" if I
publish straight from VS, but I've found nothing regarding overwriting
parameter defaults.
I'm familiar with the SetReportParameters method, what I'm trying to
do is avoid writing specific scripts for each report that I need to
publish. The rss script I use to publish now is a generic script that
I can use against any of my reports. Setting the default values
manually via the report manager interface is not really an option for
me. For one reason, it introduces the possibility of me setting
values differently than what was actually used in our test
environment, and two, we are using query based defaults and the report
manager interface doesn't give you enough detail to even be able to
make these changes (i.e. it doesn't show dataset or value field).
Currently the best idea I have for how to resolve this issue is to
publish my report to a temporary location (where it does not already
exist), loop through all of the parameters and capture the defaults, I
can then publish the report to it's normal location and do a
SetReportParameters using the default values I collected. I'm not
especially happy with this approach, it feels a little kludgy to me,
but at least it keeps me from having to write report specific rss
scripts.
Any other ideas?
thanks for your response...
-bruce
On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Defaultvalues for parameters are a little like datasources, in that you
> have to explicitly indicate that you want to override a previous definition
> of a datasource when you re-publish a report to a server. Basically the idea
> is that your testbed, from which you publish, may not be the same as the
> server environment, and you want to keep those things separate, and I'm
> saying that parameters' defaults are treated like datasources in this
> respect.
> OK so far?
> If you were handling this interactively using the Report Manager interface,
> and assuming you have appropriate rights, you know that you can see the Data
> Sources and configure them from the Properties tab of a report. Again, the
> assumption is not made that the data source information for this report is
> re-deployable and automatically written from your test bed.
> Similarly, if a report has parameters, when you have selected the Properties
> tab, you should see a Parameters item in the left-hand menu along with Data
> Sources. Here you can set thedefaultvalues differently from how they are
> currently set -- I don't really understand whether "Overridedefault" works
> all the time or not, you will see it in the dialog, though. Never mind.
> Here is where you can fix whatever you don't like about how the report got
> re-published.
> However, you say "I've tried with both the CreateReport and
> SetReportDefinition functions", indicating that you are using web services
> rather than interactively publishing. I understand this -- just consider
> the above explanation a way to conceptualize *why* the parameters work the
> way they do and require a separate step, rather than the way you expected.
> I think that you may need to use the .SetReportParameters method here,
> explicitly providing the new information to indicate that, yes, you want to
> change thedefaultvalues. If not, it may be the .SetProperties method.
> I hope this works for you -- if not, you may be able to use the explanation
> above to figure out the correct web service approach <s>.
> >L<
> <bruc...@.gmail.com> wrote in message
> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>
> > I've written a rss script file to automate publishing of reports to
> > our server. I've encountered a problem when republishing reports that
> > have adefaultvalue set for aparameter.
> > If I change thedefaultvalue of theparameterin the report rdl and
> > then try to republish, thatparameter'sdefaultvalue is not getting
> > changed on the server. If I add or remove parameters then the server
> > gets updated correctly. It's only if I change thedefaultvalue that
> > the update is not happening.
> > I've tried with both the CreateReport and SetReportDefinition
> > functions. The only way I've gotten this to work is to delete the
> > report and then republish but this is not an acceptable solution
> > because it also deletes report history and subscriptions.
> > I'm using RS2005.
> > Any help is appreciated.- Hide quoted text -
> - Show quoted text -|||Hi Bruce,
>> Any other ideas?
Forgive my lateness of reply (I will CC your e-mail to make sure you see
this) -- I don't get to the forum all that often.
AFAIK you are correct about not having the Overwrite parameter option to
match the Overwrite datasources when you publish straight from VS. When I
mentioned the "override defaults" I was talking only about the interactive
manager interface.
I do agree with you not only that it is confusing but also that, in most
cases, it is not advisable to set values differently in the test environment
than you would set in production. However -- bear with me, I am trying to
envision what was supposed to be the purpose of this "feature" -- we can
imagine that the designers of this system thought it *was* a good idea to
have an "override defaults" so that you could manage the report differently
on different servers, whether for test versus production or deployment of a
generic report to different customers.
For example there might be a sample size used for the test box that would be
different from production, or some sort of customer-specific value that you
wanted to use to brand a generic report.
Now to address your question...
For your generic script purposes, you might need to do something similar to
reflection to "publish" each report.
So far, I'm just restating something that you may have already tried with
your "publish to a temporary location". You could obviously pull the
parameters out of the appropriate Catalog field on the server, or use
GetReportParameters, if you've done that.
However, you don't really need to do that. Remember that the parameters
exist in the RDL, without publication, as a set of XML nodes. So, without
temporarily publishing anywhere, you should be able to read them out of the
RDL and issue the appropriate calls to set them properly on the target.
I hope this makes sense. If you like, you can e-mail me to discuss further
if it doesn't <g>. Again, I don't get here all that much...
>L<
<bruce42@.gmail.com> wrote in message
news:1176117653.014697.204110@.w1g2000hsg.googlegroups.com...
> So, from what you're saying, it's intentional that the parameter
> defaults are not being overriden. You mention an "override defaults",
> can you be more specific? I can see an "OverwriteDataSources" if I
> publish straight from VS, but I've found nothing regarding overwriting
> parameter defaults.
> I'm familiar with the SetReportParameters method, what I'm trying to
> do is avoid writing specific scripts for each report that I need to
> publish. The rss script I use to publish now is a generic script that
> I can use against any of my reports. Setting the default values
> manually via the report manager interface is not really an option for
> me. For one reason, it introduces the possibility of me setting
> values differently than what was actually used in our test
> environment, and two, we are using query based defaults and the report
> manager interface doesn't give you enough detail to even be able to
> make these changes (i.e. it doesn't show dataset or value field).
> Currently the best idea I have for how to resolve this issue is to
> publish my report to a temporary location (where it does not already
> exist), loop through all of the parameters and capture the defaults, I
> can then publish the report to it's normal location and do a
> SetReportParameters using the default values I collected. I'm not
> especially happy with this approach, it feels a little kludgy to me,
> but at least it keeps me from having to write report specific rss
> scripts.
> Any other ideas?
> thanks for your response...
> -bruce
> On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Defaultvalues for parameters are a little like datasources, in that you
>> have to explicitly indicate that you want to override a previous
>> definition
>> of a datasource when you re-publish a report to a server. Basically the
>> idea
>> is that your testbed, from which you publish, may not be the same as the
>> server environment, and you want to keep those things separate, and I'm
>> saying that parameters' defaults are treated like datasources in this
>> respect.
>> OK so far?
>> If you were handling this interactively using the Report Manager
>> interface,
>> and assuming you have appropriate rights, you know that you can see the
>> Data
>> Sources and configure them from the Properties tab of a report. Again,
>> the
>> assumption is not made that the data source information for this report
>> is
>> re-deployable and automatically written from your test bed.
>> Similarly, if a report has parameters, when you have selected the
>> Properties
>> tab, you should see a Parameters item in the left-hand menu along with
>> Data
>> Sources. Here you can set thedefaultvalues differently from how they are
>> currently set -- I don't really understand whether "Overridedefault"
>> works
>> all the time or not, you will see it in the dialog, though. Never mind.
>> Here is where you can fix whatever you don't like about how the report
>> got
>> re-published.
>> However, you say "I've tried with both the CreateReport and
>> SetReportDefinition functions", indicating that you are using web
>> services
>> rather than interactively publishing. I understand this -- just consider
>> the above explanation a way to conceptualize *why* the parameters work
>> the
>> way they do and require a separate step, rather than the way you
>> expected.
>> I think that you may need to use the .SetReportParameters method here,
>> explicitly providing the new information to indicate that, yes, you want
>> to
>> change thedefaultvalues. If not, it may be the .SetProperties method.
>> I hope this works for you -- if not, you may be able to use the
>> explanation
>> above to figure out the correct web service approach <s>.
>> >L<
>> <bruc...@.gmail.com> wrote in message
>> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>>
>> > I've written a rss script file to automate publishing of reports to
>> > our server. I've encountered a problem when republishing reports that
>> > have adefaultvalue set for aparameter.
>> > If I change thedefaultvalue of theparameterin the report rdl and
>> > then try to republish, thatparameter'sdefaultvalue is not getting
>> > changed on the server. If I add or remove parameters then the server
>> > gets updated correctly. It's only if I change thedefaultvalue that
>> > the update is not happening.
>> > I've tried with both the CreateReport and SetReportDefinition
>> > functions. The only way I've gotten this to work is to delete the
>> > report and then republish but this is not an acceptable solution
>> > because it also deletes report history and subscriptions.
>> > I'm using RS2005.
>> > Any help is appreciated.- Hide quoted text -
>> - Show quoted text -
>

Problem with Parameter

Hi ,

I am having a peculiar problem when i am using report parameters in a report. I am using report paramter to let the user select the month for which the report has to run, but the drop down has ordered the months in alphabetical order,for selection Is there any way I can set it to list it in the correct order ?

Thanks in advance
PMJ

If you are using an analysis services datasource, there is an easy way to solve this.

Open up your Date Dimension. Select the Month attribute. Right-click and select properties. Set the "Order By" property to key. Build and deploy your solution. Refresh the project in Reporting Services. And you will be good to go.

|||Hi Joel,

thanks a lot it worked !!!

Thanks and regards
PMNJ

problem with output parameter stored procedure

My stored procedure below compiled - not sure if it is even correct though.
I have to get the sum of a totalpaid column from one table and get the sum o
f
a totalpaid column from a second table. I need to return the difference of
these sums.
---
CREATE PROCEDURE [stp_SumDiffTotalPaid]
@.SumDiff decimal output
AS
declare @.a decimal, @.b decimal
Select @.a = sum(pd_totl_amt) from tblncanschd
Select @.b = sum(totalpaid) from tblncalnonschedemipaid
Set @.SumDiff = @.a - @.b
Return
GO
----
-
Here is how I call my sp from query analyzer:
declare @.a decimal
stp_sumdiffTotalpaid, @.sumdiff = @.a output
This is not working. Any suggestions appreciated how to get this to work or
if there is a simpler way to do this.
Thanks,
RichI figured it out
Declare @.a
Excec stp_sumdiffTotalpaid @.a output
or
Excec stp_sumdiffTotalpaid @.sumdiff = @.a output
print @.a
I was missing Execute
I guess I don't need the comma after the sp either.
"Rich" wrote:

> My stored procedure below compiled - not sure if it is even correct though
.
> I have to get the sum of a totalpaid column from one table and get the sum
of
> a totalpaid column from a second table. I need to return the difference o
f
> these sums.
> ---
> CREATE PROCEDURE [stp_SumDiffTotalPaid]
> @.SumDiff decimal output
> AS
> declare @.a decimal, @.b decimal
> Select @.a = sum(pd_totl_amt) from tblncanschd
> Select @.b = sum(totalpaid) from tblncalnonschedemipaid
> Set @.SumDiff = @.a - @.b
> Return
> GO
> ----
--
> Here is how I call my sp from query analyzer:
> declare @.a decimal
> stp_sumdiffTotalpaid, @.sumdiff = @.a output
> This is not working. Any suggestions appreciated how to get this to work
or
> if there is a simpler way to do this.
> Thanks,
> Rich

Problem with output parameter in SP

I am having problems returning the value of a parameter I have set in my stored procedure. Basically this is an authentication to check username and password for my login page. However, I am receiving the error:

Procedure 'DBAuthenticate' expects parameter '@.@.ID', which was not supplied.

This stored procedure is supposed to return a -1 if the username is not found, -2 if the password does not match, or the @.ID parameter, which is the user ID, if it is successful. How do i go about fixing this SP so that I am returning this output for @.ID?

CREATE PROCEDURE DBAuthenticate

(

@.UserName nVarChar (20),
@.Password nVarChar (20),
@.@.ID varchar(4) OUTPUT

)

AS
Declare @.ActualPassword nVarchar (20)

Select

@.@.ID = RegionID,

@.ActualPassword =regpassword

From dbo.Regions

Where Region = @.Username

If @.@.ID is not null
Begin
if @.Password =@.actualpassword

Select @.@.ID
Else

Select -2
End
Else

Select -1
GOMake sure that you specify OUTPUT in your EXECUTE call. If either the caller or the called routine fail to specify OUTPUT, the value isn't returned.

-PatP|||A couple of questions/things:

1. Why do you want to return something that your code already knows about? Return 1 instead.
2. Naming your parameter with @.@.xxx would result in server knowing it as @.xxx, not xxx as expected. And it doesn't make your parameter a "global" variable either.
3. Based on your logic @.ID variable will ALWAYS have whatever value was retrieved from Region table based on @.UserName or NULL, regardless of whether authentication was successful or not. You probably need to change the path of your authentication algorythm. How about setting it to NULL even if it exists but the password is wrong?