Skip to Content
avatar image
Former Member

Show all returns from a query without manually sort, also count returns by value

I have this code:

select LastReqTime,

BlockedOn,

UncommitOps

from sa_conn_info()

WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedOn > 0 AND UncommitOps = 0

An example of the return is:

Is there a way to instruct SQL to return and show all values, without limiting the display? In the above

query, it shows the first 115 rows and I must manually sort or scroll down to see the total number.

Also is there a way to get a count of each unique BlockedOn value, for use in a different query?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 21, 2016 at 06:20 PM

    select BlockedOn, count (*) n

    from sa_conn_info ()

    where ...

    group by BlockedOn

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Volker Stöffler

      Wow, as usual, you've exceeded my expectations. Not only that, you've given me something to study and increase my knowledge. Thanks again!

  • May 21, 2016 at 08:10 PM

    What client software are you using?

    The dbisql utility is sometimes annoying when it forces you to scroll to see all the rows, but in my experience the number is far higher than 115.

    One method is to use OUTPUT TO 'c:\\temp\\select.txt' and then browse the file with notepad.

    FWIW, Foxhound lets you perform all sorts of adhoc queries on connection data after the fact.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Breck,

      I'm using isql. Thanks for the tip on OUTPUT TO, I

      can use that to generate/email reports, which is my

      next project once I get the count totals finished.