SQL Central Management Servers (CMS), Using
Question
Thursday, January 3, 2013 8:50 PM
Using CMS is so nice. Hitting all the SQL servers with one SQL SELECT or any script for that matter is so productive. I've enjoyed it much until one day, the ugly 'Gremlins' came out. I don't know what happened, could be a change in the network, maybe it's caused from a SQL service pack release, I don't know. I just don't know and cannot find anything out there that really helps me to resolve the problem. What's the problem?
No more results. Just stuff like:
MYSERVER(Domain\MrDoGood): (0 row(s) affected)
MYSERVER(Domain\MrDoGood): An error occurred while executing batch. Error message is: The result set could not be merged because the result schema did not match the schema from the first responding server.
Rows and rows and rows of these. Darn it. I like the other way where the results were so nice and clean with my headers and all. I sure cannot figure this out and I've Googled until I am blue in the face so here is my last resort.
Anyone ever seen this and HAVE A solution? I'm really looking for a SOLUTION since I've slowly researched for about 8 months. I am using SQL 2008 R2 with SP2.
Scrambling I.T. Guy WILD WILD WEST SHOW
All replies (9)
Monday, January 7, 2013 6:56 PM ✅Answered | 2 votes
You could set the Multi Server results Options Merge Results = "False" and rerun the same query. Now you will see multiple result sets. This could help you isolate the issue why the result schema returned by specific server is not the same as other result sets
in SSMS -> Tools -> Options
Query results
-> SQL Server
> Multi Server Results
Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.
Monday, January 7, 2013 7:57 PM ✅Answered | 1 vote
Dah !!!!! That worked perfectly. You are the GOD of the day. I solute you and Sean Gallardy. I learned a lot from both of you.
Sean's is very informative and a small lesson to boot. Your response is simply, DAH !!!!! Whatg was I thinking. Funny how one forgets the simple things.
Thanks Sethu !!!!! Thanks Sean !!!!!!
Tim
Scrambling I.T. Guy WILD WILD WEST SHOW
Thursday, January 3, 2013 10:37 PM | 1 vote
Hi there,
From the message above i guess you have a merge replication? is that correct? check the SQL server agent history and look for failed jobs.come back with that information.
thnaks
kumar
Thursday, January 3, 2013 10:42 PM | 1 vote
Hi there,
From the message above i guess you have a merge replication? is that correct? check the SQL server agent history and look for failed jobs.come back with that information.
thnaks
kumar
I'm not running any jobs Kumar. This happens when I run a script of any kind that I type in using the CMS functionality in 2008 R2 SSMS. That's where you have a block of SQL servers registered in a tree and you run one script and it goes to all the servers in the tree and gives results, at least it use to until whatever happened at my location. Okay? Does that help Kumar. Thanks, Tim
Scrambling I.T. Guy WILD WILD WEST SHOW
Saturday, January 5, 2013 2:32 PM
I get this error when the result from my SELECT statements don't have the same number of columns, or matching data types. Like if I do SELECT * FROM sys.databases and have both 2005 and 2008 servers.
Tibor Karaszi, SQL Server MVP | web | blog
Monday, January 7, 2013 2:32 PM
I have a mixed set of SQL Server versions too on my CMS tree but I am baffled as to why it worked so well then all of a sudden these errors started coming up using the same select Statement I always use. It is strange. I'm trying to associate it with maybe a service pack deploy but that's grasping at straws maybe. :)
Scrambling I.T. Guy WILD WILD WEST SHOW
Monday, January 7, 2013 4:05 PM | 1 vote
Hello,
I'm not sure why you unproposed Tibor's answer as it's correct. If you would like further help as to which instance is causing the issue please run a CMS query with the following query and post your output ALONG with the query you attempted to run when the error occured.
SELECT SERVERPROPERTY('ProductVersion') AS [Version_Info]
You don't need to wait for us though, depending on the versions other columns may have been added or removed and as Tibor pointed out, this is generally caused by SELECT * usage since all columns will be returned which then causes the mismatch error. Technically if a column was added and a column list was specified that did not involve the added column then your query would continue to work.
As an aside, DMVs and columns may be added, removed, or modified per service pack or CU. This is why it is very important to read what changes are included in each and what that may affect.
-Sean
Sean Gallardy | Blog | Twitter
Monday, January 7, 2013 5:17 PM
Here you go. After this list is the script I have trouble with which I did not before. The editor for this thread gave me trouble pasting in the code so...format is ugly.
One more thing, you're explanation is excellent and the kind I was looking for. KUDOs to you !! THANKS !!!
10.50.1600.1
10.50.1600.1
10.50.1600.1
9.00.5000.00
9.00.4053.00
10.0.4000.0
10.0.5500.0
9.00.5000.00
10.50.1600.1
9.00.4053.00
10.50.1600.1
10.50.1600.1
10.0.5512.0
10.50.1600.1
10.0.2531.0
10.0.5500.0
9.00.4035.00
10.50.2500.0
8.00.2301
10.50.1600.1
9.00.4035.00
-- Run from Central Management Servers Repository
USE
master GO
/* Declare Variables */
DECLARE
@finduser varchar(255)
DECLARE
@DOMAIN varchar(25)
DECLARE
@CompareOption varchar(1)
/* Set Variables */
SET
@CompareOption = '%' /* Mathces all names starting with letter(s) you put into @finduser variable */
SET
@DOMAIN = 'xxxx\ /* Static, DO NOT change */
SET
@finduser = ('mmouse' + @CompareOption) /* Type user login or at least one character in login between quotes */
/* Search for SQL login ENTRIES in table sysxlogins, syslogins, and sysusers */
/* If a table does not exist, keep going. Apply easy-to-read column headings */
/* identifying which table the data belongs. Display logins in alphabetical order */
IF
OBJECT_ID('dbo.sysxlogins') IS NOT NULL
SELECT name AS "**SYSXLOGIN**" FROM sysxlogins
WHERE (name LIKE @finduser) OR (name LIKE (@DOMAIN + @finduser))OR (name LIKE UPPER(@finduser))OR (name LIKE ((UPPER(@DOMAIN)) + @finduser))
order by name
IF
OBJECT_ID('dbo.syslogins') IS NOT NULL
SELECT name AS "**SYSLOGINS**" FROM syslogins
WHERE (name LIKE @finduser) OR (name LIKE (@DOMAIN + @finduser))OR (name LIKE UPPER(@finduser))OR (name LIKE ((UPPER(@DOMAIN)) + @finduser))
order by name
IF
OBJECT_ID('dbo.sysusers') IS NOT NULL
SELECT name AS "**SYSUSERS**" FROM sysusers
WHERE (name LIKE @finduser) OR (name LIKE (@DOMAIN + @finduser))OR (name LIKE UPPER(@finduser))OR (name LIKE ((UPPER(@DOMAIN)) + @finduser))
order by name
go
Scrambling I.T. Guy WILD WILD WEST SHOW
Monday, January 7, 2013 5:57 PM | 1 vote
Hello,
Your issue is actually stemming from two things that are very much related and causing a 3rd effect which is the error. I'm not sure how all of a sudden this would just happen although I have two theories on it.
Issue #1: Using deprecated objects
In the query deprecated tables are used (all of the ones starting with sys) to be compatible with SQL Server 2000. When 2005 came out, these objects were deprecated in favor of the new sys schema and DMVs/DMFs and were not backwards compatible (most were, but not all). The reason, I'm assuming, that these are in here is so the query can be used with SQL Server 2000. The interesting note is that the check for the object to exist as you've already realized it doesn't exist on 2005+ is the first issue. Since the query is run as a single batch, presumably because of the variable declarations that don't go across batches, the "first" server to answer with a result set is a 2000 server of which you have 1. This causes an issue as the 2005+ servers don't have any initial result sets (since the object id check turned out null, it's not executed) which places teh second result set at a mismatch from the first, meaning the results can't be combined.
Issue #2: Entire execution as a batch
This was hit on as part of the explanation of issue #1, but I wanted to make a point of it. Since the entire batch is executed together, the issue arises. This isn't to say changing this will accomplish anything useful, just pointing out that is there is an issue with execution it will (depending on severity) stop processing the batch.
Effect #1: Errors when executing the CMS query
The effect of both issues is causing the effect of the CMS query not returning or executing properly.
Theory #1: 2000 was just added
This is my first theory that assumes the SQL Server 2000 instance wasn't in the CMS before whatever time this query blew up. It seems that this probably wouldn't be the case since the query was made to work on 2000, but even if it was run on 2005+ servers ONLY there wouldn't be an issue until the sole 2000 server returned a result set for the first query - hence my theory.
Theory #2: SYSXLOGIN was added
The query would work if the first result set was never returned. Since the other part of this was Theory #1 which takes care of the returning of the result set, this is #2 which deals with the queries themselves. If the sysxlogin query wasn't added to the batch then this would continue to work normally even with the 2000 instance in there - thus the first sysxlogin query was possibly added after the queries have been working for some time.
Please do not take my theories as pointing a finger - I'm merely trying to explain what I see as possible root causes (for root cause analysis). Once you know the root cause, it's possible to figure out a solution and that's what I'll propose now.
Solution #1: Remove 2000 instances
The first solution is to remove the SQL Server 2000 instances from the CMS - obviously that's not the best option but it IS an option so I put it here anyway.
Solution #2: Fix the first query to return a null result from 2005+ servers
If an 'ELSE' is added after the first IF statement dealing with sysxlogins and something such as:
SELECT null as "**SYSXLOGIN**"
Is put after the else, a result set with the same schema will be returned and thus the issue corrected. The problem with this solution is that if some type of automation or cut and paste is used with it, the tools used later in the pipeline may be adversely affected by the addition of nulls or programming changes must be made to account for the addition of them.
Wrap up:
Unfortunately I do not know your environment or what you intend to do - nor do I need to know - with the output of these and other scripts you have. I would warn against using deprecated objects and segmenting those instances in their own containers. While it adds an extra step it also saves headache from when these types of things happen.
I would also like to point out that not all of your instances for a major release are at the same version. This alone could cause issues down the line with CMS queries as columns may be added, altered, or dropped between various levels of service packs and cumulative updates.
-Sean