Post reply

Help Regarding SQL Programming

  • May 14, 2008 at 10:49 pm

    #187746

    Hi,

    I have a linked server to MS-Access and accidentally if the MDB file is deleted or moved then i want to handle that error in my Stored procedure which queries the access using linked server query. But when the MDB file is deleted the stored procedure directly throws error and quits eventhough i have Set @ErrValue = @@Error in the next statement after select... Any idea how to capture this in @@Error and continue execution to run my own Error handling code?

    SELECT TOP 1 @Dummy = TABLE_NAME from master.dbo.SYSREMOTE_TABLES (@ServerName) --> SP Exits here itself if MDB File is deleted.

    SET @Error = @@ERROR

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • May 14, 2008 at 11:11 pm

    #815634

    Hi

    Yes the error trapping is happing after the call to the OLE/DB provider.

    In 2005 there is a TRY CATCH syntax that can be used - that will allow a better way to trap for errors like this.

    If you are not using 2005 then other ways of error trapping will need to be investigated

    Thanks

    Kevin

  • May 14, 2008 at 11:15 pm

    #815636

    Hi Kevin,

    I need to write SP compatible for SQL 2000 so i cannot use TRY/CATCH ... 🙁

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • May 14, 2008 at 11:36 pm

    #815639

    What if.... ????

    --=YOUR CODE HERE

    IF @@error <> 0

    BEGIN

    --=YOUR OWN ERROR HANDLER

    END

    "-=Still Learning=-"

    Lester Policarpio

  • May 14, 2008 at 11:39 pm

    #815640

    Hi Lester ,

    Yes that is what i have done... but as said.. SQL Server stops execution and select query level itself...it is not executing next line itself... Hope you got it...

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • May 14, 2008 at 11:47 pm

    #815643

    set nocount on

    declare

    @Path varchar (200),

    @TheFile varchar (200),

    @RestorePath varchar (300)

    --select @Path = '\\servername\E$\Softwares\MS Studio 6.0\Disc 1\SAMPLES\VID98\DATABASE\'

    select @Path = '\\jhb_kevinv_xp\c$\'

    --Create temporary table with files lists

    if object_id('tempdb.dbo.#tbltemp') > 0

    begin

    drop table #tbltemp

    end

    create table #tbltemp (Files varchar(1000))

    declare @CmdShell varchar(400)

    set @CmdShell = 'master.dbo.xp_cmdshell ''' + 'Dir ' + @Path + ''''

    --select @CmdShell

    insert into #tbltemp exec (@CmdShell)

    select @TheFile = (select right(files,11) from #tblTemp where upper(right(files,4)) = '.MDB')

    if (select right(files,11) from #tblTemp where upper(right(files,4)) = '.MDB') = 'GALLERY.MDB'

    begin

    --File exists - run SP in here

    select 'File Found'

    end

    set nocount off

  • May 14, 2008 at 11:51 pm

    #815645

    The above code assumes you can enable or use xp_cmdshell - but SQL 2000 this should be ok.

    You also need to use the UNC path to the computer name as I have in the above example

    Just comment out the @path for my pc and enable the one above - replacing servername with the computer name you have the MDB file locatd on

    Then if the file exists then you can replace the {select 'File Found'} with the SP you need to run

    Let us know if this works

    Thanks

    Kevin

  • May 15, 2008 at 12:15 am

    #815651

    Hi Kevin,

    xp_cmdshell is diabled in production servers we cannot use this... 🙁

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • May 15, 2008 at 12:43 am

    #815659

    hopefully someone has had a similar problem to solve and can supply us with a solution

    good luck and let us know when you do resolve this issue

    Thanks

    Kevin

  • August 31, 2008 at 9:05 am

    #865481

    Hi All,

    To resolve this issue, I have created two scripts one for SQL 2000 and other for SQL 2005. Check out them

    http://www.sqlservercentral.com/scripts/Monitoring/63309/ -> SQL 2K

    http://www.sqlservercentral.com/scripts/Monitoring/63307/ -> SQL 2K5

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply