September 11, 2012 at 4:52 pm
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.
September 12, 2012 at 10:00 am
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
September 13, 2012 at 3:32 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply