Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0216 - SQL Server - Use APP_NAME() to change application behavior based on connection properties

Nov 26 2012 12:00AM by Nakul Vachhrajani   

Recently, I wrote a couple of posts around some of the best practices that should be followed when working with Connection Strings. In case you missed them earlier, they are linked below for your kind reference:

In these posts, I mentioned that the application name parameter (and connection strings in general) can be used to alter the behavior of an application. One of the follow-up questions I received was around this topic, and today, I would briefly demonstrate a practical use of this functionality.

How can you modify the application behavior by using the application name parameter of a connection string?

A common requirement that I have encountered in many home-grown enterprise systems which share the same database is to exhibit different auditing behavior based on which application or interface is inserting/updating a piece of data. Most legacy applications implement auditing via database triggers, and checking for the application name in these triggers is the solution to realizing this requirement. Today, I will explore with you how the APP_NAME() function can be used to alter the behavior of an application based on the application name set by the connection string.

Using APP_NAME()

Consider the following trigger that is executed whenever an Employee record is updated. The only purpose of this is to audit the changes made to the Employee record by applications other than the “MyEnterpriseProduct”. These updates could be through interfaces or through any other applications that the organization may be using.

This demo will walk you through using the APP_NAME() function, which returns the application name for the current session if set by the application, within a DDL trigger.

The following code creates an auditing table followed by an update trigger on the Employee table.

USE AdventureWorks2008R2
GO

--Step 01: Prepare the environment
--       : Create the auditing table
--       : Create the update trigger
IF OBJECT_ID('HumanResources.uEmployee','TR') IS NOT NULL
BEGIN
    DROP TRIGGER [HumanResources].[uEmployee];
END
GO

IF OBJECT_ID('HumanResources.Audit') IS NOT NULL
BEGIN
    DROP TABLE [HumanResources].[Audit];
END
GO

CREATE TABLE [HumanResources].[Audit] (AuditDate DATETIME,
                                       BeforeJobTitleValue VARCHAR(100),
                                       AfterJobTitleValue VARCHAR(100),
                                       ApplicationName VARCHAR(100)
                                      );
GO

CREATE TRIGGER [HumanResources].[uEmployee]
ON [HumanResources].[Employee]
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @appName VARCHAR(100);
    SET @appName = APP_NAME();
    
    IF (@appName != 'MyEnterpriseProduct')
    BEGIN
        INSERT INTO [HumanResources].[Audit] 
               (AuditDate, BeforeJobTitleValue, AfterJobTitleValue, ApplicationName)
        SELECT GETUTCDATE() AS AuditDate, 
               deleted.JobTitle AS BeforeJobTitleValue, 
               inserted.JobTitle AS AfterJobTitleValue, 
               @appName AS ApplicationName
        FROM [HumanResources].[Employee] AS Employee
        INNER JOIN deleted ON deleted.BusinessEntityID = Employee.BusinessEntityID
        INNER JOIN inserted ON deleted.BusinessEntityID = inserted.BusinessEntityID
        WHERE Employee.BusinessEntityID = inserted.BusinessEntityID;
    END
END
GO

Now, using the methods described in the post “How to test your Connection String using SSMS?”, establish two connections from the SSMS to the test AdventureWorks database – one using the Application Name – “MyEnterpriseProduct” and other using “OtherApplication”. Execute the queries shown below using the respective connections:

--Step 02: Using a connection with Application Name = "MyEnterpriseProduct",
--         update the Employee record
--         Confirm that no auditing has been performed
USE AdventureWorks2008R2
GO
--Chief Executive Officer
UPDATE HumanResources.Employee SET JobTitle = 'CEO' WHERE BusinessEntityID = 1;
GO

SELECT NULL AS AuditDate, 
       NULL AS BeforeJobTitleValue, 
       NULL AS AfterJobTitleValue,
       APP_NAME() AS ApplicationName
UNION
SELECT AuditDate, 
       BeforeJobTitleValue, 
       AfterJobTitleValue, 
       ApplicationName
FROM [HumanResources].[Audit];
GO

--Step 03: Using a connection with Application Name = "OtherApplication",
--         update the Employee record
--         Confirm that auditing has been performed
USE AdventureWorks2008R2
GO
--Chief Executive Officer
UPDATE HumanResources.Employee SET JobTitle = 'Chief Executive Officer' WHERE BusinessEntityID = 1;
GO

SELECT NULL AS AuditDate, 
       NULL AS BeforeJobTitleValue, 
       NULL AS AfterJobTitleValue,
       APP_NAME() AS ApplicationName
UNION
SELECT AuditDate, 
       BeforeJobTitleValue, 
       AfterJobTitleValue, 
       ApplicationName
FROM [HumanResources].[Audit];
GO

Here’s what one would see in the two different connections:

Query Window Audit Date BeforeJobTitleValue AfterJobTitleValue Application Name
1 NULL NULL NULL MyEnterpriseProduct
2 NULL NULL NULL OtherApplication
2 11/16/2012 CEO Chief Executive Officer OtherApplication

0216

Notice that in the second result set (in the connection with Application Name set to “OtherApplication”, the BeforeJobValue is set to “CEO”, indicating that the first query did actually update the data, but the activity was not logged.

Finally, it’s cleanup time!

--Step 04: Cleanup!
USE AdventureWorks2008R2
GO
IF OBJECT_ID('HumanResources.uEmployee','TR') IS NOT NULL
BEGIN
    DROP TRIGGER [HumanResources].[uEmployee];
END
GO

IF OBJECT_ID('HumanResources.Audit') IS NOT NULL
BEGIN
    DROP TABLE [HumanResources].[Audit];
END
GO

Conclusion

As can be seen in the demonstration above, we were able to change the behavior of the auditing mechanism built into the application based on the parameter (Application Name) supplied within the connection string. Connection strings are therefore very powerful, and I urge all readers to go back, explore and share the results of your experiments through your comments on this post.

Disclaimer: The above example is only intended to demonstrate the use of the system function APP_NAME() and should not be used as a guidance for designing an auditing solution. An auditing solution requires capturing many more parameters and is outside the scope of this discussion.

Until we meet next time,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, Administration, Tips, Development, DBA, T-SQL, #TSQL, Best Practices, Guidance, HowTo


Nakul Vachhrajani
4 · 36% · 11645
8
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • You may be interested to read this too http://beyondrelational.com/modules/2/blogs/70/posts/10908/sql-server-appname-function-find-out-which-application-has-modified-your-data.aspx

    commented on Nov 26 2012 11:02PM
    Madhivanan
    3 · 40% · 12947
  • Thank-you, Madhivanan! It's nice & simple :)

    commented on Nov 27 2012 2:58AM
    Nakul Vachhrajani
    4 · 36% · 11645

Your Comment


Sign Up or Login to post a comment.

"#0216 - SQL Server - Use APP_NAME() to change application behavior based on connection properties" rated 5 out of 5 by 8 readers
#0216 - SQL Server - Use APP_NAME() to change application behavior based on connection properties , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]