Syntax errors sp_refreshview cannot be detected programmatically - by Erland Sommarskog

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


5
1
Sign in
to vote
ID 553802 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/24/2010 1:20:44 PM
Access Restriction Public

Description

If you run sp_refreshview (and supposedly sp_refreshsqlmodule), and there is a syntax error in the code, due to the view being created in an earilier compatibility mode of SQL Server, and the call to sp_refreshview is in TRY-CATCH, the error message is suppresed and the CATCH handler is not invoked.

If you instead try old-fashioned error handling by checking return value and @@error, you find that neither this works. The return value is 0. (And the reason for that is really shameful, sp_refreshsqlmodule_internal include this code:

	if @@error <> 0
	begin
		COMMIT TRANSACTION
		raiserror(15165,-1,-1,@name)
		return @@error
)

This issue does not exist in SQL 2005.

The issue does not occur with binding errors, for instance when a column has been dropped from a table.
Sign in to post a comment.
Posted by Umachandar [MSFT] on 2/8/2011 at 10:58 PM
FYI - This has been fixed now in SQL Server "Denali".

--
Umachandar, SQL Programmability Team
Posted by Umachandar [MSFT] on 7/13/2010 at 5:01 PM
Hi Erland,
We have fixed the issues in the system SP for the next major version of SQL Server. The return code should now be 1 in case of failure. We also modified the error handling for the internal SP so that you can catch the syntax error if you have TRY...CATCH block.

--
Umachandar, SQL Programmability Team
Posted by Umachandar [MSFT] on 7/13/2010 at 5:00 PM
Hi Erland,
We have fixed the issues in the system SP for the next major version of SQL Server. The return code should now be 1 in case of failure. We also modified the error handling for the internal SP so that you can catch the syntax error if you have TRY...CATCH block.

--
Umachandar, SQL Programmability Team
Posted by Umachandar [MSFT] on 7/13/2010 at 4:58 PM
Hi Erland,
We have fixed the issues in the system SP for the next major version of SQL Server. The return code should now be 1 in case of failure. We also modified the error handling for the internal SP so that you can catch the syntax error if you have TRY...CATCH block.

--
Umachandar, SQL Programmability Team
Posted by Umachandar [MSFT] on 5/4/2010 at 7:49 PM
Hi Erland,
Thanks for reporting the issue. We will investigate it and figure out how to correct this issue in SQL Server 2008 / next major version of SQL Server.

--
Umachandar, SQL Programmability Team