VBBRett
asked on
Need help with a SQL Stored Procedure
I am in need of help, my stored procedure does not execute. It used to work just fine and for some reason it just stopped working. As an fyi, it stops executing right before EXEC sp.createMCSProfile, but everything before that completes it's transaction. So, what do I do? Below is my stored procedure;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[spMembership_CreateUser]
@ApplicationName nvarchar(256),
@Description nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@ProfileTypeId int,
@BirthDay DATETIME,
@FirstName nvarchar(100) = null,
@LastName nvarchar(100) = null,
@Email nvarchar(256),
@IsApproved bit,
@PasswordQuestion nvarchar(256) = null,
@PasswordAnswer nvarchar(128) = null,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int,
@PasswordFormat int,
@Gender int,
@UserId uniqueidentifier = null OUTPUT,
@MCSProfileId uniqueidentifier = null OUTPUT,
@GarageId uniqueidentifier = null OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowsStart datetime
SET @FailedPasswordAnswerAttemptWindowsStart = CONVERT( datetime, '17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.spApplications_CreateApplication @ApplicationName, @Description, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc
SELECT @NewUserId = UserId FROM dbo.Users WHERE @UserName = UserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.spCreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.Memberships
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.Memberships m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND Email = @Email))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.Memberships
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowsStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowsStart )
DECLARE @ProfileDisplayName nvarchar(256) = NULL
DECLARE @Address nvarchar(100) = NULL
DECLARE @City nvarchar(100) = NULL
DECLARE @State nvarchar(100) = NULL
DECLARE @Zip nvarchar(10) = NULL
DECLARE @Region nvarchar(100) = NULL
DECLARE @Country nvarchar(100) = NULL
DECLARE @ProfilePictureUrl varbinary(max) = NULL
DECLARE @ProfileThumbnailUrl varbinary(max) = NULL
DECLARE @IsDefaultPicture bit
DECLARE @MemberSince DATETIME
DECLARE @IsActive bit
DECLARE @IsOnline bit
DECLARE @IsUserAnonymous bit
DECLARE @LastActivityDate DATETIME
SET @ProfileDisplayName = @UserName
SET @MemberSince = @CreateDate
SET @IsDefaultPicture = 1
SET @IsActive = 1
SET @IsOnline = 1
SET @IsUserAnonymous = 0
SET @LastActivityDate = @CreateDate
EXEC dbo.spCreateMCSProfile @UserId, @ProfileTypeId, @ProfileDisplayName, @BirthDay, @FirstName, @LastName,
@Address, @City, @State, @Zip, @Region, @Country, @ProfilePictureUrl, @ProfileThumbnailUrl, @IsDefaultPicture,
@Gender, @MemberSince, @IsActive, @IsOnline, @IsUserAnonymous, @LastActivityDate, @MCSProfileId OUTPUT
DECLARE @GarageDisplayName nvarchar(max)
DECLARE @GarageString nvarchar(100) = 'Garage'
DECLARE @GarageCreatedDate datetime
DECLARE @GarageDisplayimage varbinary(max) = null
DECLARE @GarageDisplayimageName nvarchar(max) = null
DECLARE @GarageDisplayDefaultImage bit
SET @GarageDisplayName = @UserName + ' ' + @GarageString
SET @GarageCreatedDate = @CreateDate
SET @GarageDisplayDefaultImage = 1
EXEC dbo.spCreateGarage @MCSProfileId, @GarageDisplayName, @GarageCreatedDate, @GarageDisplayimage,
@GarageDisplayimageName, @GarageDisplayDefaultImage, @GarageId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
Éric Moreau
any error message would really help us!
Define 'does not execute.'.
Note that there are no EE zones named 'Mind reading'. We suck at that.
Note that there are no EE zones named 'Mind reading'. We suck at that.
ASKER
When I attempted to run the sp.createMCSProfile by itself with dummy data, I get the following error:
Msg 8114, Level 16, State 5, Procedure spCreateMCSProfile, Line 0
Error converting data type nvarchar to datetime.
(1 row(s) affected)
(1 row(s) affected)
Msg 8114, Level 16, State 5, Procedure spCreateMCSProfile, Line 0
Error converting data type nvarchar to datetime.
(1 row(s) affected)
(1 row(s) affected)
that means that one of the field is expecting a date and you try to put a string instead.
it can also be that the date is in a format unrecognized by the server. try to use yyyy-MM-dd
it can also be that the date is in a format unrecognized by the server. try to use yyyy-MM-dd
>>
Msg 8114, Level 16, State 5, Procedure spCreateMCSProfile, Line 0
Error converting data type nvarchar to datetime.
<<
The error occurred in proc spCreateMCSProfile ... thus, we need to see the code for that proc to debug the problem.
My guess is that the BirthDay parameter is out of place. That is, @BirthDay is coming in as the 4th param, but birthday should actually be the 3rd or 5th param.
Msg 8114, Level 16, State 5, Procedure spCreateMCSProfile, Line 0
Error converting data type nvarchar to datetime.
<<
The error occurred in proc spCreateMCSProfile ... thus, we need to see the code for that proc to debug the problem.
My guess is that the BirthDay parameter is out of place. That is, @BirthDay is coming in as the 4th param, but birthday should actually be the 3rd or 5th param.
ASKER
Here is the spCreateMCSProdile stored proc
ALTER PROCEDURE [dbo].[spCreateMCSProfile]
@UserId uniqueidentifier,
@ProfileTypeId int,
@ProfileDisplayName nvarchar(256) = NULL,
@BirthDay DATETIME,
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Address nvarchar(100) = NULL,
@City nvarchar(100) = NULL,
@State nvarchar(100) = NULL,
@Zip nvarchar(10) = NULL,
@Region nvarchar(100) = NULL,
@Country nvarchar(100) = NULL,
@ProfilePictureId nvarchar(50) = NULL,
@ProfileThumbnailPictureId nvarchar(50) = NULL,
@IsDefaultPicture bit,
@BannerPictureId nvarchar(50) = NULL,
@IsDefaultBannerPicture bit,
@Gender int,
@MemberSince DATETIME,
@IsActive bit,
@IsOnline bit,
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@MCSProfileId uniqueidentifier OUTPUT
AS
BEGIN
IF( @MCSProfileId IS NULL )
SELECT @MCSProfileId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT MCSProfileId FROM dbo.MCSProfile
WHERE @MCSProfileId = MCSProfileId ) )
RETURN -1
END
INSERT dbo.MCSProfile(UserId, MCSProfileId, ProfileTypeId, ProfileDisplayName, Birthday, FirstName, LastName, Address, City, State, Zip, Region, Country, ProfilePictureId, ProfileThumbnailPictureId, IsDefaultPicture, BannerPictureId, IsDefaultBannerPicture, Gender, MemberSince, IsActive, IsOnline, IsUserAnonymous, LastActivityDate)
VALUES (@UserId, @MCSProfileId, @ProfileTypeId, @ProfileDisplayName, @BirthDay, @FirstName, @LastName, @Address, @City, @State, @Zip, @Region, @Country, @ProfilePictureId, @ProfileThumbnailPictureId, @IsDefaultPicture, @BannerPictureId, @IsDefaultBannerPicture, @Gender, @MemberSince, @IsActive, @IsOnline, @IsUserAnonymous, @LastActivityDate)
RETURN 0
ENDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.