OpenLabyrinth 2.6 full release

[Rachel]

Dear OLers - I am happy to announce the release of OL 2.6 - the
package has a full and delta code set however given that various
implementers are running different versions I am only including the
master SQL build files and I will ask those upgrading to do a compare
with their own version. If someone wants to provide a build script for
any of the various previous versions then please share through this
forum - all the best, Rachel

[Ellen Meiselman]

Hi, I'm sorry - I am very new to SQL server, (more used to oracle and
mysql). I attempted to create a build script using SQLDBDiff V3.0 on a
test version of the database. I can log in to the upgraded application
but clicking on any of the existing labyrinths results in a blank
page.

SQLDBDiff creates a script based on looking at the 2 databases - then
I commented out anything that seemed inappropriate or unnecessary. I
may have introduced problems in this step.

There were some errors when i ran the upgrade script on the 2.5.1
database, but I am not able to troubleshoot this very well. I'll list
my upgrade script first, then the log - perhaps some kind soul will
help me move past this.

I'm just trying to upgrade plain vanilla 2.5.1 to 2.6.1 - I made no
custom changes to the database since it was set up.

Thanks,

Ellen Meiselman

BEGIN UPGRADE SCRIPT
____________________


------------------------------------------------------------
-- This script is generated by SQLDBDiff V3.0
-- http://www.sqldbtools.com
-- 8/18/2010 1:01:41 PM
-- Note :
-- Run your script againt test environement, before any deployement
in production
------------------------------------------------------------

use [openlabyrinth2]
go
--
---$ Drop Index/PK: PK_USERSESSION, Table : dbo.USERSESSION
--if object_id(N'dbo.PK_USERSESSION') is not null
--begin
-- print 'Drop constraint PK_USERSESSION'
-- alter table dbo.USERSESSION
-- drop constraint PK_USERSESSION
--end
--go

-- Drop DATABASE_ROLE : db_owner
--exec sp_droprolemember 'db_owner', 'UHQMSTS1\IUSR_UHQMSTS1'
--go
--exec sp_droprole 'db_owner'
--go

-- Create DATABASE_ROLE : db_owner
--exec sp_addrole @rolename = 'db_owner', @ownername = 'dbo'
--go

--exec sp_addrolemember 'db_owner', 'dbo'
--go



---$ Alter table dbo.MAPNODE
print 'Alter table dbo.MAPNODE alter column OldMNodeID'
alter table dbo.MAPNODE
alter column OldMNodeID varchar(50) null
go


---$ Alter table dbo.QUESTION
print 'Alter table dbo.QUESTION alter column QuestionStem'
alter table dbo.QUESTION
alter column QuestionStem varchar(500) not null
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Feedback')
begin
print 'Add column : dbo.QUESTION.Feedback'
alter table dbo.QUESTION
add Feedback varchar(1000) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'ShowAnswer')
begin
print 'Add column : dbo.QUESTION.ShowAnswer'
alter table dbo.QUESTION
add ShowAnswer char(1) null default ('n')
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'ScoreCounter')
begin
print 'Add column : dbo.QUESTION.ScoreCounter'
alter table dbo.QUESTION
add ScoreCounter int null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'NumTries')
begin
print 'Add column : dbo.QUESTION.NumTries'
alter table dbo.QUESTION
add NumTries int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp1t')
begin
print 'Add column : dbo.QUESTION.Resp1t'
alter table dbo.QUESTION
add Resp1t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp1y')
begin
print 'Add column : dbo.QUESTION.Resp1y'
alter table dbo.QUESTION
add Resp1y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp1s')
begin
print 'Add column : dbo.QUESTION.Resp1s'
alter table dbo.QUESTION
add Resp1s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp1f')
begin
print 'Add column : dbo.QUESTION.Resp1f'
alter table dbo.QUESTION
add Resp1f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp2t')
begin
print 'Add column : dbo.QUESTION.Resp2t'
alter table dbo.QUESTION
add Resp2t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp2y')
begin
print 'Add column : dbo.QUESTION.Resp2y'
alter table dbo.QUESTION
add Resp2y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp2s')
begin
print 'Add column : dbo.QUESTION.Resp2s'
alter table dbo.QUESTION
add Resp2s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp2f')
begin
print 'Add column : dbo.QUESTION.Resp2f'
alter table dbo.QUESTION
add Resp2f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp3t')
begin
print 'Add column : dbo.QUESTION.Resp3t'
alter table dbo.QUESTION
add Resp3t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp3y')
begin
print 'Add column : dbo.QUESTION.Resp3y'
alter table dbo.QUESTION
add Resp3y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp3s')
begin
print 'Add column : dbo.QUESTION.Resp3s'
alter table dbo.QUESTION
add Resp3s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp3f')
begin
print 'Add column : dbo.QUESTION.Resp3f'
alter table dbo.QUESTION
add Resp3f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp4t')
begin
print 'Add column : dbo.QUESTION.Resp4t'
alter table dbo.QUESTION
add Resp4t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp4y')
begin
print 'Add column : dbo.QUESTION.Resp4y'
alter table dbo.QUESTION
add Resp4y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp4s')
begin
print 'Add column : dbo.QUESTION.Resp4s'
alter table dbo.QUESTION
add Resp4s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp4f')
begin
print 'Add column : dbo.QUESTION.Resp4f'
alter table dbo.QUESTION
add Resp4f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp5t')
begin
print 'Add column : dbo.QUESTION.Resp5t'
alter table dbo.QUESTION
add Resp5t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp5y')
begin
print 'Add column : dbo.QUESTION.Resp5y'
alter table dbo.QUESTION
add Resp5y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp5s')
begin
print 'Add column : dbo.QUESTION.Resp5s'
alter table dbo.QUESTION
add Resp5s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp5f')
begin
print 'Add column : dbo.QUESTION.Resp5f'
alter table dbo.QUESTION
add Resp5f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp6t')
begin
print 'Add column : dbo.QUESTION.Resp6t'
alter table dbo.QUESTION
add Resp6t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp6y')
begin
print 'Add column : dbo.QUESTION.Resp6y'
alter table dbo.QUESTION
add Resp6y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp6s')
begin
print 'Add column : dbo.QUESTION.Resp6s'
alter table dbo.QUESTION
add Resp6s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp6f')
begin
print 'Add column : dbo.QUESTION.Resp6f'
alter table dbo.QUESTION
add Resp6f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp7t')
begin
print 'Add column : dbo.QUESTION.Resp7t'
alter table dbo.QUESTION
add Resp7t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp7y')
begin
print 'Add column : dbo.QUESTION.Resp7y'
alter table dbo.QUESTION
add Resp7y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp7s')
begin
print 'Add column : dbo.QUESTION.Resp7s'
alter table dbo.QUESTION
add Resp7s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp7f')
begin
print 'Add column : dbo.QUESTION.Resp7f'
alter table dbo.QUESTION
add Resp7f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp8t')
begin
print 'Add column : dbo.QUESTION.Resp8t'
alter table dbo.QUESTION
add Resp8t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp8y')
begin
print 'Add column : dbo.QUESTION.Resp8y'
alter table dbo.QUESTION
add Resp8y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp8s')
begin
print 'Add column : dbo.QUESTION.Resp8s'
alter table dbo.QUESTION
add Resp8s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp8f')
begin
print 'Add column : dbo.QUESTION.Resp8f'
alter table dbo.QUESTION
add Resp8f text null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp9t')
begin
print 'Add column : dbo.QUESTION.Resp9t'
alter table dbo.QUESTION
add Resp9t varchar(250) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp9y')
begin
print 'Add column : dbo.QUESTION.Resp9y'
alter table dbo.QUESTION
add Resp9y varchar(1) null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp9s')
begin
print 'Add column : dbo.QUESTION.Resp9s'
alter table dbo.QUESTION
add Resp9s int null default (0)
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTION') and name = 'Resp9f')
begin
print 'Add column : dbo.QUESTION.Resp9f'
alter table dbo.QUESTION
add Resp9f text null
end
go


---$ Alter table dbo.QUESTIONRESPONSE
if not exists(select * from sys.columns where object_id =
object_id(N'dbo.QUESTIONRESPONSE') and name = 'QRID')
begin
print 'Add column : dbo.QUESTIONRESPONSE.QRID'
alter table dbo.QUESTIONRESPONSE
add QRID int identity(1,1) not null constraint
QRID_Default_ForNotNullCol default (0)

-- drop generated default constraint
if object_id(N'QRID_Default_ForNotNullCol') is not null
alter table dbo.QUESTIONRESPONSE
drop constraint QRID_Default_ForNotNullCol
end
go


---$ Alter table dbo.REMOTESERVICE
if not exists(select * from sys.columns where object_id =
object_id(N'dbo.REMOTESERVICE') and name = 'ServiceType')
begin
print 'Add column : dbo.REMOTESERVICE.ServiceType'
alter table dbo.REMOTESERVICE
add ServiceType char(1) not null default ('s')
end
go


---$ Alter table dbo.USERSESSIONTRACE
if not exists(select * from sys.columns where object_id =
object_id(N'dbo.USERSESSIONTRACE') and name = 'BookmarkMade')
begin
print 'Add column : dbo.USERSESSIONTRACE.BookmarkMade'
alter table dbo.USERSESSIONTRACE
add BookmarkMade int null
end
go

if not exists(select * from sys.columns where object_id =
object_id(N'dbo.USERSESSIONTRACE') and name = 'BookmarkUsed')
begin
print 'Add column : dbo.USERSESSIONTRACE.BookmarkUsed'
alter table dbo.USERSESSIONTRACE
add BookmarkUsed int null
end
go


---$ Alter table dbo.VPDELEMENT
print 'Alter table dbo.VPDELEMENT alter column ElementValue'
alter table dbo.VPDELEMENT
alter column ElementValue ntext null
go


---$ Create table dbo.BOOKMARK
if object_id(N'dbo.BOOKMARK') is null
begin
print 'Create table dbo.BOOKMARK'
create table dbo.BOOKMARK
(
BookmarkID int identity(1,1) not null,
SessionID varchar(50) not null,
[TimeStamp] varchar(50) not null,
MUID int not null,
MNodeID int not null
)
end
go


---$ Create table dbo.MAPRATING
if object_id(N'dbo.MAPRATING') is null
begin
print 'Create table dbo.MAPRATING'
create table dbo.MAPRATING
(
RatingID int identity(1,1) not null,
RatingComment ntext null,
Rating varchar(20) not null default ('unrated'),
RatingUser varchar(50) not null,
RatingWhen varchar(80) not null,
MapID int not null
)
end
go


---$ Create table dbo.SETPARAMTRACE
if object_id(N'dbo.SETPARAMTRACE') is null
begin
print 'Create table dbo.SETPARAMTRACE'
create table dbo.SETPARAMTRACE
(
LogID int identity(1,1) not null,
MCountersOld varchar(200) null,
MCountersNew varchar(200) null,
MUID varchar(200) null,
[TimeStamp] varchar(200) null,
SessionID varchar(200) null
)
end
go


---$ Alter View dbo.SESSIONCOUNT
if object_id(N'dbo.SESSIONCOUNT') is null
begin
print 'Create View : dbo.SESSIONCOUNT'
exec('create view dbo.SESSIONCOUNT as select null as Col1')
end
go

print 'Alter view : dbo.SESSIONCOUNT'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter view [dbo].[SESSIONCOUNT]
AS
SELECT DISTINCT dbo.USERSESSION.SessionID,dbo.USERSESSION.MapID,
dbo.USERSESSION.UserID,dbo.USERSESSION.StartTime,
(SELECT COUNT(*) AS cnum
FROM dbo.USERSESSIONTRACE
WHERE (dbo.USERSESSION.SessionID = sessionid)) AS cnum
FROM dbo.USERSESSION INNER JOIN
dbo.USERSESSIONTRACE AS USERSESSIONTRACE_1
ON dbo.USERSESSION.SessionID = USERSESSIONTRACE_1.sessionid

go

---$ Create Index/PK: PK_QUESTIONRESPONSE, Table :
dbo.QUESTIONRESPONSE
if object_id(N'dbo.PK_QUESTIONRESPONSE') is null
begin
print 'Add PK constraint : PK_QUESTIONRESPONSE'
alter table dbo.QUESTIONRESPONSE
add constraint PK_QUESTIONRESPONSE primary key clustered(QRID)
end
go

---$ Create Index/PK: PK_USERSESSION_1, Table : dbo.USERSESSION
if object_id(N'dbo.PK_USERSESSION_1') is null
begin
print 'Add PK constraint : PK_USERSESSION_1'
alter table dbo.USERSESSION
add constraint PK_USERSESSION_1 primary key
clustered(SessionID)
end
go

---$ Create Index/PK: PK_BOOKMARKS, Table : dbo.BOOKMARK
if object_id(N'dbo.PK_BOOKMARKS') is null
begin
print 'Add PK constraint : PK_BOOKMARKS'
alter table dbo.BOOKMARK
add constraint PK_BOOKMARKS primary key clustered(BookmarkID)
end
go

---$ Create Index/PK: PK_MAPRATING, Table : dbo.MAPRATING
if object_id(N'dbo.PK_MAPRATING') is null
begin
print 'Add PK constraint : PK_MAPRATING'
alter table dbo.MAPRATING
add constraint PK_MAPRATING primary key clustered(RatingID)
end
go

---$ Create Index/PK: PK_SETPARAMTRACE, Table : dbo.SETPARAMTRACE
if object_id(N'dbo.PK_SETPARAMTRACE') is null
begin
print 'Add PK constraint : PK_SETPARAMTRACE'
alter table dbo.SETPARAMTRACE
add constraint PK_SETPARAMTRACE primary key clustered(LogID)
end
go

---$ Alter Procedure dbo.GETCOUNTERS
if object_id(N'dbo.GETCOUNTERS') is null
begin
print 'Create procedure : dbo.GETCOUNTERS'
execute('create procedure dbo.GETCOUNTERS as return 0')
end
go

print 'Alter procedure : dbo.GETCOUNTERS'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETCOUNTERS]
@mapid varchar (50)
AS
SELECT
CounterID,CounterMapID,CounterLabel,CounterDescription,CounterStartValue,
CounterIconPath,CounterPrefix,CounterSuffix,CounterVisible,CounterOutOf
FROM dbo.COUNTER
WHERE @mapid= dbo.COUNTER.countermapid

go

---$ Alter Procedure dbo.GETLOGIN
if object_id(N'dbo.GETLOGIN') is null
begin
print 'Create procedure : dbo.GETLOGIN'
execute('create procedure dbo.GETLOGIN as return 0')
end
go

print 'Alter procedure : dbo.GETLOGIN'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETLOGIN]
@userid varchar (20),
@password varchar (20)
AS
SELECT *
FROM dbo.USERS
WHERE @userid = dbo.USERS.userid and @password = dbo.USERS.password

go

---$ Alter Procedure dbo.GETMAP
if object_id(N'dbo.GETMAP') is null
begin
print 'Create procedure : dbo.GETMAP'
execute('create procedure dbo.GETMAP as return 0')
end
go

print 'Alter procedure : dbo.GETMAP'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETMAP]
@mapid varchar (50)
AS
SELECT *,(select top 1 mnodeID from mapnode where mNodeType='root'
and mapID=@mapid) as rootNode
FROM dbo.MAP
WHERE @mapid= dbo.MAP.mapid

go

---$ Alter Procedure dbo.GETMAPNODE
if object_id(N'dbo.GETMAPNODE') is null
begin
print 'Create procedure : dbo.GETMAPNODE'
execute('create procedure dbo.GETMAPNODE as return 0')
end
go

print 'Alter procedure : dbo.GETMAPNODE'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETMAPNODE]
@nodeid varchar (50)
AS
SELECT *
FROM dbo.MAPNODE
WHERE @nodeid= dbo.MAPNODE.mnodeid

go

---$ Alter Procedure dbo.GETMAPNODE_MAPNODE
if object_id(N'dbo.GETMAPNODE_MAPNODE') is null
begin
print 'Create procedure : dbo.GETMAPNODE_MAPNODE'
execute('create procedure dbo.GETMAPNODE_MAPNODE as return 0')
end
go

print 'Alter procedure : dbo.GETMAPNODE_MAPNODE'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETMAPNODE_MAPNODE]
@nodeid varchar (50)
AS
SELECT mnodeid2,mnimage,mntext,mnprobability,mnlinkorder
FROM dbo.MAPNODE_MAPNODE
WHERE @nodeid= dbo.MAPNODE_MAPNODE.mnodeid1

go

---$ Alter Procedure dbo.GETMAPNODE_MAPNODE_MB
if object_id(N'dbo.GETMAPNODE_MAPNODE_MB') is null
begin
print 'Create procedure : dbo.GETMAPNODE_MAPNODE_MB'
execute('create procedure dbo.GETMAPNODE_MAPNODE_MB as return 0')
end
go

print 'Alter procedure : dbo.GETMAPNODE_MAPNODE_MB'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETMAPNODE_MAPNODE_MB]
@nodeid varchar (50),
@mysession varchar (50)
AS
SELECT MapID, MNodeID1, MNodeID2, MNImage, MNText
FROM dbo.MAPNODE_MAPNODE
WHERE MNodeID1 LIKE @nodeID AND (MNodeID2 NOT IN
(SELECT MNodeID
FROM USERSESSIONTRACE
WHERE SessionID LIKE @mysession))

go

---$ Alter Procedure dbo.GETMAPS
if object_id(N'dbo.GETMAPS') is null
begin
print 'Create procedure : dbo.GETMAPS'
execute('create procedure dbo.GETMAPS as return 0')
end
go

print 'Alter procedure : dbo.GETMAPS'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETMAPS]
AS
SELECT *
FROM dbo.MAP
where MapEnabled = 'y'
order by mapname

go

---$ Alter Procedure dbo.GETMAPSTARTNODE
if object_id(N'dbo.GETMAPSTARTNODE') is null
begin
print 'Create procedure : dbo.GETMAPSTARTNODE'
execute('create procedure dbo.GETMAPSTARTNODE as return 0')
end
go

print 'Alter procedure : dbo.GETMAPSTARTNODE'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETMAPSTARTNODE]
@mapid varchar (50)
AS
SELECT MNodeID
FROM dbo.MAPNODE
WHERE @mapid= dbo.MAPNODE.mapid
and dbo.MAPNODE.MNodeType = 'root'

go

---$ Alter Procedure dbo.GETNODE
if object_id(N'dbo.GETNODE') is null
begin
print 'Create procedure : dbo.GETNODE'
execute('create procedure dbo.GETNODE as return 0')
end
go

print 'Alter procedure : dbo.GETNODE'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETNODE]
@nodeid varchar (50)
AS
SELECT *
FROM dbo.MAPNODE
WHERE @nodeid= dbo.MAPNODE.mnodeid

go

---$ Alter Procedure dbo.GETNODES
if object_id(N'dbo.GETNODES') is null
begin
print 'Create procedure : dbo.GETNODES'
execute('create procedure dbo.GETNODES as return 0')
end
go

print 'Alter procedure : dbo.GETNODES'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETNODES]
@mapid varchar (50)
AS
SELECT *
FROM dbo.MAPNODE
WHERE @mapid= dbo.MAPNODE.mapid

go

$ Alter Procedure dbo.TRACE_SESSION
if object_id(N'dbo.TRACE_SESSION') is null
begin
print 'Create procedure : dbo.TRACE_SESSION'
execute('create procedure dbo.TRACE_SESSION as return 0')
end
go

print 'Alter procedure : dbo.TRACE_SESSION'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[TRACE_SESSION]
@mysession varchar(50),
@mapid varchar(50)
AS
SELECT TOP 100 PERCENT dbo.USERSESSIONTRACE.sessionid,
dbo.USERSESSIONTRACE.MCounters, dbo.USERSESSIONTRACE.datestamp,
dbo.USERSESSIONTRACE.MNodeid, dbo.MAPNODE.MNodeID AS Expr1,
dbo.MAPNODE.MNodeTitle,
dbo.MAPNODE.MNodeGreen, dbo.MAPNODE.MNodeOrange, dbo.MAPNODE.MNodeRed
FROM dbo.MAPNODE INNER JOIN
dbo.USERSESSIONTRACE ON dbo.MAPNODE.MNodeID =
dbo.USERSESSIONTRACE.MNodeid AND
dbo.MAPNODE.MNodeID = dbo.USERSESSIONTRACE.MNodeid
WHERE dbo.USERSESSIONTRACE.sessionid = @mysession AND
dbo.USERSESSIONTRACE.mapid=@mapid
ORDER BY dbo.USERSESSIONTRACE.datestamp

go

---$ Alter Procedure dbo.GETCOUNTERVALUES
if object_id(N'dbo.GETCOUNTERVALUES') is null
begin
print 'Create procedure : dbo.GETCOUNTERVALUES'
execute('create procedure dbo.GETCOUNTERVALUES as return 0')
end
go

print 'Alter procedure : dbo.GETCOUNTERVALUES'
go
SET QUOTED_IDENTIFIER ON
go

SET ANSI_NULLS ON
go

alter procedure [dbo].[GETCOUNTERVALUES]
@sessionid varchar (50)
AS

select top 1 mcounters,muid
from dbo.USERSESSIONTRACE
where @sessionid=dbo.USERSESSIONTRACE.sessionID
order by MUID desc

go

-- Drop WINDOWS_USER : [UHQMSTS1\IUSR_UHQMSTS1]
--exec sp_revokedbaccess 'UHQMSTS1\IUSR_UHQMSTS1'
--go



________________________
END UPGRADE SCRIPT


BEGIN LOG
_____________


Alter table dbo.MAPNODE alter column OldMNodeID
Alter table dbo.QUESTION alter column QuestionStem
Add column : dbo.QUESTION.Feedback
Add column : dbo.QUESTION.ShowAnswer
Add column : dbo.QUESTION.ScoreCounter
Add column : dbo.QUESTION.NumTries
Add column : dbo.QUESTION.Resp1t
Add column : dbo.QUESTION.Resp1y
Add column : dbo.QUESTION.Resp1s
Add column : dbo.QUESTION.Resp1f
Add column : dbo.QUESTION.Resp2t
Add column : dbo.QUESTION.Resp2y
Add column : dbo.QUESTION.Resp2s
Add column : dbo.QUESTION.Resp2f
Add column : dbo.QUESTION.Resp3t
Add column : dbo.QUESTION.Resp3y
Add column : dbo.QUESTION.Resp3s
Add column : dbo.QUESTION.Resp3f
Add column : dbo.QUESTION.Resp4t
Add column : dbo.QUESTION.Resp4y
Add column : dbo.QUESTION.Resp4s
Add column : dbo.QUESTION.Resp4f
Add column : dbo.QUESTION.Resp5t
Add column : dbo.QUESTION.Resp5y
Add column : dbo.QUESTION.Resp5s
Add column : dbo.QUESTION.Resp5f
Add column : dbo.QUESTION.Resp6t
Add column : dbo.QUESTION.Resp6y
Add column : dbo.QUESTION.Resp6s
Add column : dbo.QUESTION.Resp6f
Add column : dbo.QUESTION.Resp7t
Add column : dbo.QUESTION.Resp7y
Add column : dbo.QUESTION.Resp7s
Add column : dbo.QUESTION.Resp7f
Add column : dbo.QUESTION.Resp8t
Add column : dbo.QUESTION.Resp8y
Add column : dbo.QUESTION.Resp8s
Add column : dbo.QUESTION.Resp8f
Add column : dbo.QUESTION.Resp9t
Add column : dbo.QUESTION.Resp9y
Add column : dbo.QUESTION.Resp9s
Add column : dbo.QUESTION.Resp9f
Msg 1754, Level 16, State 0, Line 7
Defaults cannot be created on columns with an IDENTITY attribute.
Table 'QUESTIONRESPONSE', column 'QRID'.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint. See previous errors.
Add column : dbo.REMOTESERVICE.ServiceType
Add column : dbo.USERSESSIONTRACE.BookmarkMade
Add column : dbo.USERSESSIONTRACE.BookmarkUsed
Alter table dbo.VPDELEMENT alter column ElementValue
Create table dbo.BOOKMARK
Create table dbo.MAPRATING
Create table dbo.SETPARAMTRACE
Alter view : dbo.SESSIONCOUNT
Msg 2010, Level 16, State 1, Procedure SESSIONCOUNT, Line 4
Cannot perform alter on 'dbo.SESSIONCOUNT' because it is an
incompatible object type.
Add PK constraint : PK_QUESTIONRESPONSE
Msg 1911, Level 16, State 1, Line 6
Column name 'QRID' does not exist in the target table or view.
Msg 1750, Level 16, State 0, Line 6
Could not create constraint. See previous errors.
Add PK constraint : PK_USERSESSION_1
Add PK constraint : PK_BOOKMARKS
Add PK constraint : PK_MAPRATING
Add PK constraint : PK_SETPARAMTRACE
Create procedure : dbo.GETCOUNTERS
Alter procedure : dbo.GETCOUNTERS
Create procedure : dbo.GETLOGIN
Alter procedure : dbo.GETLOGIN
Create procedure : dbo.GETMAP
Alter procedure : dbo.GETMAP
Create procedure : dbo.GETMAPNODE
Alter procedure : dbo.GETMAPNODE
Create procedure : dbo.GETMAPNODE_MAPNODE
Alter procedure : dbo.GETMAPNODE_MAPNODE
Create procedure : dbo.GETMAPNODE_MAPNODE_MB
Alter procedure : dbo.GETMAPNODE_MAPNODE_MB
Create procedure : dbo.GETMAPS
Alter procedure : dbo.GETMAPS
Create procedure : dbo.GETMAPSTARTNODE
Alter procedure : dbo.GETMAPSTARTNODE
Create procedure : dbo.GETNODE
Alter procedure : dbo.GETNODE
Create procedure : dbo.GETNODES
Alter procedure : dbo.GETNODES

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '$ '.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'if'.

Alter procedure : dbo.TRACE_SESSION

Msg 208, Level 16, State 6, Procedure TRACE_SESSION, Line 6
Invalid object name 'dbo.TRACE_SESSION'.
Create procedure : dbo.GETCOUNTERVALUES
Alter procedure : dbo.GETCOUNTERVALUES

________________________
END LOG

[Mike Paget]

At first glance - You might be able to drop the user sessiontrace logic if you can live without your existing trace data.

 

Second, if it is mostly porting over, you may just need to reassign the root node in the cases

 

If SQLDiff wrote the script - it also may be breaking on the TRACE SESSION because of changes to "dbo.MAPNODE.MNodeGreen, dbo.MAPNODE.MNodeOrange, dbo.MAPNODE.MNodeRed"

 

Hope that helps,

 

Mike Paget

 

--
You received this message because you are subscribed to the Google Groups "OpenLabyrinth" group.
To post to this group, send email to openla...@googlegroups.com.
To unsubscribe from this group, send email to openlabyrint...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/openlabyrinth?hl=en.

[Ellen Meiselman]

Thank you - I'll start with these ideas, very helpful.

Ellen

> > --http://www.sqldbtools.com

> >    print 'Add column : dbo.QUESTION.Resp7y'...
>
> read more »