error on reporting trending part of the Spoltight:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "SpotlightStatisticsRepository" requested by the login. The login failed
Possibly DiagnosticServer is not configured to store repository data.
Make sure the DiagnosticServer is configured to store repository data ( Configure tab -> Diagnostic Server icon -> Spoltight Options -> verify the setting there).
If you have multiple Diagnostic Servers configured, then run Spotlight, go to "Report" tab, select "Repository" item on the ribbon and make sure that valid instance and database name are specified for browsing reports.
Then run Management Studio on the Spotlight-machine and connect to the same SSR database (Spotlight Statistic Repository) specified above: be sure that the database exists and contains SSR DB-objects. Run the SQL below against the SSR DB to prove this fact and see what it returned:
declare @ver int
exec @ver = spotlight_ssr_version
select case when @ver=52 then 'SoSSE2.6'
when @ver=60 then 'SoSSE3.0'
when @ver=61 then 'SoSSE3.0.1 / SoSSE3.0.2'
when @ver=78 then 'SoSSE6.0'
when @ver=81 then 'SoSSE7.0 / SoSSE7.0.1'
when @ver=84 then 'SoSSE7.6 LA / SoSSE7.6.1'
when @ver=85 then 'SoSSE8.0'
else 'Unknown SSR version. Value: ' + cast(@ver as
varchar(5))
end
