Trapping SQL Server errors 1

[huggyboy]

This is a puzzling one.

I am writing a sql server backup routine in vb.net sending an sql command of the form

USE fred;
BACKUP DATABASE fred TO DISK = 'c:\fred.bak'
WITH FORMAT,
MEDIANAME = 'Z_SQLServerBackups',
NAME = 'Full Backup of fred on 19 Jun 2009 at 09:42:51'

and it generally works ok - i've got a try catch clause around the execute command and if the database doesnt exist (for instance) the err.description gives

Could not locate entry in sysdatabases for database 'fred'. No entry found with that name. Make sure that the name is entered correctly.

However if i try to dump the database to another server doing it manually on server management studio fails giving the message

Msg 3201, Level 16, State 1, Line 2
Cannot open backup device '\\JBA01\DBBackup\PO19jun2009v2.Bak'. Operating system error 1326(Logon failure: unknown user name or bad password.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

but running the vb.net code the try catch doesnt detect it as a failure - is there some kind of exception i'm not trapping? Code included below

        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim MyCmd = New ADODB.Command
        '  Dim cmd As New ADODB.Command
        Try
            ' make the connection
            cn.Open(strConn)
            ' set the command
            MyCmd.CommandText = strSQL
            MyCmd.CommandType = ADODB.CommandTypeEnum.adCmdText
            MyCmd.ActiveConnection = cn
            ' set the timeout
            If intTimeoutPeriod <> -1 Then
                cn.CommandTimeout = intTimeoutPeriod
            End If
            ' run the command to dump the database
            MyCmd.Execute()
        Catch
            ' output a less than cheery message
            strerror = "!!! Database dumped failed - source " & strDatabaseName & _
                                  " dumping to  " & strFolderName & strwork & " - error " & Err.Description & " !!!" & vbLf
            Console.Write(strerror)
            funcAddToEmailMessage(strerror, constFailureMessage)
            Exit Sub
        End Try

 

[djj55]

Just a comment that you might what to have USE master instead of USE Fred.



djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[huggyboy]

I changed it to master - didnt seem to make any difference.
Thanks for the comment.

 

[pmegan]

What version of .Net are you using?

I'd use ado.net's SQL Server objects instead of ADODB.This way you can take advantage of the managed classes and the exceptions they throw. Those old libraries are pretty limited in comparison.

Something like this (untested):

		Dim cn As New SqlClient.SqlConnection(strConn)
		Dim cmd As New SqlClient.SqlCommand(strSql, cn)
		Const strERROR As String = "Message: {0}" & vbCrLf & vbCrLf & "{2}"

		Try
			' set the command type
			cmd.CommandType = CommandType.Text

			' open the connection            
			cn.Open()

			' set the timeout            
			If intTimeoutPeriod <> -1 Then
				cn.CommandTimeout = intTimeoutPeriod
			End If

			' run the command to dump the database            
			cmd.ExecuteNonQuery()

		Catch exSql As SqlClient.SqlException
			Dim strErr As String = [String].Format(strERROR, exSql.Message, exSql.ToString)
			funcAddToEmailMessage(strerror, constFailureMessage)
		Catch ex As Exception
			Dim strErr As String = [String].Format(strERROR, ex.Message, ex.ToString)
			funcAddToEmailMessage(strerror, constFailureMessage)
		Finally
			If cn.State = ConnectionState.Open Then
				cn.Close()
			End If
		End Try

 

[huggyboy]

Thanks for the reply - I will try it out and post back

looks like it will work.

 

[huggyboy]

Excellent - worked like a charm

Thanks a lot

 

[pmegan]

No problem. Glad to have helped.