none
SQL Central Management Servers (CMS), Using

    Question

  •      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


    Thursday, January 03, 2013 8:50 PM

Answers

  • 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.

    • Proposed as answer by Naomi N Monday, January 07, 2013 7:04 PM
    • Marked as answer by SCRAMBLINGITGUY Monday, January 07, 2013 7:54 PM
    Monday, January 07, 2013 6:56 PM
    Moderator
  •      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

    Monday, January 07, 2013 7:57 PM

All replies

  • 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 03, 2013 10:37 PM
  • 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

    Thursday, January 03, 2013 10:42 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

    Saturday, January 05, 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 07, 2013 2:32 PM
  • 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 07, 2013 4:05 PM
    Answerer
  • 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 07, 2013 5:17 PM
  • 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


    Sean Gallardy | Blog | Twitter

    Monday, January 07, 2013 5:57 PM
    Answerer
  • 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.

    • Proposed as answer by Naomi N Monday, January 07, 2013 7:04 PM
    • Marked as answer by SCRAMBLINGITGUY Monday, January 07, 2013 7:54 PM
    Monday, January 07, 2013 6:56 PM
    Moderator
  •      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

    Monday, January 07, 2013 7:57 PM