Discussion

2
Replies
173
Views
 ManasRanjan Brahma (brahm)
PEGA

Pegasystems Inc.
brahm Member since 2015 1 post
PEGA
Posted: July 14, 2015
Last activity: April 6, 2018
Posted: 14 Jul 2015 2:54 EDT
Last activity: 6 Apr 2018 19:26 EDT
Closed

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.

System Administration Upgrades
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Discussion, please write a new Discussion.
Posted: 5 years ago
Posted: 14 Jul 2015 6:45 EDT
Jaffer Sathick Mohideen Abdul Kather (jafferSathick)
TCS
Engineering Lead
TCS
GB

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);

Show More

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.


 


 


Show Less