Refactor - Version 6

For the first time I am using the Refactoring feature of the professional version.

During my initial attempts to refactor a test table I continue to get a message stating:

Database Error MSG 139, Level 15 State 1, Cannot assign a default value to a local variable

Any thoughts?

Also, If i change the name of a column in a table using Refactor, is it supposed to change the column name in all dependecies such as stored procedures and functions?

	Quote:
	During my initial attempts to refactor a test table I continue to get a message stating: Database Error MSG 139, Level 15 State 1, Cannot assign a default value to a local variable

Please provide DDL for your test table and describe exactly what you are changing and where exactly the error is reported.

	Quote:
	Also, If i change the name of a column in a table using Refactor, is it supposed to change the column name in all dependecies such as stored procedures and functions?

That's correct. You can see all that in the change preview dialog and choose what to change and what not. By default it will attempt to rename all found references to the column.

My DDL:
CREATE TABLE dbo._bs_refactor
(
a INT,
b INT,
c int
)

-I will highlight the table name: dbo._bs_refactor
-I enter a new name _bs_refactor_1 and leave all other defaults unchanged --> click next
-Step 2 shows a tree diagram where i can select what i want refactored --> click Refactor
-Step 3 shows the error box. Database Error MSG 139, Level 15 State 1, Cannot assign a default value to a local variable

	Quote:
	-Step 3 shows the error box. Database Error MSG 139, Level 15 State 1, Cannot assign a default value to a local variable

Do you know the dependent object this error is displayed for? Do you see any invalid code changes in the preview (step 2)?

I tried to process the code generated and get the same error:

declare @objname varchar(255) = '['+ 'dbo'+'].['+'_bs_refactor'+']'
EXEC sp_rename @objname, '_bs_refactor_1', 'OBJECT'

The error occurs at: declare @objname varchar(255) = '['+ 'dbo'+'].['+'_bs_refactor'+']'

If I change it and run the below it will work:

declare @objname varchar(255)
SET @objname = '['+ 'dbo'+'].['+'_bs_refactor'+']'

Is there a fix for this?

brianshannon wrote:

I tried to process the code generated and get the same error:

declare @objname varchar(255) = '['+ 'dbo'+'].['+'_bs_refactor'+']'
EXEC sp_rename @objname, '_bs_refactor_1', 'OBJECT'

The error occurs at: declare @objname varchar(255) = '['+ 'dbo'+'].['+'_bs_refactor'+']'

If I change it and run the below it will work:

declare @objname varchar(255)
SET @objname = '['+ 'dbo'+'].['+'_bs_refactor'+']'

Is there a fix for this?

If I recall correctly, declaring a variable and assigning value at the same time was introduced in SQL Server 2k8. If you are on Server 2k or 2k5 you get the error 'Cannot assign a default value to a local variable' when executing 'DECLARE @myvariable INT = 0' and similar. Perhaps the refactoring code does not take this into account.

I am running this on 2 k server or is that a bug.

So refactor only works on 2008 server?

I have another post about view dependencies not working. Is that the case there as well.

you will need to change "object rename query" in SQL Assistant options.

Here is what you have now

	Code:
	[code]declare @objname varchar(255) = '['+ :REFACTORING_SCHEMA+'].['+:REFACTORING_OLD_NAME+']' EXEC sp_rename @objname, :REFACTORING_NEW_NAME, 'OBJECT'[/code]

Here is what you need to make it compatible with SQL 2000

	Code:
	DECLARE @objname varchar(255) SET @objname = '['+ :REFACTORING_SCHEMA+'].['+:REFACTORING_OLD_NAME+']' EXEC sp_rename @objname, :REFACTORING_NEW_NAME, 'OBJECT'

To modify the query, open SQL Assistant Options, click Refactoring tab, click SQL Server Refactoring type in the left top corner, expand DB Queries for SQL Server Refactoring group on the left bottom; select [object rename] query, update the code as described above.

Please make similar changes in [column delete] and [column rename] queries, separating variable declarations from value assignments. That should do it.