Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems
It is rare that this will come up, but in certain circumstances you may need to change (or guarantee) the database in which a query executes.
This can really plague you when you’re working with multiple databases on multiple servers. You don’t want to forget which database you’re in… and execute code in the wrong place.
So first of all, you can add this code to the top of each SQL script you have – for a straight query, or a CREATE/ALTER script – to backstop yourself and prevent unexpected accidents!
-- Check to make sure this SQL is running on the correct server:
IF ( SELECT SERVERPROPERTY('MachineName') ) <> '<Expected Server Name Here>'
BEGIN
THROW 50000,N'This code is not running on the correct Server!',1
;
END
;
That makes sure you’re on the right server.
Then…
While we often define a default database in the data source in SSRS, we cannot guarantee that it will be pointing to the zcus (or whatever schema holds your custom code) database. Best practice would indicate using fully qualified names in the SSRS report and in the stored procedure(s) the report invokes.
So far so good.
We’ve worked on several reports that use dynamic management views and procedures, some of which assume the current database. Thus we must guarantee we am operating in the correct database. Turns out that you can insert the USE <database> command as part of a dynamic SQL statement and your query will execute in that database.
This query returns three result sets showing the current database for each query in the batch:
USE [Livefdb] ;
GO
DECLARE @sql NVARCHAR(MAX) ;
SET @sql = N'USE zcus; SELECT [DatabaseName] = DB_NAME() ;'
SELECT [DatabaseName] = DB_NAME();
EXEC ( @sql ) ;
SELECT [DatabaseName] = DB_NAME() ;
The result sets in order:
Livefdb
zcus
Livefdb
Notice the quirk. The changed database context only holds for the execution of the dynamic SQL statement, so your entire query must be in the dynamic SQL variable. Temp tables and variables are also lost after the execution of the dynamic SQL.
This is a real query. The sys.dm_sql_referenced_entities function must be run in the zcus database to return a result set. We force the query to run in the zcus database no matter what the current database may be. I also clean up my temp tables at the end as a matter of good practice.
DECLARE @cDatabase SYSNAME = 'zcus' ;
DECLARE @cSQL NVARCHAR(MAX) ;
SET @cSQL = N'USE ' + @cDatabase + N'; '
+ 'DECLARE
@max INT
,@rownum INT
,@object_name SYSNAME
,@object_type NVARCHAR(2)
,@object_type_description NVARCHAR(60)
;
IF OBJECT_ID(''tempdb.dbo.#Objects'') IS NOT NULL DROP TABLE #Objects;
CREATE TABLE #Objects
(RowNum INT
,object_name SYSNAME COLLATE DATABASE_DEFAULT
,object_type NVARCHAR(2) COLLATE DATABASE_DEFAULT
,object_type_description NVARCHAR(60) COLLATE DATABASE_DEFAULT
)
;
IF OBJECT_ID(''tempdb.dbo.#Dependencies'') IS NOT NULL DROP TABLE #Dependencies;
CREATE TABLE #Dependencies
(ParentObject SYSNAME COLLATE DATABASE_DEFAULT
,ParentObjectType NVARCHAR(2) COLLATE DATABASE_DEFAULT
,ParentObjectTypeDescription NVARCHAR(60) COLLATE DATABASE_DEFAULT
,ReferencedObjectServer SYSNAME COLLATE DATABASE_DEFAULT NULL
,ReferencedObjectDatabase SYSNAME COLLATE DATABASE_DEFAULT NULL
,ReferencedSchemaName SYSNAME COLLATE DATABASE_DEFAULT NULL
,ReferencedEntityName SYSNAME COLLATE DATABASE_DEFAULT NULL
,ReferencedColumnID INT NULL
,ReferencedColumn SYSNAME COLLATE DATABASE_DEFAULT NULL
,Problem NVARCHAR(250) COLLATE DATABASE_DEFAULT NULL
);
INSERT #Objects
SELECT
ROW_NUMBER() OVER (ORDER BY SS.[name] + ''.'' + SO.[name]) AS RowNum
,SS.[name] + ''.'' + SO.[name]
,SO.[type]
,SO.[type_desc]
FROM
'+@cDatabase+'.sys.objects SO
INNER JOIN
'+@cDatabase+'.sys.schemas SS ON SO.[schema_id] = SS.[schema_id]
WHERE
SO.[type] IN (''FN'',''IF'',''TF'',''P'',''V'')
;
---------------------------------
-- Get Dependencies
---------------------------------
SET @max = (SELECT MAX(RowNum) FROM #Objects);
SET @rownum = 1;
WHILE @rownum < = @max
BEGIN
BEGIN TRY
SELECT @object_name = [object_name]
,@object_type = object_type
,@object_type_description = object_type_description
FROM #Objects
WHERE RowNum = @rownum
;
INSERT #Dependencies
SELECT
@object_name
,@object_type
,@object_type_description
,SRE.referenced_server_name
,SRE.referenced_database_name
,SRE.referenced_schema_name
,SRE.referenced_entity_name
,SRE.referenced_minor_id
,SRE.referenced_minor_name
,NULL
FROM
sys.dm_sql_referenced_entities(@object_name,''OBJECT'') SRE
;
END TRY
BEGIN CATCH
--Print Error_MESSAGE()
-- insert #BadRef values (@object_name)
INSERT #Dependencies(ParentObject,ParentObjectType,ParentObjectTypeDescription,Problem)
VALUES(@object_name,@object_type,@object_type_description,N''Invalid References'')
;
END CATCH
SET @rownum = @rownum + 1
END ;
SELECT *
FROM #Dependencies
;
---------------------------------
-- clean up
---------------------------------
IF OBJECT_ID(''tempdb.dbo.#Objects'') IS NOT NULL DROP TABLE #Objects;
IF OBJECT_ID(''tempdb.dbo.#Dependencies'') IS NOT NULL DROP TABLE #Dependencies;'
--PRINT @cSQL ; -- In case you want to see what was created
EXEC ( @cSQL ) ;
Extra Credit
Go through your library of SQL code right now and add that am-I-on-the-right-server code snippet, as appropriate.
And…
If you’d like automatic index optimization and a lovely SQL Agent job monitoring dashboard that also shows you the health of your DR (or any other SQL Server you may have in-house), give us a shout! We have a packaged deployment of code and dashboard that consolidates all of that for you.
As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!
