SQL Server CMS Multiserver query issues. If statement.

• DC!

  Valued Member

  Points: 52

  More actions

  May 7, 2014 at 4:59 am

  #307141

  Hi,

  I have recently added all of our SQL Servers to our CMS instance. Our environment is a mix of SQL Server from 2000 to 2012.

  Running the simple query:

  DECLARE @ver nvarchar(128)

  SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)

  SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

  IF ( @ver = '11' )

  SELECT Name FROM SYS.DATABASES

  Doesn't return any results unless I set the query window "merge results" option to false.

  If I remove the "If" statement this query will work but because it is a mix environment I would very much like to say if version is 2012 run this query.

  I have tried creating a simple stored procedure to call but this fails to return results, not sure if this is a different issue.

  I'm currently reading through the Microsoft documentation but nothing has come up to suggest this would be a problem. Does any know why this is a problem? Or if I can get around it?

  Kind Regards,

  David.