When you upgrade Ax from V3 to V4 or from V3 to V2009, you might not be able to start the new AOS instance. The application event log may show an event similar like this:
Object Server 02: Internal Error occurred executing stored procedure when creating session for the AOS.
Cause:
The SQL remote procedures
CREATESERVERSESSIONS and
CREATEUSERSESSIONS are not created automatically in your upgrade database.
Solution:
1) Copy the stored procedures from a working version (database V4 or V2009, by using fe SQL Server Management Studio)
or
2) Use following scripts.
You can use the following scripts to create the necessary procedures:
USE [YourDatabaseNameGoesHere]
GO
/****** Object: StoredProcedure [dbo].[CREATESERVERSESSIONS] Script Date: 10/05/2008 15:15:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CREATESERVERSESSIONS] @aosId varchar(50), @version int, @instanceName nvarchar(50), @recid bigint, @maxservers int, @status int, @loadbalance int, @workload int, @serverid int OUTPUT as declare @first as varchar(50) declare @max_val as int begin select top 1 @first = SERVERID from SYSSERVERSESSIONS WITH (UPDLOCK, READPAST) where STATUS = 0 and AOSID = @aosId and INSTANCE_NAME = @instanceName if (select count(SERVERID) from SYSSERVERSESSIONS where SERVERID IN (@first)) > 0 begin update SYSSERVERSESSIONS set AOSID = @aosId, VERSION = @version, INSTANCE_NAME = @instanceName, LOGINDATETIME = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), LASTUPDATEDATETIME = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), STATUS = @status, WORKLOAD = @workload where SERVERID IN (@first) and ((select count(SERVERID) from SYSSERVERSESSIONS where STATUS = 1 and LOADBALANCE = 0) < @maxservers) if @@ROWCOUNT = 0 select @serverid = 0 else select @serverid = @first end else begin if (select count(SERVERID) from SYSSERVERSESSIONS WITH (UPDLOCK) where STATUS = 1 and LOADBALANCE = 0) >= @maxservers select @serverid = 0 else begin if (select count(SERVERID) from SYSSERVERSESSIONS) = 0 select @max_val = 1 else select @max_val = max(SERVERID)+1 from SYSSERVERSESSIONS insert into SYSSERVERSESSIONS(SERVERID, AOSID, INSTANCE_NAME, VERSION, LOGINDATETIME, LASTUPDATEDATETIME, STATUS, RECID, LOADBALANCE, WORKLOAD) values(@max_val, @aosId, @instanceName, @version, dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), @status, @recid, @loadbalance, @workload) select @serverid = @max_val end end end
And
USE [YourDatabaseNameGoesHere]
GO
/****** Object: StoredProcedure [dbo].[CREATEUSERSESSIONS] Script Date: 10/05/2008 15:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CREATEUSERSESSIONS] @clientType int, @sessionType int, @serverid int, @versionid int, @userid varchar(5), @lanExt varchar(10), @manExt varchar(10), @sid varchar(124), @recid bigint, @startId int, @maxusers int, @licenseType int, @masterId int, @maxClientId int, @sessionid int OUTPUT, @loginDateTime datetime OUTPUT as declare @return_val as int declare @first as int declare @max_val as int begin select @sessionid = -1 select @max_val = -1 select @loginDateTime = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()) if(not exists(select * from SYSSERVERSESSIONS WITH (NOLOCK) where SERVERID = @serverid AND Status = 1)) begin select @sessionid = -2 return end select @first = min(SESSIONID) from SYSCLIENTSESSIONS WITH (UPDLOCK,READPAST) where STATUS = 0 and SESSIONID > @maxClientId and SESSIONID <> @masterId if (select count(*) from SYSCLIENTSESSIONS where SESSIONID IN (@first)) > 0 begin if (@licenseType = 0) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType where SESSIONID IN (@first) end else if (@licenseType = 1) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType where SESSIONID IN (@first) and ((select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) < @maxusers) end else if (@licenseType = 2) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType where SESSIONID IN (@first) and ( (select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and (USERID = @userid) and ((STATUS = 1) or (STATUS = 2))) > 0 or (select count(distinct USERID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) < @maxusers ) end if @@ROWCOUNT = 0 select @sessionid = 0 else select @sessionid = @first end else begin if (@licenseType = 1) begin if (select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) >= @maxusers select @sessionid = 0 end else if (@licenseType = 2) begin if ( ((select count(distinct USERID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) >= @maxusers) and ((select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and (USERID = @userid) and ((STATUS = 1) or (STATUS = 2))) = 0) ) select @sessionid = 0 end if (@sessionid = -1) or (@licenseType = 0) begin if (select count(SESSIONID) from SYSCLIENTSESSIONS WITH (UPDLOCK) where STATUS = 0 or STATUS = 1 or STATUS = 2 or STATUS = 3) = 0 select @max_val = @startId else select @max_val = max(SESSIONID)+1 from SYSCLIENTSESSIONS WITH (UPDLOCK) if (@max_val > 65535) select @sessionid = -3 else begin insert into SYSCLIENTSESSIONS(SESSIONID, SERVERID, VERSION, LOGINDATETIME, USERID, SID, USERLANGUAGE, HELPLANGUAGE, CLIENTTYPE, SESSIONTYPE, RECID, CLIENTCOMPUTER, STATUS) values(@max_val, @serverid, @versionid, @loginDateTime, @userid, @sid, @lanExt, @manExt, @clientType, @sessionType, @recid, '', 1) if @@ROWCOUNT = 0 select @sessionid = -1 else select @sessionid = @max_val end end end end
Don't forget to replace the dummy databasename at the start of the procedure (YourDatabaseNameGoesHere).
Good luck,
Willy.
