Website

http://jksfo.com

SSIS/SQLAgent - Enable User to See and Start Jobs

To see, the user must be in the SQLAgentReaderRole or SQLAgentUserRole, to start them the user must be in the SQLAgentOperaterRole. As these only exist in MSDB then the user must also be a member there.

Get and record all browser and session values

    public static void recordUserVariables()
    {

        string sessionId = System.Web.HttpContext.Current.Session.SessionID,
        sameSessionId = UtilFns.Common.GetSessionVariable("userSessionId");
        bool recordSession = false;

        /// The sess vars are apparently not alwas set when the user first gets to the portal 
        string temp_ss_Username_aspx = UtilFns.Common.GetSessionVariable("Username_aspx");
        string repeatSessionRecording = UtilFns.Common.GetSessionVariable("repeatSessionRecording");
       
        /// The sess vars are apparently not alwas set when the user first gets to the portal
        /// so set a flag to repeat this when they are present 
        if (String.IsNullOrEmpty(temp_ss_Username_aspx))
        {
            UtilFns.Common.SetSessionVariable("repeatSessionRecording", "1");
        }

        if (repeatSessionRecording == "1")
        {
            recordSession = true;
            UtilFns.Common.SetSessionVariable("repeatSessionRecording", "0");
        }

        if (String.IsNullOrEmpty(sameSessionId))
        {
            recordSession = true;
            UtilFns.Common.SetSessionVariable("userSessionId", sessionId);
        }
        else if (sameSessionId != sessionId)
        {
            recordSession = true;
        }

        if (recordSession)
        {
            /// For recording user values, called from teh home page
            string ss_Username_aspx = "",
            ss_Email_aspx = "",
            ss_IsAdmin_aspx = "",
            ss_ActualUsername_aspx = "",
            ss_ActualIsAdmin_aspx = "",
            ss_FullName2_aspx = "",
            ss_HRAdmin = "",
            ss_eRF_Agency = "",
            ss_BudEntryAlways = "",
            sv_APPL_MD_PATH = "",
            sv_APPL_PHYSICAL_PATH = "",
            sv_AUTH_TYPE = "",
            sv_AUTH_USER = "",
            sv_INSTANCE_META_PATH = "",
            sv_LOCAL_ADDR = "",
            sv_LOGON_USER = "",
            sv_REMOTE_ADDR = "",
            sv_REMOTE_HOST = "",
            sv_REMOTE_USER = "",
            sv_REQUEST_METHOD = "",
            sv_SCRIPT_NAME = "",
            sv_SERVER_NAME = "",
            sv_SERVER_PORT = "",
            sv_SERVER_PROTOCOL = "",
            sv_SERVER_SOFTWARE = "",
            sv_URL = "",
            sv_HTTP_CONNECTION = "",
            sv_HTTP_ACCEPT = "",
            sv_HTTP_ACCEPT_ENCODING = "",
            sv_HTTP_ACCEPT_LANGUAGE = "",
            sv_HTTP_COOKIE = "",
            sv_HTTP_HOST = "",
            sv_HTTP_USER_AGENT = "",
            brwsr_computer_name = "",
            brwsr_MachineName = "",
            brwsr_Type = "",
            brwsr_Browser = "",
            brwsr_Version = "",
            brwsr_MajorVersion = "",
            brwsr_MinorVersion = "",
            brwsr_Platform = "",
            brwsr_Beta = "",
            brwsr_Crawler = "",
            brwsr_AOL = "",
            brwsr_Win16 = "",
            brwsr_Win32 = "",
            brwsr_Frames = "",
            brwsr_Tables = "",
            brwsr_Cookies = "",
            brwsr_VBScript = "",
            brwsr_EcmaScriptVersion = "",
            brwsr_JavaApplets = "",
            brwsr_ActiveXControls = "",
            brwsr_JavaScriptVersion = "";

            try
            {
                ss_Username_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Username_aspx")))?
                    UtilFns.Common.GetSessionVariable("Username_aspx"): "";

                ss_Email_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Email_aspx")))?
                    UtilFns.Common.GetSessionVariable("Email_aspx"): "";

                ss_IsAdmin_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("IsAdmin_aspx")))?
                    UtilFns.Common.GetSessionVariable("IsAdmin_aspx"): "";

                ss_ActualUsername_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Username_aspx")))?
                    UtilFns.Common.GetSessionVariable("Username_aspx"): "";

                ss_ActualIsAdmin_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("ActualIsAdmin_aspx")))?
                    UtilFns.Common.GetSessionVariable("ActualIsAdmin_aspx"): "";
   
                ss_FullName2_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("FullName2_aspx")))?
                    UtilFns.Common.GetSessionVariable("FullName2_aspx"): "";
               
                ss_HRAdmin = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("HRAdmin")))?
                    UtilFns.Common.GetSessionVariable("HRAdmin"): "";
               
                ss_eRF_Agency = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("eRF_Agency")))?
                    UtilFns.Common.GetSessionVariable("eRF_Agency"): "";
               
                ss_BudEntryAlways = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("BudEntryAlways")))?
                    UtilFns.Common.GetSessionVariable("BudEntryAlways"): "";

                sv_APPL_MD_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("APPL_MD_PATH")))?
                    UtilFns.Common.GetServerVariable("APPL_MD_PATH"): "";

                sv_APPL_PHYSICAL_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("APPL_PHYSICAL_PATH")))?
                    UtilFns.Common.GetServerVariable("APPL_PHYSICAL_PATH"): "";

                sv_AUTH_TYPE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("AUTH_TYPE")))?
                    UtilFns.Common.GetServerVariable("AUTH_TYPE"): "";

                sv_AUTH_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("AUTH_USER")))?
                    UtilFns.Common.GetServerVariable("AUTH_USER"): "";

                sv_INSTANCE_META_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("INSTANCE_META_PATH")))?
                    UtilFns.Common.GetServerVariable("INSTANCE_META_PATH"): "";

                sv_LOCAL_ADDR = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("LOCAL_ADDR")))?
                    UtilFns.Common.GetServerVariable("LOCAL_ADDR"): "";

                sv_LOGON_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("LOGON_USER")))?
                    UtilFns.Common.GetServerVariable("LOGON_USER"): ""; 

                sv_REMOTE_ADDR = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_ADDR")))?
                    UtilFns.Common.GetServerVariable("REMOTE_ADDR"): "";

                sv_REMOTE_HOST = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_HOST")))?
                    UtilFns.Common.GetServerVariable("REMOTE_HOST"): "";

                sv_REMOTE_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_USER")))?
                    UtilFns.Common.GetServerVariable("REMOTE_USER"): "";

                sv_REQUEST_METHOD = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REQUEST_METHOD")))?
                    UtilFns.Common.GetServerVariable("REQUEST_METHOD"): "";

                sv_SCRIPT_NAME = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SCRIPT_NAME")))?
                    UtilFns.Common.GetServerVariable("SCRIPT_NAME"): "";

                sv_SERVER_NAME = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_NAME")))?
                    UtilFns.Common.GetServerVariable("SERVER_NAME"): "";

                sv_SERVER_PORT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_PORT")))?
                    UtilFns.Common.GetServerVariable("SERVER_PORT"): "";

                sv_SERVER_PROTOCOL = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_PROTOCOL")))?
                    UtilFns.Common.GetServerVariable("SERVER_PROTOCOL"): "";

                sv_SERVER_SOFTWARE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_SOFTWARE")))?
                    UtilFns.Common.GetServerVariable("SERVER_SOFTWARE"): "";

                sv_URL = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("URL")))?
                    UtilFns.Common.GetServerVariable("URL"): "";

                sv_HTTP_CONNECTION = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_CONNECTION")))?
                    UtilFns.Common.GetServerVariable("HTTP_CONNECTION"): "";

                sv_HTTP_ACCEPT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT")))?
                    UtilFns.Common.GetServerVariable("HTTP_ACCEPT"): "";

                sv_HTTP_ACCEPT_ENCODING = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT_ENCODING")))?
                    UtilFns.Common.GetServerVariable("HTTP_ACCEPT_ENCODING"): "";

                sv_HTTP_ACCEPT_LANGUAGE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT_LANGUAGE")))?
                    UtilFns.Common.GetServerVariable("HTTP_ACCEPT_LANGUAGE"): "";

                sv_HTTP_COOKIE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_COOKIE")))?
                    UtilFns.Common.GetServerVariable("HTTP_COOKIE"): "";

                sv_HTTP_HOST = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_HOST")))?
                    UtilFns.Common.GetServerVariable("HTTP_HOST"): "";

                sv_HTTP_USER_AGENT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_USER_AGENT")))?
                    UtilFns.Common.GetServerVariable("HTTP_USER_AGENT"): "";
               
                string[] computer_name = System.Net.Dns.GetHostEntry
                    (HttpContext.Current.Request.ServerVariables["remote_addr"]).HostName.Split(new Char[] { '.' });
               
                brwsr_computer_name = (!String.IsNullOrEmpty(computer_name[0].ToString()))?
                    computer_name[0].ToString(): "";
               
                brwsr_MachineName = (!String.IsNullOrEmpty(System.Environment.MachineName))?
                    System.Environment.MachineName: "";

                System.Web.HttpBrowserCapabilities browser = HttpContext.Current.Request.Browser;

                brwsr_Type = (!String.IsNullOrEmpty(browser.Type.ToString()))?
                    browser.Type.ToString(): "";

                brwsr_Browser = (!String.IsNullOrEmpty(browser.Browser.ToString()))?
                    browser.Browser.ToString(): "";

                brwsr_Version = (!String.IsNullOrEmpty(browser.Version.ToString()))?
                    browser.Version.ToString(): "";

                brwsr_MajorVersion = (!String.IsNullOrEmpty(browser.MajorVersion.ToString()))?
                    browser.MajorVersion.ToString(): "";

                brwsr_MinorVersion = (!String.IsNullOrEmpty(browser.MinorVersion.ToString()))?
                    browser.MinorVersion.ToString(): "";

                brwsr_Platform = (!String.IsNullOrEmpty(browser.Platform.ToString()))?
                    browser.Platform.ToString(): "";

                brwsr_Beta = (!String.IsNullOrEmpty(browser.Beta.ToString()))?
                    browser.Beta.ToString(): "";

                brwsr_Crawler = (!String.IsNullOrEmpty(browser.Crawler.ToString()))?
                    browser.Crawler.ToString(): "";

                brwsr_AOL = (!String.IsNullOrEmpty(browser.AOL.ToString()))?
                    browser.AOL.ToString(): "";

                brwsr_Win16 = (!String.IsNullOrEmpty(browser.Win16.ToString()))?
                    browser.Win16.ToString(): "";

                brwsr_Win32 = (!String.IsNullOrEmpty(browser.Win32.ToString()))?
                    browser.Win32.ToString(): "";

                brwsr_Frames = (!String.IsNullOrEmpty(browser.Frames.ToString()))?
                    browser.Frames.ToString(): "";

                brwsr_Tables = (!String.IsNullOrEmpty(browser.Tables.ToString()))?
                    browser.Tables.ToString(): "";

                brwsr_Cookies = (!String.IsNullOrEmpty(browser.Cookies.ToString()))?
                    browser.Cookies.ToString(): "";

                brwsr_VBScript = (!String.IsNullOrEmpty(browser.VBScript.ToString()))?
                    browser.VBScript.ToString(): "";

                brwsr_EcmaScriptVersion = (!String.IsNullOrEmpty(browser.EcmaScriptVersion.ToString()))?
                    browser.EcmaScriptVersion.ToString(): "";

                brwsr_JavaApplets = (!String.IsNullOrEmpty(browser.JavaApplets.ToString()))?
                    browser.JavaApplets.ToString(): "";

                brwsr_ActiveXControls = (!String.IsNullOrEmpty(browser.ActiveXControls.ToString()))?
                    browser.ActiveXControls.ToString(): "";

                brwsr_JavaScriptVersion = (!String.IsNullOrEmpty(browser["JavaScriptVersion"].ToString()))?
                    browser["JavaScriptVersion"].ToString(): "";

                string sSQL = @"foo.insBarBrowserValues "
                + " @sessionId = '" + sessionId + "'"
                + ", @ss_Username_aspx = '" +  ss_Username_aspx + "'"
                + ", @ss_Email_aspx = '" +  ss_Email_aspx + "'"
                + ", @ss_IsAdmin_aspx = '" +  ss_IsAdmin_aspx + "'"
                + ", @ss_ActualUsername_aspx = '" +  ss_ActualUsername_aspx + "'"
                + ", @ss_ActualIsAdmin_aspx = '" +  ss_ActualIsAdmin_aspx + "'"
                + ", @ss_FullName2_aspx = '" +  ss_FullName2_aspx + "'"
                + ", @ss_HRAdmin = '" +  ss_HRAdmin + "'"
                + ", @ss_eRF_Agency = '" +  ss_eRF_Agency + "'"
                + ", @ss_BudEntryAlways = '" +  ss_BudEntryAlways + "'"
                + ", @sv_APPL_MD_PATH = '" +  sv_APPL_MD_PATH + "'"
                + ", @sv_APPL_PHYSICAL_PATH = '" +  sv_APPL_PHYSICAL_PATH + "'"
                + ", @sv_AUTH_TYPE = '" +  sv_AUTH_TYPE + "'"
                + ", @sv_AUTH_USER = '" +  sv_AUTH_USER + "'"
                + ", @sv_INSTANCE_META_PATH = '" +  sv_INSTANCE_META_PATH + "'"
                + ", @sv_LOCAL_ADDR = '" +  sv_LOCAL_ADDR + "'"
                + ", @sv_LOGON_USER = '" +  sv_LOGON_USER + "'"
                + ", @sv_REMOTE_ADDR = '" +  sv_REMOTE_ADDR + "'"
                + ", @sv_REMOTE_HOST = '" +  sv_REMOTE_HOST + "'"
                + ", @sv_REMOTE_USER = '" +  sv_REMOTE_USER + "'"
                + ", @sv_REQUEST_METHOD = '" +  sv_REQUEST_METHOD + "'"
                + ", @sv_SCRIPT_NAME = '" +  sv_SCRIPT_NAME + "'"
                + ", @sv_SERVER_NAME = '" +  sv_SERVER_NAME + "'"
                + ", @sv_SERVER_PORT = '" +  sv_SERVER_PORT + "'"
                + ", @sv_SERVER_PROTOCOL = '" +  sv_SERVER_PROTOCOL + "'"
                + ", @sv_SERVER_SOFTWARE = '" +  sv_SERVER_SOFTWARE + "'"
                + ", @sv_URL = '" +  sv_URL + "'"
                + ", @sv_HTTP_CONNECTION = '" +  sv_HTTP_CONNECTION + "'"
                + ", @sv_HTTP_ACCEPT = '" +  sv_HTTP_ACCEPT + "'"
                + ", @sv_HTTP_ACCEPT_ENCODING = '" +  sv_HTTP_ACCEPT_ENCODING + "'"
                + ", @sv_HTTP_ACCEPT_LANGUAGE = '" +  sv_HTTP_ACCEPT_LANGUAGE + "'"
                + ", @sv_HTTP_COOKIE = '" +  sv_HTTP_COOKIE + "'"
                + ", @sv_HTTP_HOST = '" +  sv_HTTP_HOST + "'"
                + ", @sv_HTTP_USER_AGENT = '" +  sv_HTTP_USER_AGENT + "'"
                + ", @brwsr_computer_name = '" +  brwsr_computer_name + "'"
                + ", @brwsr_MachineName = '" +  brwsr_MachineName + "'"
                + ", @brwsr_Type = '" +  brwsr_Type + "'"
                + ", @brwsr_Browser = '" +  brwsr_Browser + "'"
                + ", @brwsr_Version = '" +  brwsr_Version + "'"
                + ", @brwsr_MajorVersion = '" +  brwsr_MajorVersion + "'"
                + ", @brwsr_MinorVersion = '" +  brwsr_MinorVersion + "'"
                + ", @brwsr_Platform = '" +  brwsr_Platform + "'"
                + ", @brwsr_Beta = '" +  brwsr_Beta + "'"
                + ", @brwsr_Crawler = '" +  brwsr_Crawler + "'"
                + ", @brwsr_AOL = '" +  brwsr_AOL + "'"
                + ", @brwsr_Win16 = '" +  brwsr_Win16 + "'"
                + ", @brwsr_Win32 = '" +  brwsr_Win32 + "'"
                + ", @brwsr_Frames = '" +  brwsr_Frames + "'"
                + ", @brwsr_Tables = '" +  brwsr_Tables + "'"
                + ", @brwsr_Cookies = '" +  brwsr_Cookies + "'"
                + ", @brwsr_VBScript = '" +  brwsr_VBScript + "'"
                + ", @brwsr_EcmaScriptVersion = '" +  brwsr_EcmaScriptVersion + "'"
                + ", @brwsr_JavaApplets = '" +  brwsr_JavaApplets + "'"
                + ", @brwsr_ActiveXControls = '" +  brwsr_ActiveXControls + "'"
                + ", @brwsr_JavaScriptVersion = '" +  brwsr_JavaScriptVersion + "'" ;

                DataUtilities.utilSQL.DBExecNonQueryText(sSQL, "foo");
         
            }
            catch (Exception ex)
            {
                UtilFns.Common.LogAll("Error Recording User Values", ex.ToString(), UtilFns.Common.GetCurrentPageName(),
                "Session ID: " + sessionId, true);

            }
            finally
            {
                ;
            }
        }
    }


--DROP TABLE foo.userBarValues

--USE foo
--GO
--CREATE TABLE foo.userBarValues (
--    ident BIGINT IDENTITY (1, 1)
--,    sessionId VARCHAR(128) NOT NULL
--,    ss_Username_aspx VARCHAR(128)
--,    ss_Email_aspx VARCHAR(128)
--,    ss_IsAdmin_aspx VARCHAR(2)
--,    ss_ActualUsername_aspx VARCHAR(128)
--,    ss_ActualIsAdmin_aspx VARCHAR(2)
--,    ss_FullName2_aspx VARCHAR(128)
--,    ss_HRAdmin VARCHAR(2)
--,    ss_eRF_Agency VARCHAR(2)
--,    ss_BudEntryAlways VARCHAR(2)
--,    sv_APPL_MD_PATH VARCHAR(128)
--,    sv_APPL_PHYSICAL_PATH VARCHAR(128) 
--,    sv_AUTH_TYPE VARCHAR(128) 
--,    sv_AUTH_USER VARCHAR(128) 
--,    sv_INSTANCE_META_PATH VARCHAR(128) 
--,    sv_LOCAL_ADDR VARCHAR(32) 
--,    sv_LOGON_USER VARCHAR(128)
--,    sv_REMOTE_ADDR VARCHAR(32) 
--,    sv_REMOTE_HOST VARCHAR(32)
--,    sv_REMOTE_USER  VARCHAR(128)
--,    sv_REQUEST_METHOD VARCHAR(16)
--,    sv_SCRIPT_NAME VARCHAR(128) 
--,    sv_SERVER_NAME VARCHAR(128) 
--,    sv_SERVER_PORT VARCHAR(8) 
--,    sv_SERVER_PROTOCOL VARCHAR(16) 
--,    sv_SERVER_SOFTWARE VARCHAR(128) 
--,    sv_URL VARCHAR(128) 
--,    sv_HTTP_CONNECTION VARCHAR(128) 
--,    sv_HTTP_ACCEPT VARCHAR(1024) 
--,    sv_HTTP_ACCEPT_ENCODING VARCHAR(128)  
--,    sv_HTTP_ACCEPT_LANGUAGE VARCHAR(128)  
--,    sv_HTTP_COOKIE VARCHAR(1024)  
--,    sv_HTTP_HOST VARCHAR(128)  
--,    sv_HTTP_USER_AGENT VARCHAR(1024)  
--,    brwsr_computer_name VARCHAR(128)  
--,    brwsr_MachineName VARCHAR(128)  
--,    brwsr_Type VARCHAR(128)  
--,    brwsr_Browser VARCHAR(128)  
--,    brwsr_Version VARCHAR(16)  
--,    brwsr_MajorVersion VARCHAR(16)  
--,    brwsr_MinorVersion VARCHAR(16)  
--,    brwsr_Platform VARCHAR(128)  
--,    brwsr_Beta VARCHAR(16)  
--,    brwsr_Crawler VARCHAR(16)  
--,    brwsr_AOL VARCHAR(16)  
--,    brwsr_Win16 VARCHAR(16)  
--,    brwsr_Win32 VARCHAR(16)  
--,    brwsr_Frames VARCHAR(16)  
--,    brwsr_Tables VARCHAR(16)  
--,    brwsr_Cookies VARCHAR(16)  
--,    brwsr_VBScript VARCHAR(16)  
--,    brwsr_EcmaScriptVersion VARCHAR(16)  
--,    brwsr_JavaApplets VARCHAR(16)  
--,    brwsr_ActiveXControls VARCHAR(16)  
--,    brwsr_JavaScriptVersion VARCHAR(16)  
--,    crDate DATETIME DEFAULT GETDATE()
--)



USE PerformanceFoo
GO
CREATE PROC Foo.insBarBrowserValues (
    @sessionId VARCHAR(128)
,    @ss_Username_aspx VARCHAR(128)
,    @ss_Email_aspx VARCHAR(128)
,    @ss_IsAdmin_aspx VARCHAR(2)
,    @ss_ActualUsername_aspx VARCHAR(128)
,    @ss_ActualIsAdmin_aspx VARCHAR(2)
,    @ss_FullName2_aspx VARCHAR(128)
,    @ss_HRAdmin VARCHAR(2)
,    @ss_eRF_Agency VARCHAR(2)
,    @ss_BudEntryAlways VARCHAR(2)
,    @sv_APPL_MD_PATH VARCHAR(128)
,    @sv_APPL_PHYSICAL_PATH VARCHAR(128) 
,    @sv_AUTH_TYPE VARCHAR(128) 
,    @sv_AUTH_USER VARCHAR(128) 
,    @sv_INSTANCE_META_PATH VARCHAR(128) 
,    @sv_LOCAL_ADDR VARCHAR(32) 
,    @sv_LOGON_USER VARCHAR(128)
,    @sv_REMOTE_ADDR VARCHAR(32) 
,    @sv_REMOTE_HOST VARCHAR(32)
,    @sv_REMOTE_USER  VARCHAR(128)
,    @sv_REQUEST_METHOD VARCHAR(16)
,    @sv_SCRIPT_NAME VARCHAR(128) 
,    @sv_SERVER_NAME VARCHAR(128) 
,    @sv_SERVER_PORT VARCHAR(8) 
,    @sv_SERVER_PROTOCOL VARCHAR(16) 
,    @sv_SERVER_SOFTWARE VARCHAR(128) 
,    @sv_URL VARCHAR(128) 
,    @sv_HTTP_CONNECTION VARCHAR(128) 
,    @sv_HTTP_ACCEPT VARCHAR(1024) 
,    @sv_HTTP_ACCEPT_ENCODING VARCHAR(128)  
,    @sv_HTTP_ACCEPT_LANGUAGE VARCHAR(128)  
,    @sv_HTTP_COOKIE VARCHAR(1024)  
,    @sv_HTTP_HOST VARCHAR(128)  
,    @sv_HTTP_USER_AGENT VARCHAR(1024)  
,    @brwsr_computer_name VARCHAR(128)  
,    @brwsr_MachineName VARCHAR(128)  
,    @brwsr_Type VARCHAR(128)  
,    @brwsr_Browser VARCHAR(128)  
,    @brwsr_Version VARCHAR(16)  
,    @brwsr_MajorVersion VARCHAR(16)  
,    @brwsr_MinorVersion VARCHAR(16)  
,    @brwsr_Platform VARCHAR(128)  
,    @brwsr_Beta VARCHAR(16)  
,    @brwsr_Crawler VARCHAR(16)  
,    @brwsr_AOL VARCHAR(16)  
,    @brwsr_Win16 VARCHAR(16)  
,    @brwsr_Win32 VARCHAR(16)  
,    @brwsr_Frames VARCHAR(16)  
,    @brwsr_Tables VARCHAR(16)  
,    @brwsr_Cookies VARCHAR(16)  
,    @brwsr_VBScript VARCHAR(16)  
,    @brwsr_EcmaScriptVersion VARCHAR(16)  
,    @brwsr_JavaApplets VARCHAR(16)  
,    @brwsr_ActiveXControls VARCHAR(16)  
,    @brwsr_JavaScriptVersion VARCHAR(16)  
)
AS
BEGIN
   
    /*************************************************************************
    Joe Kelly
    2013-11-08 15:50:13.990

    For collecting data about the browser a user is employing, called from
    the default page in the portal

    *************************************************************************/

    SET NOCOUNT ON

    DECLARE @errorMsg VARCHAR (MAX) = ''

    BEGIN TRY

        INSERT Foo.userBarValues (
            sessionId
        ,    ss_Username_aspx
        ,    ss_Email_aspx
        ,    ss_IsAdmin_aspx
        ,    ss_ActualUsername_aspx
        ,    ss_ActualIsAdmin_aspx
        ,    ss_FullName2_aspx
        ,    ss_HRAdmin
        ,    ss_eRF_Agency
        ,    ss_BudEntryAlways
        ,    sv_APPL_MD_PATH
        ,    sv_APPL_PHYSICAL_PATH
        ,    sv_AUTH_TYPE
        ,    sv_AUTH_USER
        ,    sv_INSTANCE_META_PATH
        ,    sv_LOCAL_ADDR
        ,    sv_LOGON_USER
        ,    sv_REMOTE_ADDR
        ,    sv_REMOTE_HOST
        ,    sv_REMOTE_USER
        ,    sv_REQUEST_METHOD
        ,    sv_SCRIPT_NAME
        ,    sv_SERVER_NAME
        ,    sv_SERVER_PORT
        ,    sv_SERVER_PROTOCOL
        ,    sv_SERVER_SOFTWARE
        ,    sv_URL
        ,    sv_HTTP_CONNECTION
        ,    sv_HTTP_ACCEPT
        ,    sv_HTTP_ACCEPT_ENCODING
        ,    sv_HTTP_ACCEPT_LANGUAGE
        ,    sv_HTTP_COOKIE
        ,    sv_HTTP_HOST
        ,    sv_HTTP_USER_AGENT
        ,    brwsr_computer_name
        ,    brwsr_MachineName
        ,    brwsr_Type
        ,    brwsr_Browser
        ,    brwsr_Version
        ,    brwsr_MajorVersion
        ,    brwsr_MinorVersion
        ,    brwsr_Platform
        ,    brwsr_Beta
        ,    brwsr_Crawler
        ,    brwsr_AOL
        ,    brwsr_Win16
        ,    brwsr_Win32
        ,    brwsr_Frames
        ,    brwsr_Tables
        ,    brwsr_Cookies
        ,    brwsr_VBScript
        ,    brwsr_EcmaScriptVersion
        ,    brwsr_JavaApplets
        ,    brwsr_ActiveXControls
        ,    brwsr_JavaScriptVersion
        )
        SELECT
            @sessionId
        ,    @ss_Username_aspx
        ,    @ss_Email_aspx
        ,    @ss_IsAdmin_aspx
        ,    @ss_ActualUsername_aspx
        ,    @ss_ActualIsAdmin_aspx
        ,    @ss_FullName2_aspx
        ,    @ss_HRAdmin
        ,    @ss_eRF_Agency
        ,    @ss_BudEntryAlways
        ,    @sv_APPL_MD_PATH
        ,    @sv_APPL_PHYSICAL_PATH
        ,    @sv_AUTH_TYPE
        ,    @sv_AUTH_USER
        ,    @sv_INSTANCE_META_PATH
        ,    @sv_LOCAL_ADDR
        ,    @sv_LOGON_USER
        ,    @sv_REMOTE_ADDR
        ,    @sv_REMOTE_HOST
        ,    @sv_REMOTE_USER
        ,    @sv_REQUEST_METHOD
        ,    @sv_SCRIPT_NAME
        ,    @sv_SERVER_NAME
        ,    @sv_SERVER_PORT
        ,    @sv_SERVER_PROTOCOL
        ,    @sv_SERVER_SOFTWARE
        ,    @sv_URL
        ,    @sv_HTTP_CONNECTION
        ,    @sv_HTTP_ACCEPT
        ,    @sv_HTTP_ACCEPT_ENCODING
        ,    @sv_HTTP_ACCEPT_LANGUAGE
        ,    @sv_HTTP_COOKIE
        ,    @sv_HTTP_HOST
        ,    @sv_HTTP_USER_AGENT
        ,    @brwsr_computer_name
        ,    @brwsr_MachineName
        ,    @brwsr_Type
        ,    @brwsr_Browser
        ,    @brwsr_Version
        ,    @brwsr_MajorVersion
        ,    @brwsr_MinorVersion
        ,    @brwsr_Platform
        ,    @brwsr_Beta
        ,    @brwsr_Crawler
        ,    @brwsr_AOL
        ,    @brwsr_Win16
        ,    @brwsr_Win32
        ,    @brwsr_Frames
        ,    @brwsr_Tables
        ,    @brwsr_Cookies
        ,    @brwsr_VBScript
        ,    @brwsr_EcmaScriptVersion
        ,    @brwsr_JavaApplets
        ,    @brwsr_ActiveXControls
        ,    @brwsr_JavaScriptVersion

    END TRY
    BEGIN CATCH
        --
        SELECT @errorMsg = foo.fnGetErrorTryCatch(0)
   
        SELECT @sessionId = 'Error logging values for session: ' + @sessionId
   
        EXEC dbo.ins_sys_error_log
            'foo.insBarBrowserValues'
        ,    @sessionId
        ,    0
        ,    @errorMsg
        ,    1
       
    END CATCH

END

IIS6 Max Buffer Output Size

http://support.microsoft.com/kb/925764

Increase the AspBufferingLimit value in the metabase or ...

Method 4: Increase the buffer limit

You can increase the buffering limit if one of the following conditions is true:

• The client is not a Web browser.
• You cannot redesign the application to take advantage of a paging technology, such as the GridView class.

If you must increase the buffer limit, select a buffer limit that allows for the largest known response size. If you do not know the largest response size in advance, you can increase the buffer limit to a large value during testing. After you finish testing, use the largest value that is in the sc-bytes field in the IIS log file for the response that is generated for the page.

To increase the buffering limit, follow these steps:

1. Click Start, click Run, type cmd, and then click OK.
2. Type the following command, and then press ENTER:
   cd /d %systemdrive%\inetpub\adminscripts
3. Type the following command, and then press ENTER:
   cscript.exe adsutil.vbs SET w3svc/aspbufferinglimit LimitSize
   Note LimitSize represents the buffering limit size in bytes. For example, the number 67108864 sets the buffering limit size to 64 MB.

To confirm that the buffer limit is set correctly, follow these steps:

1. Click Start, click Run, type cmd, and then click OK.
2. Type the following command, and then press ENTER:
   cd /d %systemdrive%\inetpub\adminscripts
3. Type the following command, and then press ENTER:
   cscript.exe adsutil.vbs GET w3svc/aspbufferinglimit
    

Tool for Scripting View Generation

/******************************************************************************
Joe Kelly
2013-08-29 13:54:01.987
ScriptGen.sql

Given two databases, source and dest, this script will generate a READ ONLY
view in dest for each table or view in source.

Note:

    - Existing schema.object definitions in dest will be overwritten

    - If one tries to update one of the read only views an error as follows
      will be generated:

        Update or insert of view or function 'abc' failed because it
        contains a derived or constant field.

      this is due to the intentional ambiguity introduced to the view by a
      union on the same object with a predicate of where 1 = 0

    - Columns are not named because on larger objects the buffer can be
      exceeded resulting in syntax errors (yes, it would better to do the
      concatenation in an external program)

    - For easier testing change the rowcount set

To Use:

    Set the Custom variables and execute in text mode or to file, apply output carefully

        @sourceDB SYSNAME = 'test'
    ,    @destDB SYSNAME = 'test2'
    ,    @sourceSchema SYSNAME = 'dbo'
    ,    @destSchema SYSNAME = 'someSchema'

******************************************************************************/


USE test

GO

-- SET ROWCOUNT 30
SET ROWCOUNT 0

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Custom variables
DECLARE
    @sourceDB SYSNAME = 'foo'
,    @destDB SYSNAME = 'test2'
,    @sourceSchema SYSNAME = 'dbo'
,    @destSchema SYSNAME = 'someSchema'

-- System variables
DECLARE
    @rowCount BIGINT = 0
,    @rowIter BIGINT = 0
,    @columnCount INT = 0
,    @columnIter BIGINT = 0
,    @tableString NVARCHAR (MAX) = ''
,    @tableStringShort NVARCHAR (MAX) = ''
,    @tableName SYSNAME = ''
,    @tableType NVARCHAR (16) = ''
,    @commandString NVARCHAR (MAX) = ''
,    @use NVARCHAR (MAX) = ''
,    @drop1 NVARCHAR (MAX) = 'IF OBJECT_ID('''
,    @drop2 NVARCHAR (MAX) = ''') IS NOT NULL DROP VIEW '

SELECT @use = 'USE ' + @sourceDB

-- Ensure we are in the correct DB
EXECUTE sp_executesql @stmt = @use

IF OBJECT_ID('tempdb..#columns') IS NOT NULL
    DROP TABLE #columns

IF OBJECT_ID('tempdb..#tables') IS NOT NULL
    DROP TABLE #tables

IF OBJECT_ID('tempdb..#output') IS NOT NULL
    DROP TABLE #output

CREATE TABLE #columns (
    ident BIGINT IDENTITY (1, 1)
,    TABLE_CATALOG SYSNAME
,    TABLE_SCHEMA SYSNAME    
,    TABLE_NAME SYSNAME    
,    TABLE_TYPE SYSNAME
,    COLUMN_NAME SYSNAME 
,    ORDINAL_POSITION INT
)

CREATE TABLE #tables (
    ident BIGINT IDENTITY (1, 1) 
,    TABLE_CATALOG SYSNAME
,    TABLE_SCHEMA SYSNAME    
,    TABLE_NAME SYSNAME    
,    TABLE_TYPE SYSNAME
,    columnCount INT
,    rowStart BIGINT
)

CREATE TABLE #output (
    ident BIGINT IDENTITY (1, 1) 
,    command NVARCHAR (MAX)
)

-- Get a list of all the columns in the tables and views 
INSERT #columns (
    TABLE_CATALOG   
,    TABLE_SCHEMA   
,    TABLE_NAME   
,    TABLE_TYPE
,    COLUMN_NAME
,    ORDINAL_POSITION
)    
SELECT
    t.TABLE_CATALOG   
,    t.TABLE_SCHEMA   
,    t.TABLE_NAME   
,    t.TABLE_TYPE
,    c.COLUMN_NAME
,    c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON    t.TABLE_CATALOG = c.TABLE_CATALOG   
    AND    t.TABLE_SCHEMA = c.TABLE_SCHEMA   
    AND    t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
ORDER BY
    t.TABLE_CATALOG   
,    t.TABLE_SCHEMA   
,    t.TABLE_NAME   

-- Get a list of all the distinct tables and views 
INSERT #tables (
    TABLE_CATALOG
,    TABLE_SCHEMA
,    TABLE_NAME
,    TABLE_TYPE
,    columnCount
,    rowStart
)
SELECT DISTINCT
    c.TABLE_CATALOG
,    c.TABLE_SCHEMA
,    c.TABLE_NAME
,    c.TABLE_TYPE
,    COUNT (c.TABLE_CATALOG)
,    MIN (c.ident)
FROM #columns c
GROUP BY
    c.TABLE_CATALOG
,    c.TABLE_SCHEMA
,    c.TABLE_NAME
,    c.TABLE_TYPE

SELECT @rowCount = @@ROWCOUNT

-- Set the destination db for the output
INSERT #output ( command )
SELECT 'USE ' + @destDB
+    CHAR (10) -- + CHAR (13)
+    CHAR (10) -- + CHAR (13)
+    'GO '
+    CHAR (10) -- + CHAR (13)

-- Ensure that the schema exists in the destination db
-- Create schema has to be the first command in a batch
-- so this will be a fatal error
INSERT #output ( command )
SELECT 'IF SCHEMA_ID('''
+    @destSchema
+    ''') IS NULL '
+    'SELECT ''Destination Schema ['
+    @destSchema
+    '] does not extist'''
+    CHAR (10) -- + CHAR (13)
+    CHAR (10) -- + CHAR (13)
+    'GO '
+    CHAR (10) -- + CHAR (13)
--+     'CREATE SCHEMA '
--+    '['
--+    @destSchema
--+    '] AUTHORIZATION dbo '
--+    CHAR (10) -- + CHAR (13)
--+    CHAR (10) -- + CHAR (13)
--+    'GO '
--+    CHAR (10) -- + CHAR (13)

-- Iterate through the list of tables (order is important)
WHILE @rowIter < @rowCount
BEGIN

    SELECT @rowIter += 1
    ,    @columnIter = 0
    ,    @commandString = ''

    SELECT    @columnCount = t.columnCount
    ,    @tableName = t.TABLE_NAME
    ,    @tableType = t.TABLE_TYPE
    ,    @tableString = '[' + @destDB + '].[' + @destSchema + '].[' + t.TABLE_NAME + ']'
    ,    @tableStringShort = '[' + @destSchema + '].[' + t.TABLE_NAME + ']'
    FROM    #tables t
    WHERE    t.ident = @rowIter

    SELECT @commandString += 'SELECT * '

    -- This can produce text strings that are just too long fo the
    -- SSMS editor so just use the wildcard ...

    ---- Iterate through the list of columns (order is important)
    --WHILE @columnIter < @columnCount -1
    --BEGIN

    --    SELECT @columnIter += 1

    --    -- Get all but the last column
    --    SELECT    @commandString += '['
    --    +    c.COLUMN_NAME
    --    +    '] /*'
    --    +    CAST (c.ORDINAL_POSITION AS VARCHAR (8))
    --    +    '*/ , '
    --    FROM    #columns c
    --    WHERE    c.TABLE_NAME = @tableName
    --        AND    c.ORDINAL_POSITION = @columnIter
       
    --END 

    --SELECT @columnIter += 1

    ---- Now get the last column
    --SELECT    @commandString += '['
    --+    c.COLUMN_NAME
    --+    '] /*'
    --+    CAST (c.ORDINAL_POSITION AS VARCHAR (8))
    --+    '*/ '
    --FROM    #columns c
    --WHERE    c.TABLE_NAME = @tableName
    --    AND    c.ORDINAL_POSITION = @columnIter

    -- Now build up the table name and attach
    SELECT    @commandString += ' FROM ['
    +    @sourceDB    
    +    '].['
    +    @sourceSchema       
    +    '].['
    +    @tableName
    +    ']'
   
    -- Drop statements
    INSERT #output ( command )
    SELECT    @drop1
    +    @tableString   
    +    @drop2
    +    @tableStringShort
    +    ';'
    +    CHAR (10) -- + CHAR (13)
    +    CHAR (10) -- + CHAR (13)
    +    'GO '
    +    CHAR (10) -- + CHAR (13)

    INSERT #output ( command )
    SELECT 'CREATE VIEW '
    +    @tableStringShort
    +    CHAR (10) -- + CHAR (13)
    +    'AS '
    +    CHAR (10) -- + CHAR (13)
    +     @commandString
    +    CHAR (10) -- + CHAR (13)
    +    'UNION '
    +    CHAR (10) -- + CHAR (13)
    +    @commandString
    +    ' WHERE 1 = 0; '
    +    CHAR (10) -- + CHAR (13)
    +    CHAR (10) -- + CHAR (13)
    +    'GO '
    +    CHAR (10) -- + CHAR (13)
   
END 

SELECT command '-- BE REALLY CAREFUL WHERE YOU RUN THIS ... '
FROM #output

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]

SSIS - Package Failure on Deployment 2008 R2

http://agilebi.com/jwelch/2010/05/13/moving-ssis-packages-with-ado-net-destinations-between-2008-r2-and-2008/

Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "ADO NET Destination" (257) is not compatible with this version of the DataFlow.  [[The version or pipeline version or both for the specified component is higher than the current version.  This package was probably created on a new version of DTS or the component than is installed on the current PC.]]

Moving SSIS Packages with ADO.NET Destinations Between 2008 R2 and 2008

May 13, 2010, 10:19 pm

As noted by Matt Masson from the Integration Services team, not much has changed in SSIS 2008 R2. In fact, R2 is pretty much identical, with the exception of the ADO.NET Destination. So if you are developing packages, you can build them using the 2008 or 2008 R2 version of BIDS, and they can be used in either environment successfully, as long as the package doesn’t have an ADO.NET Destination. If it does, a package developed in the 2008 R2 version of BIDS will give errors when you open on a machine with the 2008 version of SSIS installed. This is because the 2008 version of the ADO.NET Destination doesn’t know what to do with the new property added to the R2 version.
This wouldn’t be a big deal, if you could have side-by-side installs of BIDS 2008 and BIDS 2008 R2, but the install for R2 replaces the 2008 version of BIDS. So, if you need to move packages developed in 2008 R2 to 2008 (a common scenario for me), you have to do a little extra work. There are a few values that need to be removed or changed by directly editing the package XML.
As always, it’s a good idea to make a backup of your package before editing the XML directly.
Open the package in your favorite text or XML editor, and look for the section that contains . Underneath that, you need to locate the tag that relates to your ADO NET Destination (the sample below is easy, since the name is “ADO NET Destination”, but that’s not the typical case (you do give your components meaningful names, right?).

<component id="16" name="ADO NET Destination" componentClassID="{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}" description="Writes to a database using ADO.NET provider." localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="1" pipelineVersion="0" contactInfo="Writes to a database using ADO.NET provider.;Microsoft Corporation; Microsoft SqlServer v10; © 2007 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">
  <properties>
    <property id="23" name="TableOrViewName" dataType="System.String" state="default" isArray="false" description="The Destination database table name." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">"sample"</property>
    <property id="24" name="BatchSize" dataType="System.Int32" state="default" isArray="false" description="Specify the number of rows per batch. The default is 0 which uses the same size as SSIS internal buffer. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">0</property>
    <property id="25" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates infinite time-out. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">30</property>
    <property id="26" name="UseBulkInsertWhenPossible" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether to use the SqlBulkCopy interface to improve the performance of bulk insert operations. Only certain providers support this interface." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">true</property>
    <property id="27" name="UserComponentTypeName" dataType="System.String" state="default" isArray="false" description="" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</property>
  </properties>

Once you’ve located the right tag, you need to make two changes:
One, change the version=”1” attribute in the tag to version=”0”.

<component id="16"
           name="ADO NET Destination"
           componentClassID="{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}"
           description="Writes to a database using ADO.NET provider."
           localeId="-1"
           usesDispositions="true"
           validateExternalMetadata="True"
           version="0"
           pipelineVersion="0"
           contactInfo="Writes to a database using ADO.NET provider.;Microsoft Corporation; Microsoft SqlServer v10; © 2007 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">

Second, remove the entire element. You can comment it out, as shown below, or just delete it.

< property id="26" name="UseBulkInsertWhenPossible" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether to use the SqlBulkCopy interface to improve the performance of bulk insert operations. Only certain providers support this interface." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">true

If you have a lot of packages to move back and forth, you probably don’t want to hand edit this each time. I wrote a little application that will process a folder of .dtsx files, and strip the 2008 R2 information out. This is done using an XSLT transform (available in the source code). The application and source are available to download from my SkyDrive.