Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    PARIS
    Posts
    5

    Question Unanswered: Continue the query if an error occured

    I have a looped query querying linked servers. When it cannot connect to a server, it errors out.

    Error Message;
    Server: Msg 6, Level 16, State 1, Line 2
    Specified SQL server not found.

    How can I handle this error message and continue the query?

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Continue the query if an error occured

    The query is executed by SQL Server or an
    other program ?

  3. #3
    Join Date
    Mar 2003
    Location
    PARIS
    Posts
    5

    Re: Continue the query if an error occured

    Originally posted by Karolyn
    The query is executed by SQL Server or an
    other program ?
    The program is a Transact-SQL script stored in a job launch by the SQL Server Agent.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Continue the query if an error occured

    Maybe in your job you can trap this error with

    IF @@ERROR = ???
    print "An error occurred..."

  5. #5
    Join Date
    Mar 2003
    Location
    PARIS
    Posts
    5

    Re: Continue the query if an error occured

    Originally posted by Karolyn
    Maybe in your job you can trap this error with

    IF @@ERROR = ???
    print "An error occurred..."
    it doesn't work ! when the error occured, the system stop the query execution :

    To test, try for example :


    1/ Create a linked server with a wrong password

    sp_addlinkedserver @server = @name,
    @srvproduct='',
    @provider='SQLOLEDB',
    @datasrc=@name2



    exec sp_addlinkedsrvlogin @rmtsrvname = @name,
    @useself = 'false',
    @locallogin = null ,
    @rmtuser = @user_connect,
    @rmtpassword = @pswd <=== WRONG PASSWORD



    2/ Create a list database in this linkedserver in a stored procedure

    create my_proc
    as
    select name from mylinkedServer.master..sysdatabases
    return @@error

    3/ create a stored procedure which call my-proc

    create call_proc
    as
    declare @status
    exec @status = my_proc
    select 'the stored procedure continue'


    4/ execute call_proc

    ==>
    Error Message;
    Server: Msg 6, Level 16, State 1, Line 2
    Login failes for 'my_user'

    and the procedure don't diplay the message 'the stored procedure continue'

    but I want it

  6. #6
    Join Date
    Mar 2003
    Location
    PARIS
    Posts
    5

    Re: Continue the query if an error occured

    Originally posted by Karolyn
    Maybe in your job you can trap this error with

    IF @@ERROR = ???
    print "An error occurred..."
    someone have an another idea ?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There are certain error in sql server you can't trap...it'll raise out...

    If for example you try an execute a statement against an object that doesn't exist...It'll raise...

    You need to check BEFORE you execute the statement
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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