May 27, 2015 at 12:43 pm
How do I make this query run in 2005,2008,2012 servers and by passing different SSIS service names as per the sql server version.
EXEC master..xp_servicecontrol 'QueryState', 'MsDtsServer100' ---sql 2008 and above
EXEC master..xp_servicecontrol 'QueryState', 'MsDtsServer' --- sql 2005
SSIS service names have changed from 2005 to 2008.
I want to run the above query across all servers but running into errors because we have 2005 and 2008 and 2012 environments and service names for SSIS has changed across environments.
Thanks
May 27, 2015 at 1:11 pm
Can you use a variable name to supply the value instead of just pure text? If so, how about:
DECLARE @VER AS nvarchar(255) = @@VERSION;
DECLARE @TXT AS varchar(3);
SELECT @TXT = 'MsDtsServer' + CASE WHEN SUBSTRING(@VER, CHARINDEX('SQL Server 20', @VER) + 11, 4) > '2005' THEN '100' ELSE '' END;
EXEC master..xp_servicecontrol 'QueryState', @TXT;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply