Looking for custom T-SQL

  • I've inventoried all our SQL Servers, Service Accts, etc in a home-grown Inventory DB.

    Simplified here, my Tables\Cols\Data include:

    TABLE=ServerName w/ Cols: ServerName, Active

    TABLE=ServiceAccts w/ Cols: ServerName, SvcAcct, Service

    ServerName Active SvcAcct Service

    ========== ====== ======= =======

    Server123 N SvcAcct123 SQL Service

    ServerABC N SvcAcctABC <--- SQL Service

    ServerABC N SvcAcctABC <--- SQL Agent

    ServerABC Y SvcAcctABC <--- SQL SSRS

    ServerXYX N SvcAcctABC <--- SQL Service

    ServerMNO Y SvcAcctMNO SQL Service

    ServerMNO Y SvcAcctMNO SQL Agent

    ServerLLL N SvcAcct123 SQL Service

    TASK:

    - Looking for T-SQL to list ONLY those ServerName's & SvcAcct's WHERE the SvcAcct is used on different ServerNames.

    - Doesn't matter if the SvcAcct is Active Y or N.

    In the example above:

    SvcAcct 'SvcAcctABC' is used on both ServerABC and ServerXYZ

    SvcAcct 'SvcAcct123' is used on both Server123 and ServerLLL

    so I'd like to see output:

    ServerName Active SvcAcct Service

    ========== ====== ======= =======

    ServerABC N SvcAcctABC <--- SQL Service

    ServerABC N SvcAcctABC <--- SQL Agent

    ServerABC Y SvcAcctABC <--- SQL SSRS

    ServerXYX N SvcAcctABC <--- SQL Service

    Server123 N SvcAcct123 SQL Service

    ServerLLL N SvcAcct123 SQL Service

    thx for any replies here.

    BT
  • That many points and no consumable data?

    SvcAcct is used on different ServerNames

    SELECT ServiceAccount
    FROM Accounts a INNER JOIN Servers s ON a.ServerID = s.ServerID
    GROUP BY ServiceAccount
    HAVING COUNT(*)>1
  • First, having the data in a consumable format is something that will get you a LOT more replies.  The way you have it now, we have to do the leg-work to create those tables and data on our systems to test things out.  This is not very "support friendly".

    But to me that doesn't look too hard to do.  My approach would be to add a calculated column (either in the table or in a CTE) that counts the unique SvcAcct names.  So something like:

    WITH [cte]
    AS
    (
    SELECT
    [ServerName]
    , [SvcAcct]
    , [Service]
    , COUNT([SvcAcct]) OVER (PARTITION BY
    [SvcAcct]
    ) AS [UseCount]
    FROM[ServiceAccts]
    )
    SELECT
    [sn].[ServerName]
    , [sa].[Active]
    , [sa].[SvcAcct]
    , [sa].[Service]
    FROM[ServerName] AS [sn]
    JOIN[cte]
    ON [cte].[ServerName] = [sn].[ServerName]
    WHERE[cte].[UseCount] > 1;

    NOTE - I have NOT tested the above code as I have no data to test it with.  What you posted is not consumable data.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Consummable data means posting the CREATE TABLE statements, with some INSERTs for sample data. Not just describing the columns.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply