[girasquid]

I am not entirely sure whether this should be under Databases or ASP.Net. Feel free to move it if it's better suited elsewhere.

I'm working on a project right now where we have a 'front page' application, and a web service at the backend - which is being called via Ajax. I am currently saving the authenticated user's information into the session so that I can track them through the site - but when calls are made to the web service, I need the session to be available to it, too.

I went through setting up session state storing using SQL Server, and found this blog post about modifying the stored procedure to retrieve the same session state for my two applications. However, when I attempt to run the SQL that he provides, I get these errors:

Msg 2715, Level 16, State 3, Procedure TempGetAppID, Line 2
Column, parameter, or variable #1: Cannot find data type tAppName.
Parameter or variable '@appName' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure TempGetAppID, Line 2
Column, parameter, or variable #3: Cannot find data type tAppName.
Parameter or variable '@dupApp' has an invalid data type.

However, I can't figure out what I need to change to make the query run.

Does anyone know what I need to do to create the tAppName data type, or what I need to change the type to?

Thanks,
Girasquid

[PsychoCoder]

Unfortunately without seeing this person's database Im not sure we can offer an answer to this question. It appears this is a User Defined Data Type, which you can create in SQL, but he never says what he is using as a basis for this data type.

This appears to be something he might have created himself, or is that the default data type in the original stored procedure as I don't have Query Analyzer open (or the SQL service started).

Also, this is a database issue so Im moving it to the Databases Forum for you

[girasquid]

I'm not sure - I found another blog post that documents a slightly different method, but still uses the tAppName type.

[PsychoCoder]

Have you looked in your ASPState Database, under Data Types (might be User Defined Data Types) and look for this data type?

[PsychoCoder]

Run the following line on your ASPState Database:

EXECUTE master.dbo.sp_addtype tAppName, 'VARCHAR(280)', 'NOT NULL'

That will give you that user defined data type and should solve your problem. If you are wanting to rebuild your ASPState Database you can run the following script, which will also add the tAppName data type for you as well. You would then have to go back and edit the stored procedure they're telling you to alter in order to share across multiple applications.

NOTE: This script will completely delete your ASPState database and rebuild it. I take no responsibility for any data loss that may occur if you do run it

USE master
GO

/* Drop the database containing our sprocs */
IF DB_ID('ASPState') IS NOT NULL BEGIN DROP DATABASE ASPState
END
GO

/* Drop temporary tables */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN DROP TABLE tempdb..ASPStateTempSessions
END
GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN DROP TABLE tempdb..ASPStateTempApplications
END
GO

/* Drop the startup procedure */
DECLARE @PROCID int
SET @PROCID = OBJECT_ID('ASPState_Startup') 
IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN DROP PROCEDURE ASPState_Startup 
END
GO

/* Drop the obsolete startup enabler */
DECLARE @PROCID int
SET @PROCID = OBJECT_ID('EnableASPStateStartup') 
IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN DROP PROCEDURE EnableASPStateStartup
END
GO

/* Drop the obsolete startup disabler */
DECLARE @PROCID int
SET @PROCID = OBJECT_ID('DisableASPStateStartup') 
IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN DROP PROCEDURE DisableASPStateStartup
END
GO

/* Drop the ASPState_DeleteExpiredSessions_Job */
DECLARE @JobID BINARY(16)  
SELECT @JobID = job_id     
FROM msdb.dbo.sysjobs WHERE (name = N'ASPState_Job_DeleteExpiredSessions')       
IF (@JobID IS NOT NULL)    
BEGIN -- Check if the job is a multi-server job  
    IF (EXISTS (SELECT  * 
              FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) 
    BEGIN -- There is, so abort the script 
        RAISERROR (N'Unable to import job ''ASPState_Job_DeleteExpiredSessions'' since there is already a multi-server job with this name.', 16, 1) 
    END 
    ELSE 
        -- Delete the [local] job 
        EXECUTE msdb.dbo.sp_delete_job @job_name = N'ASPState_Job_DeleteExpiredSessions' 
END

USE master
GO

/* Create and populate the ASPState database */
CREATE DATABASE ASPState
GO

USE ASPstate
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE DropTempTables
AS IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN DROP TABLE tempdb..ASPStateTempSessions
    END

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN DROP TABLE tempdb..ASPStateTempApplications
    END

    RETURN 0
GO
    
CREATE PROCEDURE CreateTempTables
AS /*
     * Note that we cannot create user-defined data types in * tempdb because sp_addtype must be run in the context
     * of the current database, and we cannot switch to 
     * tempdb from a stored procedure.
     */

    CREATE TABLE tempdb..ASPStateTempSessions (
        SessionId           CHAR(32)        NOT NULL PRIMARY KEY,
        Created             DATETIME        NOT NULL DEFAULT GETDATE(),
        Expires             DATETIME        NOT NULL,
        LockDate            DATETIME        NOT NULL,
        LockCookie          INT             NOT NULL,
        Timeout             INT             NOT NULL,
        Locked              BIT             NOT NULL,
        SessionItemShort    VARBINARY(7000) NULL,
        SessionItemLong     IMAGE           NULL,
    ) 

    CREATE TABLE tempdb..ASPStateTempApplications (
        AppId               INT             NOT NULL PRIMARY KEY,
        AppName             CHAR(280)       NOT NULL,
    ) 

    CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)

    RETURN 0
GO      

CREATE PROCEDURE ResetData
AS EXECUTE DropTempTables
    EXECUTE CreateTempTables
    RETURN 0
GO
   
EXECUTE sp_addtype tSessionId, 'CHAR(32)',  'NOT NULL'
GO

EXECUTE sp_addtype tAppName, 'VARCHAR(280)', 'NOT NULL'
GO

EXECUTE sp_addtype tSessionItemShort, 'VARBINARY(7000)'
GO

EXECUTE sp_addtype tSessionItemLong, 'IMAGE'
GO

EXECUTE sp_addtype tTextPtr, 'VARBINARY(16)'
GO

CREATE PROCEDURE GetHashCode
    @input tAppName,
    @hash INT OUTPUT
AS /* 
       This sproc is based on this C# hash function:

        int GetHashCode(string s)
        {
            int     hash = 5381;
            int     len = s.Length;

            for (int i = 0; i < len; i++) {
                int     c = Convert.ToInt32(s[i]);
                hash = ((hash << 5) + hash) ^ c;
            }

            return hash;
        }

        However, SQL 7 doesn't provide a 32-bit integer
        type that allows rollover of bits, we have to
        divide our 32bit integer into the upper and lower
        16 bits to do our calculation.
    */
       
    DECLARE @hi_16bit   INT
    DECLARE @lo_16bit   INT
    DECLARE @hi_t       INT
    DECLARE @lo_t       INT
    DECLARE @len        INT
    DECLARE @i          INT
    DECLARE @c          INT
    DECLARE @carry      INT

    SET @hi_16bit = 0
    SET @lo_16bit = 5381
    
    SET @len = DATALENGTH(@input)
    SET @i = 1
    
    WHILE (@i <= @len)
    BEGIN SET @c = ASCII(SUBSTRING(@input, @i, 1))

        /* Formula:                        
           hash = ((hash << 5) + hash) ^ c */

        /* hash << 5 */
        SET @hi_t = @hi_16bit * 32 /* high 16bits << 5 */
        SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
        
        SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */
        
        SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits */
        SET @carry = @carry / 0x10000 /* >> 16 */
        SET @hi_t = @hi_t + @carry
        SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */

        /* + hash */
        SET @lo_16bit = @lo_16bit + @lo_t
        SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000)
        /* delay clearing the overflow */

        /* ^c */
        SET @lo_16bit = @lo_16bit ^ @c

        /* Now clear the overflow bits */	
        SET @hi_16bit = @hi_16bit & 0xFFFF
        SET @lo_16bit = @lo_16bit & 0xFFFF

        SET @i = @i + 1
    END

    /* Do a sign extension of the hi-16bit if needed */
    IF (@hi_16bit & 0x8000 <> 0)
        SET @hi_16bit = 0xFFFF0000 | @hi_16bit

    /* Merge hi and lo 16bit back together */
    SET @hi_16bit = @hi_16bit * 0x10000 /* << 16 */
    SET @hash = @hi_16bit | @lo_16bit

    RETURN 0
GO

CREATE PROCEDURE TempGetAppID
    @appName    tAppName,
    @appId      INT OUTPUT
AS SET @appName = LOWER(@appName)
    SET @appId = NULL
    
    SELECT @appId = AppId
    FROM tempdb..ASPStateTempApplications WHERE AppName = @appName
    
    IF @appId IS NULL BEGIN BEGIN TRAN        

        SELECT @appId = AppId
        FROM tempdb..ASPStateTempApplications WITH (TABLOCKX)
        WHERE AppName = @appName
        
        IF @appId IS NULL
        BEGIN EXEC GetHashCode @appName, @appId OUTPUT
            
            INSERT tempdb..ASPStateTempApplications
            VALUES
            (@appId, @appName)
            
            IF @@ERROR = 2627 
            BEGIN DECLARE @dupApp tAppName
            
                SELECT @dupApp = RTRIM(AppName)
                FROM tempdb..ASPStateTempApplications WHERE AppId = @appId
                
                RAISERROR('SQL session state fatal error: hash-code collision between applications ''%s'' and ''%s''. Please rename the 1st application to resolve the problem.', 
                            18, 1, @appName, @dupApp)
            END
        END

        COMMIT
    END

    RETURN 0
GO

CREATE PROCEDURE TempGetStateItem
    @id         tSessionId,
    @itemShort  tSessionItemShort OUTPUT,
    @locked     BIT OUTPUT,
    @lockDate   DATETIME OUTPUT,
    @lockCookie INT OUTPUT
AS DECLARE @textptr AS tTextPtr
    DECLARE @length AS INT
    DECLARE @now as DATETIME
    SET @now = GETDATE()

    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now), 
        @locked = Locked,
        @lockDate = LockDate,
        @lockCookie = LockCookie,
        @itemShort = CASE @locked
            WHEN 0 THEN SessionItemShort
            ELSE NULL
            END,
        @textptr = CASE @locked
            WHEN 0 THEN TEXTPTR(SessionItemLong)
            ELSE NULL
            END,
        @length = CASE @locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END
    WHERE SessionId = @id
    IF @length IS NOT NULL BEGIN READTEXT tempdb..ASPStateTempSessions.SessionItemLong @textptr 0 @length
    END

    RETURN 0
GO


CREATE PROCEDURE TempGetStateItemExclusive
    @id         tSessionId,
    @itemShort  tSessionItemShort OUTPUT,
    @locked     BIT OUTPUT,
    @lockDate   DATETIME OUTPUT,
    @lockCookie INT OUTPUT
AS DECLARE @textptr AS tTextPtr
    DECLARE @length AS INT
    DECLARE @now as DATETIME

    SET @now = GETDATE()
    UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now), 
        @lockDate = LockDate = CASE Locked
            WHEN 0 THEN @now
            ELSE LockDate
            END,
        @lockCookie = LockCookie = CASE Locked
            WHEN 0 THEN LockCookie + 1
            ELSE LockCookie
            END,
        @itemShort = CASE Locked
            WHEN 0 THEN SessionItemShort
            ELSE NULL
            END,
        @textptr = CASE Locked
            WHEN 0 THEN TEXTPTR(SessionItemLong)
            ELSE NULL
            END,
        @length = CASE Locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END,
        @locked = Locked,
        Locked = 1
    WHERE SessionId = @id
    IF @length IS NOT NULL BEGIN READTEXT tempdb..ASPStateTempSessions.SessionItemLong @textptr 0 @length
    END

    RETURN 0
GO

CREATE PROCEDURE TempReleaseStateItemExclusive
    @id         tSessionId,
    @lockCookie INT
AS UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO


CREATE PROCEDURE TempInsertStateItemShort
    @id         tSessionId,
    @itemShort  tSessionItemShort,
    @timeout    INT
AS DECLARE @now as DATETIME
    SET @now = GETDATE()

    INSERT tempdb..ASPStateTempSessions 
        (SessionId, 
         SessionItemShort, 
         Timeout, 
         Expires, 
         Locked, 
         LockDate,
         LockCookie) 
    VALUES 
        (@id, 
         @itemShort, 
         @timeout, 
         DATEADD(n, @timeout, @now), 
         0, 
         @now,
         1)

    RETURN 0
GO

CREATE PROCEDURE TempInsertStateItemLong
    @id         tSessionId,
    @itemLong   tSessionItemLong,
    @timeout    INT
AS DECLARE @now as DATETIME
    SET @now = GETDATE()

    INSERT tempdb..ASPStateTempSessions 
        (SessionId, 
         SessionItemLong, 
         Timeout, 
         Expires, 
         Locked, 
         LockDate,
         LockCookie) 
    VALUES 
        (@id, 
         @itemLong, 
         @timeout, 
         DATEADD(n, @timeout, @now), 
         0, 
         @now,
         1)

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemShort
    @id         tSessionId,
    @itemShort  tSessionItemShort,
    @timeout    INT,
    @lockCookie INT
AS UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemShort = @itemShort, 
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemShortNullLong
    @id         tSessionId,
    @itemShort  tSessionItemShort,
    @timeout    INT,
    @lockCookie INT
AS UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemShort = @itemShort, 
        SessionItemLong = NULL, 
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemLong
    @id         tSessionId,
    @itemLong   tSessionItemLong,
    @timeout    INT,
    @lockCookie INT
AS UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemLong = @itemLong,
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempUpdateStateItemLongNullShort
    @id         tSessionId,
    @itemLong   tSessionItemLong,
    @timeout    INT,
    @lockCookie INT
AS UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE()), 
        SessionItemLong = @itemLong, 
        SessionItemShort = NULL,
        Timeout = @timeout,
        Locked = 0
    WHERE SessionId = @id AND LockCookie = @lockCookie

    RETURN 0
GO

CREATE PROCEDURE TempRemoveStateItem
    @id     tSessionId,
    @lockCookie INT
AS DELETE tempdb..ASPStateTempSessions
    WHERE SessionId = @id AND LockCookie = @lockCookie
    RETURN 0
GO
            
CREATE PROCEDURE TempResetTimeout
    @id     tSessionId
AS UPDATE tempdb..ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, GETDATE())
    WHERE SessionId = @id
    RETURN 0
GO
            
CREATE PROCEDURE DeleteExpiredSessions
AS DECLARE @now DATETIME
    SET @now = GETDATE()

    DELETE tempdb..ASPStateTempSessions
    WHERE Expires < @now

    RETURN 0
GO
            
EXECUTE CreateTempTables
GO

/* Create the startup procedure */
USE master
GO

CREATE PROCEDURE ASPState_Startup 
AS EXECUTE ASPState..CreateTempTables

    RETURN 0
GO      

EXECUTE sp_procoption @procname='ASPState_Startup', @optionname='startup', @optionvalue='true'

/* Create the job to delete expired sessions */
BEGIN TRANSACTION            
    DECLARE @JobID BINARY(16)  
    DECLARE @ReturnCode INT    
    SELECT @ReturnCode = 0     

    -- Add job category
    IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
        EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

    -- Add the job
    EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
            @job_id = @JobID OUTPUT, 
            @job_name = N'ASPState_Job_DeleteExpiredSessions', 
            @owner_login_name = NULL, 
            @description = N'Deletes expired sessions from the session state database.', 
            @category_name = N'[Uncategorized (Local)]', 
            @enabled = 1, 
            @notify_level_email = 0, 
            @notify_level_page = 0, 
            @notify_level_netsend = 0, 
            @notify_level_eventlog = 0, 
            @delete_level= 0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    -- Add the job steps
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
            @job_id = @JobID,
            @step_id = 1, 
            @step_name = N'ASPState_JobStep_DeleteExpiredSessions', 
            @command = N'EXECUTE DeleteExpiredSessions', 
            @database_name = N'ASPState', 
            @server = N'', 
            @database_user_name = N'', 
            @subsystem = N'TSQL', 
            @cmdexec_success_code = 0, 
            @flags = 0, 
            @retry_attempts = 0, 
            @retry_interval = 1, 
            @output_file_name = N'', 
            @on_success_step_id = 0, 
            @on_success_action = 1, 
            @on_fail_step_id = 0, 
            @on_fail_action = 2

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

    EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    -- Add the job schedules
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
            @job_id = @JobID, 
            @name = N'ASPState_JobSchedule_DeleteExpiredSessions', 
            @enabled = 1, 
            @freq_type = 4,     
            @active_start_date = 20001016, 
            @active_start_time = 0, 
            @freq_interval = 1, 
            @freq_subday_type = 4, 
            @freq_subday_interval = 1, 
            @freq_relative_interval = 0, 
            @freq_recurrence_factor = 0, 
            @active_end_date = 99991231, 
            @active_end_time = 235959

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    -- Add the Target Servers
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
    COMMIT TRANSACTION          
    GOTO   EndSave              
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 
GO

[baavgai]

girasquid, on 7 Feb, 2008 - 10:52 PM, said:

I'm working on a project right now where we have a 'front page' application, and a web service at the backend - which is being called via Ajax. I am currently saving the authenticated user's information into the session so that I can track them through the site - but when calls are made to the web service, I need the session to be available to it, too.

Authenticate through the webservice. Have that store the state in a session and pass back some token value. Then, when you pass that token to the webservice it will know about it and you wont have a problem.

Your "front page" application is essentially a client to the web service. If you want other clients to share data, you have place it at the web service level.

Hope that makes sense.

[girasquid]

baavgai, on 8 Feb, 2008 - 05:04 AM, said:

girasquid, on 7 Feb, 2008 - 10:52 PM, said:

snip

Authenticate through the webservice. Have that store the state in a session and pass back some token value. Then, when you pass that token to the webservice it will know about it and you wont have a problem.

Your "front page" application is essentially a client to the web service. If you want other clients to share data, you have place it at the web service level.

Hope that makes sense.

Thanks Baavgai! That makes perfect sense - I don't know why I didn't think of it before.

[Trogdor]

Probably because you see the 'frontend app' and 'backend app' as the same application, which, combined with the ajax calls, they are not.