My problem is that when using multi server queries using SSMS from SQL Server 2008 R2 against SQL Server 2008 R2, 2005 and 2000 I occasionally get error messages such as:
SQL Server 2008R2
Login failed for user 'DomainName\ADName'. Reason: Failed to open the explicitly specified database. [CLIENT: 21.12.0.0]
Error: 18456, Severity: 14, State: 38.
SQL Server 2005
2013-10-10 15:55:34.28 logon Login failed for user 'DomainName\ADName'.
Error: 18456, Severity: 14, State: 16.
SQL Server 2000
Login failed for user 'DomainName\ADName'. [CLIENT: 21.12.0.0]
Now my query works fine but given what my query does is search for errors in logs it is not ideal that it should also generate such errors.
My methodology may be flawed, what I have done is create a group under Local Server Groups and registered a few dozen servers of various versions. Then I right click on this group, select New Query to open a new batch window. Lastly I open my saved script and run it. In rough outline this is what it does
use master
go
determine version of SQL as like '%8.0%' or not
create temporary table based on version and pump results of xp_readerrorlog 0-6 into it
translate temporary table to another and insert to results temporary table
select on results table
drop temporary tables
go
I am a sysadmin on all machines. Two things I tried was the 'use master' statement at the top of the script and changing the registered servers to have a "Connect to database" of master. Neither of these has eliminated the problem. Does anyone have any further suggestions?
