help!...sp1 or sp2

After I install SP1 of SP2 for reporting services, I get the following
error:
The version of the report server database is either in a format that is
not
valid, or it cannot be read. The found version is 'C.0.6.54'. The
expected
version is 'C.0.6.43'. To continue, update the version of the report
server
database and verify access rights. (rsInvalidReportServerDatabase)
I am currently running RS enteprise edition with SQL server 2000
Standard edition.
When I used to have RS std. edition with SQL server 2000 std.edition,
I've applied both service packs and didn't get the error.
Can you help me with this problem?
Thanks,If you already installed SP2, you can not install SP1 afterwards and
you do not need to.
Service Packs are cumulative - they contain everything from prior
service packs.
bevarg wrote:
> After I install SP1 of SP2 for reporting services, I get the following
> error:
> The version of the report server database is either in a format that is
> not
> valid, or it cannot be read. The found version is 'C.0.6.54'. The
> expected
> version is 'C.0.6.43'. To continue, update the version of the report
> server
> database and verify access rights. (rsInvalidReportServerDatabase)
> I am currently running RS enteprise edition with SQL server 2000
> Standard edition.
> When I used to have RS std. edition with SQL server 2000 std.edition,
> I've applied both service packs and didn't get the error.
> Can you help me with this problem?
> Thanks,

HELP!! i deleted services!

i have installed vs 2005 and sql server enterprise edition on an xp pro sp2 machine.but i could not connect to the mssqlexpress default database that it came with.in a moment of madness i deleted the mssqlserver services (3 of them).how do i reinstall them? i thought uninstalling the products and reinstalling them would help but it did not.

please help!

cheers OK, wait a minute. You installed the enterprise edition of the MSSQL Service on a XP prof ? This is not possible. What did you actually delete, where and what did you try to achieve ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

yes it it is possible, the only thing is that you do not have access to all the functionality.i have solved the prob by uninstalling and installing the work edition.

thanks

|||Sorry to correct you, but installing SQL Server services won′t be available for enterprise edition, only the client tools.

Jens Suessmeyer.

http://www.sqlserver2005.de

HELP! activation fails CantCommunicateWithReportServerException

Hi
I have read all of the posts in this group (and others!) but still
can't get RS working. I am installing RS on an XP sp2 machine with
VS2003 and SQL2000 sp4. Installation reports activation failed, but
when I check Keys table in ReportServer db there is a row with the
client as 1 and both key binary values.
I have tried rskeymgmt -d, rsactivate -c "path" which generates a new
key, but I still get
" Unable to communicate with report server. Please verify that the
report server is operational. " when I go to
http://localhost/Reports/Pages/Folder.aspx.
And the log file has
aspnet_wp!ui!f80!19/07/2005-09:47:35:: e ERROR: HTTP status code -->
500
--Details--
CantCommunicateWithReportServerException: Unable to communicate with
report server. Please verify that the report server is operational.
at
Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
at
Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
methodname)
at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
at
Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
level)
at
Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain()
aspnet_wp!ui!f80!19/07/2005-09:47:37:: e ERROR: Exception in
ShowErrorPage: System.Threading.ThreadAbortException: Thread was being
aborted.
etc...
I'd be very greatful if someone could assist as I am tearing what's
left of my hair out!Hi
I have found a solution (may not be the only one!).
So, for anyone who is having the same problem...
1. Uninstalled RS
2. I reinstalled the .NET framework (i think this made no difference,
bu who knows)
3. Granted admin rights to ASPNET and IWAM accounts
4. I reinstalled RS, using a domain account rather than the default for
the NT Service
5. replaced the <assemblies> section in the <system.web> section of the
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\web.config file with
<assemblies>
<clear />
<add assembly="ReportingServicesWebServer" />
<add assembly="mscorlib" />
</assemblies>
6. Change the following line in both web.config files
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer
and: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportManager
with <trust level="Full" originUrl="" />
And for me, this fixed it. I <i>really</i> hope this fixes it for you,
too. This has been a REAL pain in the you-konw-what. Why Microsoft
can't get the default installation to work out it is on XP and not 2003
and act accordingly is beyond me.
shl wrote:
> Hi
> I have read all of the posts in this group (and others!) but still
> can't get RS working. I am installing RS on an XP sp2 machine with
> VS2003 and SQL2000 sp4. Installation reports activation failed, but
> when I check Keys table in ReportServer db there is a row with the
> client as 1 and both key binary values.
> I have tried rskeymgmt -d, rsactivate -c "path" which generates a new
> key, but I still get
> " Unable to communicate with report server. Please verify that the
> report server is operational. " when I go to
> http://localhost/Reports/Pages/Folder.aspx.
> And the log file has
> aspnet_wp!ui!f80!19/07/2005-09:47:35:: e ERROR: HTTP status code -->
> 500
> --Details--
> CantCommunicateWithReportServerException: Unable to communicate with
> report server. Please verify that the report server is operational.
> at
> Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
> at
> Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
> methodname)
> at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
> at
> Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
> level)
> at
> Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
> sender, EventArgs args)
> at System.EventHandler.Invoke(Object sender, EventArgs e)
> at System.Web.UI.Control.OnInit(EventArgs e)
> at System.Web.UI.Control.InitRecursive(Control namingContainer)
> at System.Web.UI.Page.ProcessRequestMain()
> aspnet_wp!ui!f80!19/07/2005-09:47:37:: e ERROR: Exception in
> ShowErrorPage: System.Threading.ThreadAbortException: Thread was being
> aborted.
> etc...
> I'd be very greatful if someone could assist as I am tearing what's
> left of my hair out!

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...
>