Call : 381-18-529-373

Archive for October, 2011

Error while executing stored procedure TRUNCATE_TRANSACTION_LOG on OPT (OPTION IMPORTS) database.

When trying to upgrade Everest 5.0.3.5 to version 6 if this error shows :

Log File created as on 10/18/2011 3:47:59 PM

Server selected for Update : WIN-2008
Company selected for Update : OPT (OPTION IMPORTS)

Old Company Version number: 5.0.700
New Company Version number: 6.0.650

One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options
1) Error while executing stored procedure TRUNCATE_TRANSACTION_LOG on OPT (OPTION IMPORTS) database.
2) Failed to execute stored procedure TRUNCATE_TRANSACTION_LOG on OPT (OPTION IMPORTS) database.

Update failed due to errors.
End of log as on 10/18/2011 3:48:03 PM

 

Run this script , then rerun Database Upgrade Wizard :

USE [EVEREST_SYSTEM_DEFAULT]
GO
/****** Object:  StoredProcedure [dbo].[TRUNCATE_TRANSACTION_LOG]    Script Date: 08/19/2011 20:47:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TRUNCATE_TRANSACTION_LOG]( @V_DB_NAME VARCHAR(200),
@V_RECOVERY_MODEL VARCHAR(100) OUT )
AS
BEGIN
SET NOCOUNT ON
/*
DECLARE @FILE_NAME VARCHAR(100),
@V_SQL VARCHAR(100)

--TRUNCATING THE TRANSACTION LOG FILE
SET @V_SQL = 'BACKUP LOG ' + LTRIM(RTRIM(@V_DB_NAME)) + ' WITH NO_LOG'
EXEC(@V_SQL)
--SHRINKING THE TRANSACTION LOG FILE
SELECT @FILE_NAME = (SELECT LTRIM(RTRIM(NAME)) FROM SYSFILES
WHERE LTRIM(RTRIM(SYSFILES.NAME)) LIKE '%LOG' AND
LTRIM(RTRIM(SYSFILES.FILENAME)) LIKE '%.LDF')
SET @V_SQL = 'DBCC SHRINKFILE (' + LTRIM(RTRIM(@FILE_NAME)) + ', 0, TRUNCATEONLY)'
EXEC(@V_SQL)
--GET THE DATABASE RECOEVERY MODEL
/*
'FULL'
'BULK_LOGGED'
'SIMPLE'
*/
SET @V_RECOVERY_MODEL = (SELECT CONVERT(VARCHAR(100), DATABASEPROPERTYEX(@V_DB_NAME, 'RECOVERY')))
--IF THE DATABASE RECOVERY MODEL IS NOT SIMPLE. THEN CHANGE IT FOR THE UPGRADE
IF LTRIM(RTRIM(@V_RECOVERY_MODEL)) <> 'SIMPLE'
BEGIN
SET @V_SQL = 'ALTER DATABASE ' + @V_DB_NAME + ' SET RECOVERY SIMPLE'
EXEC(@V_SQL)
END
*/

SET @V_RECOVERY_MODEL = 'SIMPLE'
SET NOCOUNT OFF
END

 

Read more