Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TRY CATCH 2005: SSMS still sees error (2012 does not) Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 4:52 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 868, Visits: 2,384
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.
Post #1357743
Posted Wednesday, September 12, 2012 10:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 868, Visits: 2,384
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

Post #1358089
Posted Thursday, September 13, 2012 3:32 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 868, Visits: 2,384
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.
Post #1358910
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse