Stored procedure error: int is incompatible with uniqueidentifier

Question

Stored procedure error: int is incompatible with uniqueidentifier

I am trying to fix a login issue on a client's site. My company didn't build the site, but we just make updates when the client needs them. The error that I am getting when I try the login in the browser is:

"Operand type clash: int is incompatible with uniqueidentifier"

The stack trace and pieces of code are below. I hope what I have included below is thorough enough because I am having a hard time figuring out what I'm missing here. I am not a .NET developer, so this is not stuff I work with often.

This is the stack trace:

[SqlException (0x80131904): Operand type clash: int is incompatible with uniqueidentifier]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1960506
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4890731
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2412
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +293
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteReader() +89
   TMSTrade.tmsCommon.Authenticate(String ps_login, String ps_password) +187
   TMSTrade._Default.Login1_Authenticate(Object sender, AuthenticateEventArgs e) +215
   System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +108
   System.Web.UI.WebControls.Login.AttemptLogin() +115
   System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.ImageButton.OnCommand(CommandEventArgs e) +111
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +176
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

This is the tmsCommon.Authenticate code:

Public Function Authenticate(ps_login As String, ps_password As String) As Object
    Dim sqlConnection As SqlConnection = New SqlConnection(Conversions.ToString(RuntimeHelpers.GetObjectValue(Me.GetConnString())))
    sqlConnection.Open()
    Dim sqlCommand As SqlCommand = New SqlCommand("sp_Authenticate", sqlConnection)
    Dim num As Integer = 0
    sqlCommand.CommandType = CommandType.StoredProcedure
    sqlCommand.Parameters.Add("@username", SqlDbType.VarChar, 255).Value = ps_login
    sqlCommand.Parameters.Add("@password", SqlDbType.VarChar, 255).Value = ps_password
    Dim sqlDataReader As SqlDataReader = sqlCommand.ExecuteReader()
    If sqlDataReader.Read() Then
        num = Conversions.ToInteger(RuntimeHelpers.GetObjectValue(sqlDataReader("auth")))
    End If
    sqlConnection.Close()
    Return num
End Function

This is the stored procedure sp_authenticate that is called from tmsCommon.Authenticate:

USE [tmstradedb]
GO
/****** Object:  StoredProcedure [dbo].[sp_authenticate]    Script Date: 7/27/2018 12:31:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_authenticate 'neal','blue96'
ALTER PROCEDURE [dbo].[sp_authenticate]
    ( @UserName             varchar(255),
    @password       varchar(255))
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    declare @result int
    set @result=0

    declare @appdate datetime
    declare @guestmember int
    Declare @isapproved int
    declare @islockedout int
    EXEC @UserId = dbo.spGetUserIDByName @UserName



        SELECT @result=1,@guestmember=guestmember,@appdate=approveddate,@islockedout=islockedout,@isapproved=isapproved 
        FROM    dbo.aspnet_Membership m 
        WHERE  m.password=@password and m.userid=@UserId and deleted=0 and archived=0 


        IF (@result >0) -- Username  found

        begin



            UPDATE   dbo.aspnet_Users SET LastActivityDate = getdate() WHERE    @UserId = UserId
            if (@isapproved=0) and (datediff(dd,@appdate,getdate())>31) 
            begin
                select @result=2 
            end
            if (@isapproved=2)
            begin
                select @result=3
            end
            if (@islockedout=1)
            begin
                select @result=4
            end



    end

    select @result as Auth
END

And this is the stored procedure spGetUserIDByName, which is called within sp_authenticate:

USE [tmstradedb]
GO
/****** Object:  StoredProcedure [dbo].[spGetUserIDByName]    Script Date: 7/27/2018 1:03:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select dbo.[spGetUserIDByName]('neal')
ALTER PROCEDURE [dbo].[spGetUserIDByName]
    @UserName             nvarchar(256)
AS
BEGIN
        DECLARE @UserId uniqueidentifier

         -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER('/') = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

END

Table definition of dbo.aspnet_Membership:

ApplicationId   uniqueidentifier
UserId  uniqueidentifier
Password    nvarchar(128)
PasswordFormat  int
PasswordSalt    nvarchar(128)
MobilePIN   nvarchar(16)
Email   nvarchar(256)
LoweredEmail    nvarchar(256)
PasswordQuestion    nvarchar(256)
PasswordAnswer  nvarchar(128)
IsApproved  int
IsLockedOut bit
CreateDate  datetime
LastLoginDate   datetime
LastPasswordChangedDate datetime
LastLockoutDate datetime
FailedPasswordAttemptCount  int
FailedPasswordAttemptWindowStart    datetime
FailedPasswordAnswerAttemptCount    int
FailedPasswordAnswerAttemptWindowStart  datetime
Comment ntext
ApprovedDate    datetime
GuestMember bit 
Quest   varchar(MAX)
passbkup    nvarchar(128)
QuestVisible    bit
Archived    bit

Table definition of dbo.aspnet_Users:

ApplicationId   uniqueidentifier
UserId  uniqueidentifier
UserName    nvarchar(256)
LoweredUserName nvarchar(256)
MobileAlias nvarchar(16)
IsAnonymous bit
LastActivityDate    datetime

sp_authenticate execution in SQL Server Management Studio:

USE [tmstradedb]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[sp_authenticate]
        @UserName = N'123-456', --fake user name
        @password = N'2345' -- fake password

SELECT  'Return Value' = @return_value

GO

Error returned from execution: Msg 206, Level 16, State 2, Procedure dbo.spGetUserIDByName, Line 0 [Batch Start Line 2] Operand type clash: int is incompatible with uniqueidentifier

******UPDATE***** I tried a couple of things based on the feedback that I have gotten. I modified the spGetUserIDByName procedure to the following:

USE [tmstradedb]
GO
/****** Object:  StoredProcedure [dbo].[spGetUserIDByName]    Script Date: 7/27/2018 2:12:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select dbo.[spGetUserIDByName]('neal')
ALTER PROCEDURE [dbo].[spGetUserIDByName]
    @UserName             nvarchar(256),
    @UserId uniqueidentifier OUTPUT
AS
BEGIN
        --DECLARE @UserId uniqueidentifier

         -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER('/') = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

    RETURN;

END

And I made a change to the sp_authenticate procedure as well:

USE [tmstradedb]
GO
/****** Object:  StoredProcedure [dbo].[sp_authenticate]    Script Date: 7/27/2018 2:13:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_authenticate 'neal','blue96'
ALTER PROCEDURE [dbo].[sp_authenticate]
    ( @UserName             varchar(255),
    @password       varchar(255))
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    declare @result int
    set @result=0

    declare @appdate datetime
    declare @guestmember int
    Declare @isapproved int
    declare @islockedout int
    --EXEC @UserId = dbo.spGetUserIDByName @UserName
    EXEC dbo.spGetUserIDByName @UserName



        SELECT @result=1,@guestmember=guestmember,@appdate=approveddate,@islockedout=islockedout,@isapproved=isapproved 
        FROM    dbo.aspnet_Membership m 
        WHERE  m.password=@password and m.userid=@UserId and deleted=0 and archived=0 


        IF (@result >0) -- Username  found

        begin



            UPDATE   dbo.aspnet_Users SET LastActivityDate = getdate() WHERE    @UserId = UserId
            if (@isapproved=0) and (datediff(dd,@appdate,getdate())>31) 
            begin
                select @result=2 
            end
            if (@isapproved=2)
            begin
                select @result=3
            end
            if (@islockedout=1)
            begin
                select @result=4
            end



    end

    select @result as Auth
END

I know I'm still doing something wrong here because when I execute sp_authenticate like the example above, I now get this error:

Msg 201, Level 16, State 4, Procedure dbo.spGetUserIDByName, Line 0 [Batch Start Line 2] Procedure or function 'spGetUserIDByName' expects parameter '@UserId', which was not supplied.

sql-server

int

uniqueidentifier

asked on Stack Overflow Jul 27, 2018 by

Jennifer C • edited Jul 27, 2018 by

Jennifer C

2 Answers

This is the problem:

EXEC @UserId = dbo.spGetUserIDByName @UserName

In the stored procedure being called, you declare and populate a GUID variable:

 DECLARE @UserId uniqueidentifier

         -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM   ...

But then you never use that variable as the output of the stored procedure.

Adding this at the end of that procedure will probably fix the problem:

RETURN @UserID;

An even more canonical way to fix it would be to make @UserID into an OUTPUT parameter and capture it accordingly in the calling proc.

answered on Stack Overflow Jul 27, 2018 by

Tab Alleman

sp_getuseridbyname returns the "key" via an output parameter. You aren't calling that procedure correctly. You have to "pass" a UID variable as an parameter to access the value that procedure sets.

exec dbo.spGetUserIDByName @UserName, @UserID OUTPUT;

You declared the variables in the authenticate procedure but did not use them. Focus on your code. Stop adding more things, trying things, and confusing yourself.

answered on Stack Overflow Jul 27, 2018 by

SMor

User contributions licensed under CC BY-SA 3.0