Solved

SQL question regarding UPdatedBY or CreatedBY

Posted on 2011-03-10
10
308 Views
Last Modified: 2012-06-21
HEY All,

I have a column in my Table UpdatedBY or CreatedBY,here i want to insert my appliation name whenever the row is inserted or updated.

i found this link but still am not understand the whole concept

http://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes

some one will help me is much appreciated
0
Comment
Question by:Sha1395
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 9

Expert Comment

by:gery128
ID: 35107094
By Application name you mean your desktop/web application name or just the database object owner?
0
 

Author Comment

by:Sha1395
ID: 35114794
Hi Grey128,

Am trying to insert database object owner value, it think "CONTEXT_INFO" am not sure.

Thanks in Advance for your help
0
 
LVL 9

Accepted Solution

by:
gery128 earned 500 total points
ID: 35125473
Hello Sha1395,

From the link you provided, this SQL returns the database object owner correctly:

select COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

You have any difficulties getting object owner from this script?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:Sha1395
ID: 35126006
thanks grey128

Am trying to insert my application name,but gone thru msdn i found i can do thru CONTEXT INFO.
0
 

Author Comment

by:Sha1395
ID: 35126015
DECLARE  @CONTEXT_INFO varbinary(128)
select  @CONTEXT_INFO = cast('WebService' + space(128) as binary(128))

please guide me a going on right path ?
0
 

Author Comment

by:Sha1395
ID: 35126017
This is the value am getting

0x444C415765625365727669636520202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020.
0
 

Author Comment

by:Sha1395
ID: 35126029
DECLARE  @CONTEXT_INFO varbinary(128)
select  @CONTEXT_INFO = cast('WebService' + space(128) as binary(128))
update [Employment] set EmployeeNo = @EmployeeNo,   OriginalHireDate = @OriginalHireDate,	TerminationDate=@TerminationDate,ServiceDate=@ServiceDate,BusinessTitle=@BusinessTitle,	CreatedBy=@CONTEXT_INFO,DateUpdated=getdate()	where EmployeeNo = @EmployeeNo

End

Open in new window

0
 
LVL 9

Assisted Solution

by:gery128
gery128 earned 500 total points
ID: 35126796
why are you using 'WebService' string and casting it to binary? you want that table's (database) owner (something like 'dbo') or want some hard-corded string like 'WebService' ?
if you want to set it as something like 'WebService' then no need to use context info at all. You are declaring @Context_Info variable, not using in-built Context_Info in your code.

What is the datatype of CreatedBy column?

if you want to set some hard-coded value than use this update query:
update [Employment] set CreatedBy = 'WebService'   (not including all fields here for simplicity)

or if you want to set database owner (e.g. dbo) use this update query:
update [Employment] set CreatedBy = current_user (do not prefix @ with current_user)
0
 

Author Comment

by:Sha1395
ID: 35133057
Hey Grey128

Thanks again,i just want to use in built context info in my code.

But i just seen one example i was following the same,in a result i got the above code.

so what i suppose to modify in my code if want to use Context Info ?
0
 
LVL 9

Assisted Solution

by:gery128
gery128 earned 500 total points
ID: 35135392
Hello Sha1395,

you can declare and access context info like this:
Declare @con_info As nvarchar(50) = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

Now @con_info has database owner name, you can use like this:

update [Employment] set EmployeeNo = @EmployeeNo,   OriginalHireDate = @OriginalHireDate,      TerminationDate=@TerminationDate, ServiceDate=@ServiceDate, BusinessTitle=@BusinessTitle,      CreatedBy=@con_info, DateUpdated=getdate()      where EmployeeNo = @EmployeeNo
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question