Solved

SQL question regarding UPdatedBY or CreatedBY

Posted on 2011-03-10
10
306 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

734 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