December 20, 2006 at 5:18 am
Hi, can anyone tell me how the errors can be fixed on this script??
Errors:
The CREATE DATABASE process is allocating 0.75 MB on disk 'aspstate'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'aspstate_log'.
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'usraspstate' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.
Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'usraspstate' does not exist in the current database.
Granted database access to 'usraspstate'.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.
'usraspstate' added to role 'db_owner'.
(1 row(s) affected)
Type added.
(1 row(s) affected)
Type added.
(1 row(s) affected)
Type added.
(1 row(s) affected)
Type added.
(1 row(s) affected)
Type added.
Server: Msg 2714, Level 16, State 6, Line 7
There is already an object named 'ASPStateTempSessions' in the database.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'CreateTempTables'. The stored procedure will still be created.
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57
The login 'usraspstate' already exists.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.
And here's the script, a tad long....
/*****************************************************************************/
/*This script is called by a Startup Stored Procedure in the Master Database */
/*to enable session state for usraspstate in the aspstate database. */
/*The startup stored procedure is called aspstate_reinstate. */
/*King David 22/06/2004. */
/*****************************************************************************/
/*************************************************************/
/*Drop & destroy the exisiting aspstate database completely. */
/*************************************************************/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'aspstate')
DROP DATABASE [aspstate]
GO
/****************************************************************************/
/*Create the new database, using the default model, therefore no file names */
/*required or collation required, or individual objects to be destroyed. */
/****************************************************************************/
CREATE DATABASE aspstate
GO
/*******************************/
/*Set the database properties. */
/*******************************/
exec sp_dboption N'aspstate', N'autoclose', N'false'
GO
exec sp_dboption N'aspstate', N'bulkcopy', N'false'
GO
exec sp_dboption N'aspstate', N'trunc. log', N'false'
GO
exec sp_dboption N'aspstate', N'torn page detection', N'true'
GO
exec sp_dboption N'aspstate', N'read only', N'false'
GO
exec sp_dboption N'aspstate', N'dbo use', N'false'
GO
exec sp_dboption N'aspstate', N'single', N'false'
GO
exec sp_dboption N'aspstate', N'autoshrink', N'false'
GO
exec sp_dboption N'aspstate', N'ANSI null default', N'false'
GO
exec sp_dboption N'aspstate', N'recursive triggers', N'false'
GO
exec sp_dboption N'aspstate', N'ANSI nulls', N'false'
GO
exec sp_dboption N'aspstate', N'concat null yields null', N'false'
GO
exec sp_dboption N'aspstate', N'cursor close on commit', N'false'
GO
exec sp_dboption N'aspstate', N'default to local cursor', N'false'
GO
exec sp_dboption N'aspstate', N'quoted identifier', N'false'
GO
exec sp_dboption N'aspstate', N'ANSI warnings', N'false'
GO
exec sp_dboption N'aspstate', N'auto create statistics', N'true'
GO
exec sp_dboption N'aspstate', N'auto update statistics', N'true'
GO
if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'aspstate', N'db chaining', N'false'
GO
/*********************************************************/
/*Ensure all activity is carried out in the aspstate db. */
/*********************************************************/
use aspstate
go
/*********************************/
/* Destroy the user usraspstate. */
/*********************************/
exec sp_droplogin 'usraspstate'
go
exec sp_dropuser 'usraspstate'
go
/**************************/
/*Create the usraspstate usr. */
/**************************/
if not exists (select * from master.dbo.syslogins where loginname = N'usraspstate')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)
select @logindb = N'aspstate', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
/************************************/
/*Default to the aspstate database. */
/************************************/
select @logindb = N'aspstate'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'usraspstate', 'usraspstate', @logindb, @loginlang
END
GO
/*************************/
/*Grant database access. */
/*************************/
if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)
EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'
GO
/******************************/
/*Grant the role permissions. */
/******************************/
exec sp_addrolemember N'db_datareader', N'usraspstate'
GO
exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO
/*********************************************/
/* Ensure usraspstate is the database owner. */
/*********************************************/
sp_addrolemember 'db_owner','usraspstate'
go
setuser
GO
/***********************************/
/*Add the user defined data types. */
/***********************************/
EXEC sp_addtype N'tAppName', N'varchar (280)', N'not null'
GO
setuser
GO
setuser
GO
EXEC sp_addtype N'tSessionId', N'char (32)', N'not null'
GO
setuser
GO
setuser
GO
EXEC sp_addtype N'tSessionItemLong', N'image', N'null'
GO
setuser
GO
setuser
GO
EXEC sp_addtype N'tSessionItemShort', N'varbinary (7000)', N'null'
GO
setuser
GO
setuser
GO
EXEC sp_addtype N'tTextPtr', N'varbinary (16)', N'null'
GO
setuser
GO
/*****************************************************/
/* create the tempdb table objects. */
/* creation takes place in the usraspstate database. */
/*****************************************************/
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 IDENTITY PRIMARY KEY,
AppName CHAR(280) NOT NULL,
)
CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)
go
CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETDATE()
DELETE tempdb..aspstateTempSessions
WHERE Expires < @now
RETURN 0
GO
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[DeleteExpiredSessions] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[DropTempTables] TO [usraspstate]
GO
CREATE PROCEDURE ResetData
AS
EXECUTE DropTempTables
EXECUTE CreateTempTables
RETURN 0
GO
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[ResetData] TO [usraspstate]
GO
CREATE PROCEDURE TempGetAppId
@appName tAppName,
@appId INT OUTPUT
AS
SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName
IF @appId IS NULL BEGIN
INSERT tempdb..aspstateTempApplications
(AppName)
VALUES
(@appName)
SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName
END
RETURN 0
GO
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempGetAppId] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempGetStateItem] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempGetStateItemExclusive] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempInsertStateItemLong] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempInsertStateItemShort] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempReleaseStateItemExclusive] TO [usraspstate]
GO
CREATE PROCEDURE TempRemoveStateItem
@id tSessionId,
@lockCookie INT
AS
DELETE tempdb..aspstateTempSessions
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
GO
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempRemoveStateItem] TO [usraspstate]
GO
CREATE PROCEDURE TempResetTimeout
@id tSessionId
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE())
WHERE SessionId = @id
RETURN 0
GO
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempResetTimeout] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempUpdateStateItemLong] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempUpdateStateItemLongNullShort] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempUpdateStateItemShort] TO [usraspstate]
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
/**********************************/
/* Grant usraspstate permissions. */
/**********************************/
GRANT EXECUTE ON [dbo].[TempUpdateStateItemShortNullLong] TO [usraspstate]
GO
/********************************************************/
/* Grant usraspstate permissions on the tempdb objects. */
/********************************************************/
use tempdb
go
if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)
EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'
GO
exec sp_addlogin 'usraspstate','usraspstate'
GO
exec sp_addrolemember N'db_datareader', N'usraspstate'
GO
exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [usraspstate]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [usraspstate]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [public]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [public]
GO
/************************************/
/*Now resize the .MDF & .LDF Files. */
/************************************/
USE master
GO
ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate',SIZE = 20MB)
GO
ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate_log',SIZE = 5MB)
GO
/*******/
/*DONE */
/*******/
December 20, 2006 at 8:54 am
You need to add user 'usraspstate' to the database, or under 'Security', 'Login', find the user 'usraspstate', under the tab 'Database Access' click the database that this user allows to use.
Second error:
There is already an object named 'ASPStateTempSessions' in the database.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'CreateTempTables'. The stored procedure will still be created.
It means inside the procedure 'ASPStateTempSessions', it called an object named 'CreateTempTables' but it could not find this object in the database. The script still created the procedure 'ASPStateTempSessions', but you need to add the object 'CreateTempTables' in the the database or you have to check the procedure 'ASPStateTempSessions' to see if the object 'CreateTempTables' is needed.
December 21, 2006 at 5:35 am
Cheers mate, I've decided to:
1) determine whether I can dispense with the programmatic login/user creation and do it by hand in EM;
2) dispense with the whole temp table idea...just not sure what that's all about (ie necessary or not);
Still have an outstanding syntax issue, this part still yields an "Incorrect syntax near ')'" when run by itself;
if not exists (select * from master.dbo.syslogins where loginname = N'usraspstate')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)
select @logindb = N'aspstate', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
Jaybee.
December 21, 2006 at 8:45 am
Hi Jay,
the part you are trying to run by itself contains BEGIN, but no END. If it isn't just a typo when posting, this is why the syntax error appears.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply