[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Using transaction locks in SQL Server 2012

Posted on 2013-12-17
3
Medium Priority
?
536 Views
Last Modified: 2016-02-10
Hi Experts,
I have written a stored procedure that executes an SSIS package in my SQL Server 2012 database.  The SSIS package loads data from a file into a table when executed.  If there is data in the table, it deletes the existing data and then writes the new data to the table.  This was a great solution when only one user was using the stored procedure.  Now more users are using this feature, and could possibly use it simultaneously.  I need to somehow lock the execution of the stored procedure, if another user is in the middle of running it.  How can I do this?  Do I need to add syntax in my stored procedure that would prevent this from happening?

Thanks in advance,
mrotor
0
Comment
Question by:mainrotor
  • 2
3 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39724856
Rather than locking out the stored proc, you could add the username as a clustering key to the table.  When a new user runs the proc, only rows with that username are deleted.  That should allow multiple users to use the proc at the same time.  It's a lot of code and a real hassle to restrict procs to 1-up use.
0
 

Author Comment

by:mainrotor
ID: 39724880
That sounds like a good idea.  How would I add the username as a clustering key to the table?  How would I delete rows with only that user's name on it?  
Would I have to recreate my SSIS package (since that's where the deletion happens)?

thanks,
mrotor
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39725115
>> How would I add the username as a clustering key to the table? <<

Good q :-).  It depends; hopefully it's possible.

Check "SELECT ORIGINAL_LOGIN()" for each user and see what it's value is.  If that doesn't come in, maybe you can use "HOST_NAME()".  Or if you have their unique AD name.

Anything that gives you an absolutely unique string for that user.

Then, where your package currently does this:

DELETE FROM dbo.table_name

You change it to:

DELETE FROM dbo.table_name WHERE user_name_column = @current_user_name

or the equivalent.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

612 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