Post reply

Mirror aware T-SQL

  • July 25, 2007 at 7:44 am

    #179908

    Hi all,

    I have 2 different databases that are both mirrored. I had an idea of setting up a Linked Server that used SQL Native Client for mirroring for each database.

    My question. How do I write sql (stored procedure) that handles the error that happens after a failover of the target database? I've tried the Try Catch block and the @@ERROR but they don't seem to catch the error that happens.

    Thank you for your time,

    Chuck

  • July 25, 2007 at 8:52 am

    #722327

    Why not use a linked server using a connection string that supports mirroring?

    http://www.connectionstrings.com/?carrier=sqlserver2005

    Have not tried it though but the connection should switch over without you knowing.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 25, 2007 at 9:01 am

    #722333

    Hi,

    That's how I set up the Linked Server. I used the FailoverPartner switch in the connection string.

    The problem that I have is after it failsover, the first time that I run a select I get this error.

    OLE DB provider "SQLNCLI" for linked server "EXPORT" returned message "Unspecified error".

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'sa'.

    Msg 4060, Level 11, State 1, Line 1

    Cannot open database "Export" requested by the login. The login failed.

    After that I can run the code as usual.

    Thanks,

    Chuck

  • July 25, 2007 at 9:17 am

    #722337

    Doh. That would happen.

    Sorry, you got me now. Off the top of my head, I have no idea and I don't have a mirror at work.

    A dirty idea:

    BEGIN TRY

    SELECT 1 / 0

    --substiture for SELECT TOP 0 DBID FROM MyLinkedServermaster..sysdatabases

    END TRY

    BEGIN CATCH

    SELECT 'Bailed'

    END CATCH

    --the rest of the code.

    SELECT 'this is a test'

    Bit nasty though...

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 25, 2007 at 9:24 am

    #722341

    As a thought...

    You could be a little cleverer and check the state of the mirror. This would be the same as above but a bit cleaner.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a7b1b9b0-7c19-4acc-9de3-3a7c5e70694d.htm

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 25, 2007 at 10:35 am

    #722365

    I believe the error above is happening as a result of the compiling the stored procedure before running it. I've gotten the stored procedure to work after a failover by adding the below lines.

    exec sp_testlinkedserver N'EXPORT'

    EXEC sp_executesql N'select top 10 * from EXPORT.Export.dbo.EshotHistory'

    I can only surmise that the first line resets the connection to the principal server and the second one no longer errors due to not compiling before running.

    I would still appreciate other thoughts as well, possibly a better solution?

    Thanks for the help Crispin

  • July 25, 2007 at 12:18 pm

    #722392

    There you go, test the linked server I cannot say I know what the interals do but by the name and BOL, it would be right.

    How are you calling it? From an app?

     

    My only concern wopuld be performance.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 26, 2007 at 2:53 am

    #722500

    This code would go into a stored procedure that is called from crystal reports. On a bit of testing, you only notice a performance hit (slight pause) after the mirror has been failed over. Once it's working again, I didn't notice a difference.

    Thanks,

    Chuck

  • July 26, 2007 at 3:16 am

    #722503

    There will be a pause as the driver workouts that the original principal is dead and fails over to the new principal.

    Nothing you can do about that...

    But as you are running this in a report which is not exactly going to suffer from the extra 10ms the proc will take (on a live principal), this is a good solution...

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 28, 2007 at 7:59 am

    #729782

    Ok, now that I am back on this job. I thought I would post some further testing in case anyone looks at this discussion later.

    The Linked Server using Native SQL client doesn't work if you are running the code from one of the servers that's in the mirror. This causes a loopback server query which SQL stops.

    FYI really

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

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