0

SQL Query Spool Requests per Output Device

Sometimes you may like to know how much spool request your users are printing.
The following MSSQL query will help you to find this information:

dbacockpit

 

DECLARE @PYEAR NVARCHAR(4)
DECLARE @PMONTH NVARCHAR(2)
DECLARE @PDAY NVARCHAR(2)
DECLARE @sid NVARCHAR(3)
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @OUTDEV NVARCHAR(50)

/* If you like you can filter the result according to the output device(s) string */
/* Leave blank for ALL devices */
SET @OUTDEV=’_ST’

/* Set the correct SAP sid (lower case), for SCHEMA name */
SET @sid=’wmp’

/* ——————————————————————– */

SET @PYEAR=(SELECT datepart(year,getdate()))
SET @PMONTH=(SELECT right(’00’ + convert(NVARCHAR(2),datepart(month,getdate
())),2))
SET @PDAY=(SELECT right(’00’ + convert(NVARCHAR(2),datepart(day,getdate())),2))

SET @QUERY = ‘SELECT count(RQCLIENT) AS Print_Count,
RQ1NAME AS Output_Device_Short,
TSP03D.NAME AS Output_Device_Long
FROM ‘ + @sid + ‘.TSP01
INNER JOIN ‘ + @sid + ‘.TSP03D
ON TSP01.RQ1NAME=TSP03D.PADEST
WHERE RQCRETIME LIKE ”’ + @PYEAR+@PMONTH+@PDAY + ‘%” AND NAME LIKE
”%’+@OUTDEV+’%” COLLATE SQL_Latin1_General_CP1_CI_AS
GROUP BY TSP01.RQ1NAME, TSP03D.NAME
ORDER BY Print_Count DESC’

EXEC (@QUERY)

 

Miki Barzilay

Leave a Reply