Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Trapping sqlcmd errors when executed from a job

    Hi

    I am running some scripts in files using sqlcmd via a SQL Server Agent job. If sqlcmd generates an error (for example if it is unable to connect) then the job fails. However, if the T-SQL within the script is invalid (syntax, name resolution etc etc) the job completes reporting success. If sqlcmd is invoked directly via the query window then no error is raised however there is a result set returned reporting the error.

    Anyone know why and whether is it possible to get the error to be recognised by the job?

    invalid_sql.sql
    Code:
    --The below is not actually valid SQL.
    do SOME stuff, innit!
    sqlcmd
    Code:
    EXEC master.dbo.xp_cmdshell "sqlcmd -S my_server -i C:\invalid_sql.sql"
    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No thunkles on trapping errors in T-SQL scripts run from sqlcmd?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - turned out to be simpler than I expected.

    Code:
    
    DECLARE @ret AS INT
    
    EXEC @ret = master.dbo.xp_cmdshell "sqlcmd -S my_server -i C:\invalid_sql.sql -b"
    
    IF @ret <> 0 BEGIN
    RAISERROR('Error in script called by sqlcmd.', 16, 1)
    END
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •