Our SQL server contains around 160 MS SQL databases, one for each premises and a central BI database. My dashboard uses variables to extract data from the relevant databases, but the database names must be dynamically build depending on the premises I look at. I’m trying to add a table to display error in the data range. Here is the MS SQL script I’m trying to get to work:
DECLARE @Suffix VARCHAR(3);
DECLARE @DB NVARCHAR(MAX);
USE [CompanyDataBase.BI]
SELECT @Suffix = [PremisesSuffix]
FROM [dbo].[Premises]
WHERE [PremisesName] IN($PremisesName)
SET @DB = '[CompanyDataBase.' + @Suffix + '.Remote]';
DECLARE @sql nvarchar(MAX) = 'SELECT TOP 10 [SyncLogId]
,[ErrorDateTime]
,[Action]
,[Description]
,[Details]
,[MetaData]
FROM '+@DB + '.[dbo].[SyncError]
WHERE $__timeFilter(ErrorDateTime)
ORDER BY ErrorDateTime DESC'
EXEC(@sql);
I cannot figure out how to get the WHERE $__timeFilter(ErrorDateTime) to work inside the @sql variable. Without it, like in the below script, the table is successfully populated.
DECLARE @Suffix VARCHAR(3);
DECLARE @DB NVARCHAR(MAX);
USE [CompanyDataBase.BI]
SELECT @Suffix = [PremisesSuffix]
FROM [dbo].[Premises]
WHERE [PremisesName] IN($PremisesName)
SET @DB = '[CompanyDataBase.' + @Suffix + '.Remote]';
DECLARE @sql nvarchar(MAX) = 'SELECT TOP 10 [SyncLogId]
,[ErrorDateTime]
,[Action]
,[Description]
,[Details]
,[MetaData]
FROM '+@DB + '.[dbo].[SyncError]
ORDER BY ErrorDateTime DESC'
EXEC(@sql);
I’ve also tried it this way:
DECLARE @Suffix VARCHAR(3);
DECLARE @DB NVARCHAR(MAX);
DECLARE @sql nvarchar(MAX);
DECLARE @sqlCommand nvarchar(MAX);
USE [CompanyDataBase.BI]
SELECT @Suffix = [PremisesSuffix]
FROM [dbo].[Premises]
WHERE [PremisesName] IN($PremisesName)
SET @DB = '[CompanyDataBase.' + @Suffix + '.Remote]';
SELECT @sql = 'SELECT TOP 10 [SyncLogId]
,[ErrorDateTime]
,[Action]
,[Description]
,[Details]
,[MetaData]
FROM '+@DB + '.[dbo].[SyncError]
WHERE {timeFilter}
ORDER BY ErrorDateTime DESC';
SELECT @sqlCommand = REPLACE(@sql, '{timeFilter}', $__timeFilter(ErrorDateTime));
EXEC(@sqlCommand);
With it I’m getting the following error:
Status: 500. Message: db query error: mssql: Incorrect syntax near the keyword ‘BETWEEN’.
As the more experienced people will note, I’m not very proficient in SQL, so I’m mostly stumbling my way through. I’m grateful for any assistance as this is a good learning opportunity for me.