T-SQL - Get RowCount from a Dynamically Executed Statement

    DECLARE
        @Period VARCHAR (8) = ''
    ,    @DEDFYXXRecords BIGINT = 0
    ,    @EXECFYXX NVARCHAR (MAX) = ''
    ,    @RowCount BIGINT = 0; 


    SELECT @EXECFYXX = ' INSERT Data( '
    +    ' Period '
    +    ', CostCenter '    
    +    ') '
    +    'SELECT '
    +    ' FYXX.Period '
    +    ', FYXX.CostCenter '    
    +    'FROM DataFY'
    +    LEFT (@Period, 2)
    +    ' FYXX '
    +    ' JOIN DataB da '
    +    '     ON FYXX.Account = da.Acct '
    +    ' WHERE da.GroupID = 99 '
    +    '    AND    FYXX.Period = '''
    +    @Period
    +    '''; SELECT @RowCount = @@ROWCOUNT; ';

    EXEC sp_executesql @EXECFYXX, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT

    SELECT @DEDFYXXRecords = @RowCount

Classic ASP DB Conn Cheat

For those time when you gotta go back but don't want to make it too bad...

Scenario: Classic ASP ap (perhaps mixed with .net) that has BO layer in ActiveX dll's (VB, of all things). You want perform some follow-up work after the dll method has run.

We could parse web.config for the connection string and then reformat for ADO ... or we could just use a separate connection string and store it in an include file:

<%

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Joe Kelly
' 2013.07.30
' Classic.inc
'
' Changed to use ../Include/Classic.inc so as to have one place to manage
' connection strings for Classic ASP
'
' Page Admin_Imp_Expense.asp, part of the "Monthly Expense Detail" process
' uses this file 
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

   
Dim connPortal
connPortal = "Provider=SQLNCLI10;Server=TESTSQL;Database=TESTPortal;Uid=TESTUser;Pwd=TESTPw;"

%>

And then to use it ...

    Dim conn, cmd, result 

    Set conn = Server.CreateObject("ADODB.Connection")
    Set cmd = Server.CreateObject("ADODB.Command")
    conn.ConnectionString = connPortal
    conn.Open
    cmd.ActiveConnection = conn
    cmd.CommandText = "EXEC dbo.someProc; "
   
    Set result = cmd.Execute
    If result.EOF = False Then
        While result.EOF = False
            Response.Write ("")
            Response.Write (result.Fields("DEDFYXXRecords"))
            result.MoveNext
        WEnd
    End If
    conn.Close
   
    Set conn = Nothing
    Set cmd = Nothing
   

SQL Server - Currently Running Jobs

-- How to query currently running SQL Server Agent jobs
-- Source: http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/

IF EXISTS (
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
    DROP TABLE [tempdb].[dbo].[Temp1]
   
GO

CREATE TABLE [tempdb].[dbo].[Temp1]
(
    job_id uniqueidentifier NOT NULL
,    last_run_date nvarchar (20) NOT NULL
,    last_run_time nvarchar (20) NOT NULL
,    next_run_date nvarchar (20) NOT NULL
,    next_run_time nvarchar (20) NOT NULL
,    next_run_schedule_id INT NOT NULL
,    requested_to_run INT NOT NULL
,    request_source INT NOT NULL
,    request_source_id sysname COLLATE database_default NULL
,    running INT NOT NULL
,    current_step INT NOT NULL
,    current_retry_attempt INT NOT NULL
,    job_state INT NOT NULL
)

DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]

EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6)
,    next_run_time = right ('000000' + next_run_time, 6);

SELECT j.name AS JobName
,    j.enabled AS Enabled
,    CASE x.running
        WHEN 1
            THEN 'Running'
        ELSE
            CASE h.run_status
                WHEN 2
                    THEN 'Inactive'
                WHEN 4
                    THEN 'Inactive'
                ELSE 'Completed'
            END
    END
AS CurrentStatus
,    coalesce (x.current_step, 0) AS CurrentStepNbr
,    CASE
        WHEN x.last_run_date > 0
            THEN convert (datetime, substring (x.last_run_date, 1, 4)
                + '-' + substring (x.last_run_date, 5, 2)
                + '-' + substring (x.last_run_date, 7, 2)
                + ' ' + substring (x.last_run_time, 1, 2)
                + ':' + substring (x.last_run_time, 3, 2)
                + ':' + substring (x.last_run_time, 5, 2)
                + '.000', 121)
        ELSE
            NULL
    END AS LastRunTime
,    CASE h.run_status
        WHEN 0
            THEN 'Fail'
        WHEN 1
            THEN 'Success'
        WHEN 2
            THEN 'Retry'
        WHEN 3
            THEN 'Cancel'
        WHEN 4
            THEN 'In progress'
    END AS LastRunOutcome
,    CASE
        WHEN h.run_duration > 0
            THEN (h.run_duration / 1000000) * (3600 * 24)
                + (h.run_duration / 10000 % 100) * 3600
                + (h.run_duration / 100 % 100) * 60   
                + (h.run_duration % 100)
        ELSE
            NULL   
    END AS LastRunDuration
FROM    [tempdb].[dbo].[Temp1] x
LEFT JOIN msdb.dbo.sysjobs j
    ON    x.job_id = j.job_id
LEFT JOIN msdb.dbo.syscategories c
    ON    j.category_id = c.category_id
LEFT JOIN msdb.dbo.sysjobhistory h
    ON  x.job_id = h.job_id
    AND x.last_run_date = h.run_date
    AND x.last_run_time = h.run_time
    AND h.step_id = 0
where x.running = 1

SQL SERVER Quick-N-Dirty All Tables with Dates

SELECT
    'U'
,    [name]
,    CAST (create_date AS DATE)
,    CAST (modify_date AS DATE)
FROM sys.tables
WHERE create_date > '2013-01-01'
UNION       
SELECT
    'V'
,    [name]
,    CAST (create_date AS DATE)
,    CAST (modify_date AS DATE)
FROM sys.views
WHERE create_date > '2013-01-01'
UNION
SELECT
    'P'
,    [name]
,    CAST (create_date AS DATE)
,    CAST (modify_date AS DATE)
FROM sys.procedures
WHERE create_date > '2013-01-01'
ORDER BY [name]