SQL Query SSIS Service Names

  • 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

  • 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