HELP!!!! - Report parameter "Read Only"

I'm stuck with a report not being able to use the URL parameter.

I get the error:

The report parameter is read-only and cannot be modified.

I do have a prompting text for the report, and I've checked the XML and there are <PROMPT></PROMPT> tags!

Most likely, you've modified the parameter definition in Report Manager to make it read-only. If you are using SQL 2005, this can happen when you mark it in the designer as 'Internal'.|||

I just dealt with a similar problem (working in RS2000) and no matter what I tried, I couldn't get the error to go away. I tried removing the parameter and re-adding it manually. I tried removing the procedure that required the parameter, re-deploying, and then adding the procedure (and thus the parameter) back in. That still didn't work. This is what I finally did:

1) Removed the parameter from the procedure (commented out the param and any references to it in the sp)

2) Refresh the dataset (to remove the parameter). Ensured the parameter was removed from the report.

3) Re-deployed the report (without the offending parameter). It worked.

4) Went back to the sp and uncommented out the parameter and all references to it

5) Refreshed the dataset (to add the parameter back into the report).

6) Re-deployed. It worked and now accepted the parameter.

I can only surmise that RS had some former reference to the parameter that indicated it was read only, and any re-deployment that I did didn't overwrite that reference. Only by completely removing the parameter, re-deploying, and adding the parameter back in did this issue go away.

I hope this helps someone out there.

|||I got the same problem with VS2005. One time paremeter was checked as "Not Prompt user". After that I changed, deleted and recreated parameter, redeploys report to report server but recived the same error "Report parameter Read Only". Its helped a manualy chnage property of report parameter direct on report server using Report Manager( check on "Propmpt User" property of required parameter). Redeploy of report from VS don't cause changes of report properties on server.

HELP!!!! - Report parameter "Read Only"

I'm stuck with a report not being able to use the URL parameter.

I get the error:

The report parameter is read-only and cannot be modified.

I do have a prompting text for the report, and I've checked the XML and there are <PROMPT></PROMPT> tags!

Most likely, you've modified the parameter definition in Report Manager to make it read-only. If you are using SQL 2005, this can happen when you mark it in the designer as 'Internal'.|||

I just dealt with a similar problem (working in RS2000) and no matter what I tried, I couldn't get the error to go away. I tried removing the parameter and re-adding it manually. I tried removing the procedure that required the parameter, re-deploying, and then adding the procedure (and thus the parameter) back in. That still didn't work. This is what I finally did:

1) Removed the parameter from the procedure (commented out the param and any references to it in the sp)

2) Refresh the dataset (to remove the parameter). Ensured the parameter was removed from the report.

3) Re-deployed the report (without the offending parameter). It worked.

4) Went back to the sp and uncommented out the parameter and all references to it

5) Refreshed the dataset (to add the parameter back into the report).

6) Re-deployed. It worked and now accepted the parameter.

I can only surmise that RS had some former reference to the parameter that indicated it was read only, and any re-deployment that I did didn't overwrite that reference. Only by completely removing the parameter, re-deploying, and adding the parameter back in did this issue go away.

I hope this helps someone out there.

|||I got the same problem with VS2005. One time paremeter was checked as "Not Prompt user". After that I changed, deleted and recreated parameter, redeploys report to report server but recived the same error "Report parameter Read Only". Its helped a manualy chnage property of report parameter direct on report server using Report Manager( check on "Propmpt User" property of required parameter). Redeploy of report from VS don't cause changes of report properties on server.

HELP!!!! - Report parameter "Read Only"

I'm stuck with a report not being able to use the URL parameter.

I get the error:

The report parameter is read-only and cannot be modified.

I do have a prompting text for the report, and I've checked the XML and there are <PROMPT></PROMPT> tags!

Most likely, you've modified the parameter definition in Report Manager to make it read-only. If you are using SQL 2005, this can happen when you mark it in the designer as 'Internal'.|||

I just dealt with a similar problem (working in RS2000) and no matter what I tried, I couldn't get the error to go away. I tried removing the parameter and re-adding it manually. I tried removing the procedure that required the parameter, re-deploying, and then adding the procedure (and thus the parameter) back in. That still didn't work. This is what I finally did:

1) Removed the parameter from the procedure (commented out the param and any references to it in the sp)

2) Refresh the dataset (to remove the parameter). Ensured the parameter was removed from the report.

3) Re-deployed the report (without the offending parameter). It worked.

4) Went back to the sp and uncommented out the parameter and all references to it

5) Refreshed the dataset (to add the parameter back into the report).

6) Re-deployed. It worked and now accepted the parameter.

I can only surmise that RS had some former reference to the parameter that indicated it was read only, and any re-deployment that I did didn't overwrite that reference. Only by completely removing the parameter, re-deploying, and adding the parameter back in did this issue go away.

I hope this helps someone out there.

|||I got the same problem with VS2005. One time paremeter was checked as "Not Prompt user". After that I changed, deleted and recreated parameter, redeploys report to report server but recived the same error "Report parameter Read Only". Its helped a manualy chnage property of report parameter direct on report server using Report Manager( check on "Propmpt User" property of required parameter). Redeploy of report from VS don't cause changes of report properties on server.

HELP!!!! - Report parameter "Read Only"

I'm stuck with a report not being able to use the URL parameter.

I get the error:

The report parameter is read-only and cannot be modified.

I do have a prompting text for the report, and I've checked the XML and there are <PROMPT></PROMPT> tags!

Most likely, you've modified the parameter definition in Report Manager to make it read-only. If you are using SQL 2005, this can happen when you mark it in the designer as 'Internal'.|||

I just dealt with a similar problem (working in RS2000) and no matter what I tried, I couldn't get the error to go away. I tried removing the parameter and re-adding it manually. I tried removing the procedure that required the parameter, re-deploying, and then adding the procedure (and thus the parameter) back in. That still didn't work. This is what I finally did:

1) Removed the parameter from the procedure (commented out the param and any references to it in the sp)

2) Refresh the dataset (to remove the parameter). Ensured the parameter was removed from the report.

3) Re-deployed the report (without the offending parameter). It worked.

4) Went back to the sp and uncommented out the parameter and all references to it

5) Refreshed the dataset (to add the parameter back into the report).

6) Re-deployed. It worked and now accepted the parameter.

I can only surmise that RS had some former reference to the parameter that indicated it was read only, and any re-deployment that I did didn't overwrite that reference. Only by completely removing the parameter, re-deploying, and adding the parameter back in did this issue go away.

I hope this helps someone out there.

|||I got the same problem with VS2005. One time paremeter was checked as "Not Prompt user". After that I changed, deleted and recreated parameter, redeploys report to report server but recived the same error "Report parameter Read Only". Its helped a manualy chnage property of report parameter direct on report server using Report Manager( check on "Propmpt User" property of required parameter). Redeploy of report from VS don't cause changes of report properties on server.

HELP! Passing parameters

Still need help passing a criteria parameter query from a SQL Function (i.e. @.StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].

If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.

Could you explain this a bit more in detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

HELP! Parameter default or provided value not valid???

Default value or value provided for the report parameter 'ParamaterName' is
not a valid value. (rsInvalidReportParameter)
I am getting this error when I am trying to run a deployed report. However,
this is driving me mad because in the report preview within VS.Net the report
works fine and i can choose the same parameter settings from within the dev
environment with no problems. I am populating this parameter with a dropdown
with valid values from a sproc (and this I've done a thousand times before).
I can not figure out why it is throwing this error, particularly since it
works perfectly fine in the preview pane. How could it be crappin out when I
deploy it?!?! I am going insane!
-isamuAre you sure it is exactly in the valid values list? The list is case
sensitive. Also, if you are using date / time functions you have to be
careful as they will need to be exact matches.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:D6B6D595-8748-4308-8EF2-2B7F846EB9B5@.microsoft.com...
> Default value or value provided for the report parameter 'ParamaterName'
> is
> not a valid value. (rsInvalidReportParameter)
> I am getting this error when I am trying to run a deployed report.
> However,
> this is driving me mad because in the report preview within VS.Net the
> report
> works fine and i can choose the same parameter settings from within the
> dev
> environment with no problems. I am populating this parameter with a
> dropdown
> with valid values from a sproc (and this I've done a thousand times
> before).
> I can not figure out why it is throwing this error, particularly since it
> works perfectly fine in the preview pane. How could it be crappin out
> when I
> deploy it?!?! I am going insane!
> -isamu|||Hi Brian,
I believe there must've been some buggy stuff going on. What I did to fix
it, on a whim, was change all my variable names in my stored procedure and in
my report, and voila, guess what? it worked. I don't know what could've
been causing it to act so strangely, but I'm just happy it got fixed. Wasted
a good 3 hours of time though.
"Brian Welcker [MSFT]" wrote:
> Are you sure it is exactly in the valid values list? The list is case
> sensitive. Also, if you are using date / time functions you have to be
> careful as they will need to be exact matches.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "isamu" <isamu@.discussions.microsoft.com> wrote in message
> news:D6B6D595-8748-4308-8EF2-2B7F846EB9B5@.microsoft.com...
> > Default value or value provided for the report parameter 'ParamaterName'
> > is
> > not a valid value. (rsInvalidReportParameter)
> >
> > I am getting this error when I am trying to run a deployed report.
> > However,
> > this is driving me mad because in the report preview within VS.Net the
> > report
> > works fine and i can choose the same parameter settings from within the
> > dev
> > environment with no problems. I am populating this parameter with a
> > dropdown
> > with valid values from a sproc (and this I've done a thousand times
> > before).
> > I can not figure out why it is throwing this error, particularly since it
> > works perfectly fine in the preview pane. How could it be crappin out
> > when I
> > deploy it?!?! I am going insane!
> >
> > -isamu
>
>

Help! Parameter Conversion Unix Time

My dates are in unix time and the paramaters are entered as 7/6/2004. How can I convert the date in the parameter field back into unix time so it will retireve the records.
Unix time is the number of seconds from 1/1/1970 at 12:00am
ThanksFor a variable dateTime of type DateTime you can use the follwing
expression:
dateTime.Subtract(new DateTime(1970, 1, 1)).TotalSeconds
This returns floating point value and may contain fractions of a second.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:52981B6F-5FB5-4638-8959-8CFE298D5503@.microsoft.com...
> My dates are in unix time and the paramaters are entered as 7/6/2004. How
can I convert the date in the parameter field back into unix time so it will
retireve the records.
> Unix time is the number of seconds from 1/1/1970 at 12:00am
> Thanks

HELP! Default Date Parameters Expression in Reporting Services

How do I add a default date parameter to get this:

@.StartDate: Last Sunday

@.EndDate : Yesterday

So if I was to run this today I want the reports StartDate As Sunday, November 11, 2007

and

EndDate >>> Yesterday Thursday, November 15, 2007

Hi,

From your description, it seems that you want to add some parameters to filter the report, right?

If so, I suggest that you should associate a Query Parameter with a Report Parameter. Assume that you handle your querying works in a stored procedure, you can use getdate() method to get the current date, datediff() and dateadd() method to calculate the date. See the following code snippet (Just the idea, not the executable code.):

declare @.InputDay,@.StartDay,@.EndDay

-- @.EndDay=getdate()-1
case @.InputDay='Sun'
-- @.StartDay=getdate()-7
case @.InputDay='Mon'
-- @.StartDay=getdate()-1
case @.InputDay='Tue'
-- @.StartDay=getdate()-2
case @.InputDay='Wed'
-- @.StartDay=getdate()-3
case @.InputDay='Thu'
-- @.StartDay=getdate()-4
case @.InputDay='Fri'
-- @.StartDay=getdate()-5
case @.InputDay='Sat'
-- @.StartDay=getdate()-6

Thanks.

Help! Call a code block based on parameter name

I have a column heading that needs to call a function based on the value
contained in the heading (which is a parameter selected by the user).
Example: they can choose system, database, network or application. I have
functions which do different translations and the functions are named
translateSystem, translateApplication, translateDatabase, etc.
I tried to do this by adding a textbox (which I named tbResType) with the
following code in it:
IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
IIF(Parameters!ResType.value = "process",
"translateProcess",IIF(Parameters!ResType.value = "database",
"translateDatabase", IIF(Parameters!ResType.value = "application",
"translateApplication", "translateSystem"))))
Then I tried to use that value in the column heading to call the appropriate
code block (the function translates into multiple languages so its parm is
the language value the user chooses)
=code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
anyway ... the translation for the first textbox works fine and I see
translateProcess when I entered process for the ResType parm but the column
heading gives me an error ==> The value expression for the textbox â'textbox6â'
contains an error: [BC30203] Identifier expected.
Any clues?You may have to write a wrapper around you actual calls within your code
block. This wrapper will accept the bResType parameter and the sub call the
appropriate routines from there.
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>I have a column heading that needs to call a function based on the value
> contained in the heading (which is a parameter selected by the user).
> Example: they can choose system, database, network or application. I have
> functions which do different translations and the functions are named
> translateSystem, translateApplication, translateDatabase, etc.
> I tried to do this by adding a textbox (which I named tbResType) with the
> following code in it:
> IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> IIF(Parameters!ResType.value = "process",
> "translateProcess",IIF(Parameters!ResType.value = "database",
> "translateDatabase", IIF(Parameters!ResType.value = "application",
> "translateApplication", "translateSystem"))))
> Then I tried to use that value in the column heading to call the
> appropriate
> code block (the function translates into multiple languages so its parm is
> the language value the user chooses)
> =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> anyway ... the translation for the first textbox works fine and I see
> translateProcess when I entered process for the ResType parm but the
> column
> heading gives me an error ==> The value expression for the textbox
> 'textbox6'
> contains an error: [BC30203] Identifier expected.
> Any clues?
>|||Thanks Craig ... I am not sure how to do that. Is there an example somewhere
(simple example...) I am very new with vb .net
"Craig" wrote:
> You may have to write a wrapper around you actual calls within your code
> block. This wrapper will accept the bResType parameter and the sub call the
> appropriate routines from there.
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >I have a column heading that needs to call a function based on the value
> > contained in the heading (which is a parameter selected by the user).
> > Example: they can choose system, database, network or application. I have
> > functions which do different translations and the functions are named
> > translateSystem, translateApplication, translateDatabase, etc.
> >
> > I tried to do this by adding a textbox (which I named tbResType) with the
> > following code in it:
> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> > IIF(Parameters!ResType.value = "process",
> > "translateProcess",IIF(Parameters!ResType.value = "database",
> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> > "translateApplication", "translateSystem"))))
> >
> > Then I tried to use that value in the column heading to call the
> > appropriate
> > code block (the function translates into multiple languages so its parm is
> > the language value the user chooses)
> >
> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >
> > anyway ... the translation for the first textbox works fine and I see
> > translateProcess when I entered process for the ResType parm but the
> > column
> > heading gives me an error ==> The value expression for the textbox
> > 'textbox6'
> > contains an error: [BC30203] Identifier expected.
> >
> > Any clues?
> >
>
>|||Put this in your code...
Public Function GetTranslation(ByVal Application As String, ByVal Language
As String) As String
Select Case Application
Case "nw Interface"
Return translateNetwork(Language)
Case "process"
Return translateProcess(Language)
Case "database"
Return translateDatabase(Language)
Case "application"
Return translateApplication(Language)
End Select
Return ""
End Function
Then in your header just put
=code.GetTranslation(Parameters!Language.Value)
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> Thanks Craig ... I am not sure how to do that. Is there an example
> somewhere
> (simple example...) I am very new with vb .net
> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its parm
>> > is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>|||Sorry fogot one other parameter in the call...
you need...
=code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
Craig
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Put this in your code...
> Public Function GetTranslation(ByVal Application As String, ByVal Language
> As String) As String
> Select Case Application
> Case "nw Interface"
> Return translateNetwork(Language)
> Case "process"
> Return translateProcess(Language)
> Case "database"
> Return translateDatabase(Language)
> Case "application"
> Return translateApplication(Language)
> End Select
> Return ""
> End Function
> Then in your header just put
> =code.GetTranslation(Parameters!Language.Value)
>
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
>> Thanks Craig ... I am not sure how to do that. Is there an example
>> somewhere
>> (simple example...) I am very new with vb .net
>> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the
>> >value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its
>> > parm is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>
>|||I'm sorry ... I forgot to reply that it worked! Thank you very much and I
have rated it as such.
"Craig" wrote:
> Sorry fogot one other parameter in the call...
> you need...
> =code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
> Craig
> "Craig" <craigm_richardson@.hotmail.com> wrote in message
> news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Put this in your code...
> >
> > Public Function GetTranslation(ByVal Application As String, ByVal Language
> > As String) As String
> >
> > Select Case Application
> >
> > Case "nw Interface"
> >
> > Return translateNetwork(Language)
> >
> > Case "process"
> >
> > Return translateProcess(Language)
> >
> > Case "database"
> >
> > Return translateDatabase(Language)
> >
> > Case "application"
> >
> > Return translateApplication(Language)
> >
> > End Select
> >
> > Return ""
> >
> > End Function
> >
> > Then in your header just put
> >
> > =code.GetTranslation(Parameters!Language.Value)
> >
> >
> > Craig
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> >> Thanks Craig ... I am not sure how to do that. Is there an example
> >> somewhere
> >> (simple example...) I am very new with vb .net
> >>
> >> "Craig" wrote:
> >>
> >> You may have to write a wrapper around you actual calls within your code
> >> block. This wrapper will accept the bResType parameter and the sub call
> >> the
> >> appropriate routines from there.
> >>
> >> Craig
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >> >I have a column heading that needs to call a function based on the
> >> >value
> >> > contained in the heading (which is a parameter selected by the user).
> >> > Example: they can choose system, database, network or application. I
> >> > have
> >> > functions which do different translations and the functions are named
> >> > translateSystem, translateApplication, translateDatabase, etc.
> >> >
> >> > I tried to do this by adding a textbox (which I named tbResType) with
> >> > the
> >> > following code in it:
> >> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> >> > IIF(Parameters!ResType.value = "process",
> >> > "translateProcess",IIF(Parameters!ResType.value = "database",
> >> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> >> > "translateApplication", "translateSystem"))))
> >> >
> >> > Then I tried to use that value in the column heading to call the
> >> > appropriate
> >> > code block (the function translates into multiple languages so its
> >> > parm is
> >> > the language value the user chooses)
> >> >
> >> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >> >
> >> > anyway ... the translation for the first textbox works fine and I see
> >> > translateProcess when I entered process for the ResType parm but the
> >> > column
> >> > heading gives me an error ==> The value expression for the textbox
> >> > 'textbox6'
> >> > contains an error: [BC30203] Identifier expected.
> >> >
> >> > Any clues?
> >> >
> >>
> >>
> >>
> >
> >
>
>

Help!

All,
My report should look like this:
WTD MTD YTD
Actual value 3 5 7
Percentage 10% 20% 50%
I have set a parameter list based on time dimension in the cube and
they are displayed in different levels representing the time dimension
hierarchy. I'm not sure how I can use the parameter that I select from
this dropdown list and use it to roll up wtd,mtd and ytd values. Any
help will be greatly appreciated.maybe this might be of use:
with
member [measures].[wtd total] as ' IIF([Time].CurrentMember IS [Time].[Week
Level], YourValue, 0)'
member [measures].[mtd total] as ' IIF([Time].CurrentMember IS [Time].[Month
Level], YourValue, 0)'
member [measures].[ytd total] as ' IIF([Time].CurrentMember IS [Time].[Year
Level], YourValue, 0)'
select { [measures].[wtd total]
, [measures].[mtd total]
, [measures].[ytd total]} on columns,
{ [Your Dimensions] } on rows
From YourCube
"Maddie" wrote:
> All,
> My report should look like this:
> WTD MTD YTD
>
> Actual value 3 5 7
> Percentage 10% 20% 50%
>
> I have set a parameter list based on time dimension in the cube and
> they are displayed in different levels representing the time dimension
> hierarchy. I'm not sure how I can use the parameter that I select from
> this dropdown list and use it to roll up wtd,mtd and ytd values. Any
> help will be greatly appreciated.
>

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [zero]
in the table.
--
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
--
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
--
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
--
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
--
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:
>> EXEC @.pass = dbo.usp_GeneratePassword
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
>)
>> Hi all,
>[quoted text clipped - 180 lines]
>> Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"thegekkster via SQLMonster.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [
;zero]
in the table.
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:[vbcol=seagreen]
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
> )
>
>[quoted text clipped - 180 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||I'm glad I was able to help you out.
Dan Guzman
SQL Server MVP
"thegekkster via droptable.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>