[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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

Open in new window

0
VBBRett
Asked:
VBBRett
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
any error message would really help us!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'does not execute.'.

Note that there are no EE zones named 'Mind reading'.  We suck at that.
0
 
VBBRettAuthor Commented:
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)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Éric MoreauSenior .Net ConsultantCommented:
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
0
 
Scott PletcherSenior DBACommented:
>>
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.
0
 
VBBRettAuthor Commented:
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
END

Open in new window

0
 
Scott PletcherSenior DBACommented:
Yep, the parameters in the proc and the params being passed into proc spCreateMCSProfile don't line up quite right.  You need to review the order of params being passed into that proc when it's executed.

One tip off that it's likely a param issue is the "line 0" in the error message.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now