My favorite [sp_QuickieStore] is giving an error when used with Azure SQL Database
dataVidyarthi opened this issue · 6 comments
Is your feature request related to a problem? Please describe.
I am big fan of you, however, when I had to work on Azure SQL DB, the sp_QuickieStore gives an error
Error:
Msg 208, Level 16, State 1, Procedure sp_QuickieStore, Line 1133 [Batch Start Line 0]
Invalid object name 'sys.dm_hadr_availability_replica_states'.
Describe the solution you'd like
Declare one more Variable , say, @azurecheck and bypass the original logic of if we are in Azure SQL DB.
Original Code which gives error
AND NOT EXISTS
(
SELECT
1/0
FROM sys.dm_hadr_availability_replica_states AS s
JOIN sys.availability_databases_cluster AS c
ON s.group_id = c.group_id
AND d.name = c.database_name
WHERE s.is_local <> 1
AND s.role_desc <> N'PRIMARY'
AND DATABASEPROPERTYEX(c.database_name, N'Updateability') <> N'READ_WRITE'
)
Describe alternatives you've considered
I have modified the original code to this for Azure SQL DB
IF @Azure = 1
BEGIN
-- Logic specific to Azure SQL Database
INSERT INTO #databases WITH(TABLOCK)
(
database_name
)
SELECT
database_name = ISNULL(@database_name, DB_NAME())
WHERE @get_all_databases = 0
UNION ALL
SELECT
database_name = d.name
FROM sys.databases AS d
WHERE @get_all_databases = 1
AND d.is_query_store_on = 1
AND d.database_id > 4
AND d.state = 0
AND d.is_in_standby = 0
AND d.is_read_only = 0
OPTION(RECOMPILE);
END
ELSE
BEGIN
-
INSERT INTO #databases WITH(TABLOCK)
(
database_name
)
SELECT
database_name = ISNULL(@database_name, DB_NAME())
WHERE @get_all_databases = 0
UNION ALL
SELECT
database_name = d.name
FROM sys.databases AS d
WHERE @get_all_databases = 1
AND d.is_query_store_on = 1
AND d.database_id > 4
AND d.state = 0
AND d.is_in_standby = 0
AND d.is_read_only = 0
AND NOT EXISTS
(
SELECT 1/0
FROM sys.dm_hadr_availability_replica_states AS s
JOIN sys.availability_databases_cluster AS c
ON s.group_id = c.group_id
AND d.name = c.database_name
WHERE s.is_local <> 1
AND s.role_desc <> N'PRIMARY'
AND DATABASEPROPERTYEX(c.database_name, N'Updateability') <> N'READ_WRITE'
)
OPTION(RECOMPILE);
END
Are you ready to build the code for the feature?
Yes.
Thanks for reporting this! I'll keep an eye out for the pull request.
Done, created it. I did it for the first time. thanks Erik
@AbhinavTiwariDBA thanks for that, but can you point the PR to the dev branch instead? I can’t accept pull requests directly into the main branch, like I talk about in the contributing guide.
I did that
@AbhinavTiwariDBA thanks! It looks like the stuff from you fork of main is making for a somewhat odd set of changes: https://github.com/erikdarlingdata/DarlingData/pull/393/files
If you don't mind, I'm going to grab your original code change and add it to the dev branch manually. I think that'd be easier than the two of us wrestling with GitHub any more today 😃
Yes please do that. Thanks a ton. I am attaching the modified code too.