Post reply

Error creating stored procedure in SQL instance using Availability groups

  • October 9, 2017 at 1:11 pm

    #325790

    Good day all.  I am hoping someone has run into this issue before.

    We have two SQL instances on separate servers that are using Always On.  Not all the databases on the second node are participating in the Always on.  When trying to create a stored procedure on one of the databases that is NOT in the Availability Group getting the error below:

    The target database, 'XYZ', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

    Again, the stored procedure I am trying to create is in a database that is NOT participating in the Always On and I am not trying to create a stored procedure on the XYZ database or anything that references the XYZ database.

    Please help!!!

    Thanks.

  • October 10, 2017 at 9:04 am

    #1963035

    Can you query sys.availability_databases_cluster?

  • October 10, 2017 at 9:20 am

    #1963040

    Maybe a silly suggestion since improper reporting of the database connection isn't supposed to be an issue anymore, but have you tried re-opening SSMS and pasting the stored procedure code into a new query window? 

    Are you getting the same error for any stored procedure you try to create in that database?

  • October 12, 2017 at 7:33 pm

    #1963488

    Yes, on both Primary and Secondary, I can run query select * From sys.availability_databases_cluster without any issues.

    Tried closing and opening new query window and additional window, same error.

  • October 13, 2017 at 1:01 am

    #1963500

    Is the stored procedure trying to go cross database?  So while you may be creating it in a database that is not in an AG, it may be doing some operation in a database that is in the AG?

  • October 13, 2017 at 8:45 am

    #1963563

    Ah, I meant make sure the db isn't part of anything with an AG. Just a double check that you're doing what you think your'e doing.

Viewing 6 posts - 1 through 5 (of 5 total)

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