Error in MS Stored Procedure

I am current creating a Oracle membership provider in dotnet based on the MS membership provider. One of the stored procedures that need converting is shown below. The variable UserId (highlighted blue) is declare and never assigned to but is used within the update query also highlighted in blue. If this is an oversight then I presume that this part of the stored procedure is never actually executed. Can somebody put an eye over this code to confirm.

Thanks.

ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.CurrentTimeUtc datetime,
@.UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @.UserId uniqueidentifier

IF (@.UpdateLastActivity = 1)
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @.CurrentTimeUtc, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1

UPDATE dbo.aspnet_Users
SET LastActivityDate = @.CurrentTimeUtc
WHERE @.UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1
END

RETURN 0
END

It is a bug in the code. The code is wrong and there are other problems with the logic. You should file a bug at http://connect.microsoft.com.

Error in Derived Column component

I am getting this error,

[Derived Column [192]] Error: Converting an input column from type DT_STR to type DT_WSTR failed. An error occurred while performing the implicit conversion on the input column.

But I don really understand y there is a attempt to type cast at all ?

Please advise ....

thanks in advance

The expression evaluator almost always implicitly converts DT_STR columns to DT_WSTR, because all string functions are implemented for Unicode only. There are only a couple very specific exceptions.

You might try to putting a data conversion transform in your flow and doing an explicit convert from DT_STR to DT_WSTR for that column and see if you get a more helpful error message. Or, redirect that row to an error output, and inspect the data and post it here.

Thanks
Mark

|||

Mark Durley wrote:

You might try to putting a data conversion transform in your flow and doing an explicit convert from DT_STR to DT_WSTR for that column and see if you get a more helpful error message.

I thought of this earlier and when I tried with a Data Conversion Component, I was not able to locate an option as DT_WSTR. Am I missing something here?

thanks for the Help so far...

|||

In the Data Conversion transform UI, in the Data Type drop down, you should see an option:

Unicode string [DT_WSTR]

Mark

|||

In the column marked Data Type in your Derived Column Shape change the value from Unicode [DT_WSTR] to string [DT_STR]

Does this answer your question?

|||Thx for the reply :)