SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TRY CATCH 2005: SSMS still sees error (2012 does not)


TRY CATCH 2005: SSMS still sees error (2012 does not)

Author
Message
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 2726
Here's an interesting little thing I've found - SQL 2005, when given two errors, CATCHes one and lets the other pass through, while SQL 2008 and up CATCH one and hide the other.



-- Run this in one window; it needs to take at least 20 seconds or so
BACKUP DATABASE [generic] TO DISK = N'\\YourBackupPathAndFilename' WITH CHECKSUM, BLOCKSIZE = 65536, BUFFERCOUNT = 16, MAXTRANSFERSIZE = 4194304




-- Run this in another window immediately after starting the first.
BEGIN TRY
RESTORE HEADERONLY FROM DISK = '\\YourBackupPathAndFilename'
END TRY
BEGIN CATCH
PRINT 'Ignoring error'
END CATCH



Using SSMS 2012 to connect to SQL 2005 SP4 shows:
Msg 3201, Level 16, State 2, Line 2
Cannot open backup device '\\YourBackupPathAndFilename'. Operating system error 32(The process cannot access the file because it is being used by another process.).
Ignoring error

Useing SSMS 2012 to connect to SQL 2012 RTM shows:
Ignoring error

Thus, the Catch block always fires, but on the SQL 2005 connection SQL Server throws the error anyway, while on the SQL 2012 connection SQL Server does not throw the error.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 2726
Further information to date - on both 2005 and 2012 servers, the usual divide by zero example is caught without showing any errors at all.

BEGIN TRY
PRINT 1/0
END TRY
BEGIN CATCH
PRINT 'Div by 0'
END CATCH


Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 2726
Attempting to change a variety of SET options has no effect. SQL 2008 acts like SQL 2012.

The RESTORE VERIFYONLY on a file that exists returns two errors; SQL 2005 SP4 catches one and lets the other through. SQL 2008 SP3 and 2012 RTM catch one and hide the other.

Does anyone know a good way to get a single error if a file is locked (without using xp_cmdshell or other syadmin level commands)? As a note, both xp_fileexist, like xp_dirtree, are returning the filename without any errors, even in SQL 2005.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search