Getting the error in rule upgrade from 6.1Sp2 to 7.1.8:SQLServerException: Cannot assign a default value to a local variable.
I am facing problem in upgrading a customer application from PRPC6.1SP2 to Pega7.1ML8, While performing rules upgrade, encountered "SQLServerException: Cannot assign a default value to a local variable".
Below is the stack trace:
[java] INFO: CREATE PROCEDURE RULESSCHEMA.SPPR_DEFRAGMENT_TABLE
[java] Jul 13, 2015 3:55:37 AM com.pega.pegarules.install.SQLLoader writeException
[java] WARNING: Failed to execute SQL in file mssql00590.sql
[java] com.microsoft.sqlserver.jdbc.SQLServerException: Cannot assign a default value to a local variable.
[java] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
[java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
[java] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:662)
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:266)
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
DB is in earlier version than mssql 2008r2. And it does not support declare variable and assign value on the same line.
Below link will help to address this issue
http://www.sql-server-helper.com/error-messages/msg-139.aspx
I gather that you are using SQL Server 2005. SQL Server 2005 is not supported in Pega 7. It was last supported in Pega 6.1SP2. You would need to upgrade your SQL server to 2008 R2 before you upgrade to Pega 7.
Anyhow, the technical reasons for your failure is as follows.
I see that the code of SPPR_DEFRAGMENT_TABLE has the following statement syntax which is ONLY supported starting from SQL Server 2008.
DECLARE @l_actual_tablename varchar(129) = @p_schema_name+ '.'+@p_table_name;
DECLARE @l_temp_tablename varchar(134) = @l_actual_tablename+ '_temp';
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
Starting from SQL Server 2008 alone, DECLARE statements can have local value assignments. Before SQL Server 2008 ( i.e SQL Server 2005 and below ), the local value assignments happened with SET variables.
I’ve rewritten the DDLs of the procedure SPPR_DEFRAGMENT_TABLE as per the below and created it.
DECLARE @l_actual_tablename varchar(129);
SET @l_actual_tablename = @p_schema_name+ '.'+@p_table_name;
DECLARE @l_temp_tablename varchar(134);
SET @l_temp_tablename = @l_actual_tablename+ '_temp';
DECLARE @ErrorMessage NVARCHAR(4000);
SET @ErrorMessage = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT;
SET @ErrorSeverity = ERROR_SEVERITY();
DECLARE @ErrorState INT;
SET @ErrorState = ERROR_STATE();
Anyways, you would not need the above fix as Pega 7 would not support SQL Server 2005.