5 Replies Latest reply on Jun 24, 2015 9:46 AM by sqlrockstar

    Web-based Reporting SQL - T-SQL Limitations?

    jbiggley

      One of the advantages of having ciulei on your team is that he brings lots of SQL knowledge as a "not currently practicing full-time" DBA.  We were trying to build a report that would let us capture nodes that were duplicates.  Duplicates by IP were pretty easy:

       

      SELECT Caption, IP_Address, OwnerGroup, Comments

      FROM Nodes WITH (NOLOCK)

      WHERE IP_Address IN (

        SELECT IP_Address FROM Nodes WITH (NOLOCK) GROUP BY IP_Address HAVING COUNT(*) > 1)

      ORDER BY IP_Address

       

      Finding nodes that had the same node name but were monitored via a different IP was a little more challenging.  (This query tests for caption but you could use DNS, sysname, etc.)  Notice that this query excludes nodes where the caption might be the IP address by filtering the SUBSTRING of the caption that is between 1 and 255.

       

      SELECT SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END) AS NodeName,

                Caption,

                IP_Address,

                OwnerGroup,

                Comments

      FROM Nodes WITH (NOLOCK)

      WHERE SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END) IN (

             SELECT SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)

             FROM Nodes WITH (NOLOCK) GROUP BY SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)

             HAVING COUNT(*) > 1)

      AND SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END) NOT BETWEEN '1' AND '255'

      ORDER BY Caption

      GO

       

      The challenge we had was that this returned some false positives (ftp.a.b.c was flagged as a duplicate to ftp.x.y.z because our query only checks for data to the left of the first .) but definitely caught everything else.  Trying to clean up the query (because the query above is a little messy) ciulei came up with the following 2 queries.

       

      The first query uses a temp table (#TEMP_TAB) to store the results.  It works great in SSMS but returns an invalid query in the web report builder.  The second query uses a Common Table Expression but also fails with an invalid query in the web report builder.  Is this a known limitation?  Wondering if sqlrockstar can provide some insight here?

       

      SELECT (CASE

        WHEN PATINDEX('[0-9][0-9].%',Caption)=1

        THEN Caption

        WHEN PATINDEX('[0-9][0-9][0-9].%',Caption)=1

        THEN Caption

        WHEN CHARINDEX('.',Caption) = 0

        THEN Caption

        ELSE LEFT(caption,(CHARINDEX('.',caption) - 1))

        END) AS CapHost

      INTO #TEMP_TAB

      FROM Nodes WITH (NOLOCK)

      ORDER BY Caption

      GO

        --Select all hosts/IPs with duplicates.

      SELECT CapHost, Count(CapHost) AS Dup_Count

      FROM #TEMP_TAB

      GROUP BY CapHost

      HAVING Count(CapHost) > 1

      GO

        --Drop temp table

      DROP TABLE #TEMP_TAB

      GO

       

      ------

       

      WITH TEMP_TAB (Host)

             AS

             (

             SELECT (CASE

                                 WHEN PATINDEX('[0-9][0-9].%',Caption)=1

                                       THEN Caption

                                 WHEN PATINDEX('[0-9][0-9][0-9].%',Caption)=1

                                       THEN Caption

                                 WHEN CHARINDEX('.',Caption) = 0

                                       THEN Caption

                                 ELSE LEFT(caption,(CHARINDEX('.',caption) - 1))

                                 END) AS Host

             FROM Nodes WITH (NOLOCK)

             )

      SELECT Host, COUNT(Host) AS Dup_Count

      FROM TEMP_TAB

      GROUP BY Host

             HAVING COUNT(Host) > 1

      ORDER BY Host