Store Procedure LOCK. How To Solve The Problem Of Simultaneous Call?
Mar 20, 2007
Hello,
I have one store procedure that writes something data to one physical sql table 'MyTable' and on beginning I first call:
DELETE FROM MyTable
Procedure returns at the end data from 'MyTable' as on SELECT.
PROBLEM:
I started SQL Manager on my Laptop and on Computer near me and all pointed to same database (on Server) and I run that procedure simultaneously on Laptop and Computer at the same time (two hands on F5 button on these computers). Occasionally when clicking with both hands at the same time I got no records at the end of execution (on both computers), otherwise I got the results.
WHAT I THINK IS PROBLEM:
Somhow one SP start working and it did not come to the end and another computer called this SP and execute first command (DELETE FROM MyTable) and I got empty records at the end..
WHAT I NEED:
I need to prevent this situation to get empty records. All computers must get these records always. How I can use some sort of LOCKs to do this?
Or to do that on C# application level using LOCK command?
Hello:I run one process that calls the following the store procedure andworks fine.create PROCEDURE sp_GetHostSequenceNumASBEGINSELECT int_parameter_dbf + 1FROM system_parameter_dbtWHERE parameter_name_dbf = 'seqNum'UPDATE system_parameter_dbtSET int_parameter_dbf = int_parameter_dbf + 1WHERE parameter_name_dbf = 'seqNum'ENDGOIf I run two processes that call the above store procedure, I mightoccasionally get the dirty data of int_parameter_dbt. I guess that iscaused by two processes accessing to the same resource simultaneously.Is there any way to lock the store procedure call from MSSQL Serverand allow only one process to access it at a time?Thanks for help.Best Jin
I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
I have searched this forum for how to call a store procedure with parameter and so far no result has answered my question yet. I have the following store procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
-- Add the parameters for the stored procedure here
@strSearch varchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR [title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" + @strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR [content_nws] LIKE '%" + @strSearch + "%')
END
GO
I got this far in my code on how to call the parameter store procedure. string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar); I don't know what do next.
How can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
Hi, I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it. 1) I creat a class in Visual Basic.Net 2005, whose code is given below: Imports System Imports System.IO Imports Microsoft.VisualBasic Imports System.Diagnostics Public Class WLog Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String) Dim w As StreamWriter = File.AppendText(LogName) LogIt(newMessage, w) w.Close() End Sub Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter) wr.Write(ControlChars.CrLf & "Log Entry:") wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString()) wr.WriteLine(" :") wr.WriteLine(" :{0}", logMessage) wr.WriteLine("---------------------------") wr.Flush() End Sub Public Shared Sub LotToEventLog(ByVal errorMessage As String) Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog log.Source = "My Application" log.WriteEntry(errorMessage) End Sub End Class
2) Make & register its assembly, in SQL Server 2005. 3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile ( @LogName nvarchar(255), @NewMessage nvarchar(255) ) AS EXTERNAL NAME [asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile': System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied. System.UnauthorizedAccessException: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options) at System.IO.StreamWriter.CreateFile(String path, Boolean append) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize) at System.IO.StreamWriter..ctor(String path, Boolean append) at System.IO.File.AppendText(String path) at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are: tbltest
Here i am getting problem in //SqlParameter[] arProcParams = new SqlParameter("@RegionID",txtRegionID.Text);in btnDelete eventI have create a method for executing stored proc. Such that i only can pass procedure name and get resultI have used two field RegionID, and RegionName when suppose i want to delete any field i put RegionID and it will be deleted <asp:TextBox ID="txtRegionID" runat="server"></asp:TextBox><asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" /> SqlConnection mConn;SqlCommand mDataCom;SqlDataAdapter mDataAdapter; private void openConnection(){ if (mConn == null) { mConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); if (mConn.State == ConnectionState.Closed) mConn.Open(); mDataCom = new SqlCommand(); mDataAdapter = new SqlDataAdapter() mDataCom.Connection = mConn; }}private void closeConnection(){ if (mConn.State == ConnectionState.Open) mConn.Close(); } private void disposeConnection() { if (mConn != null) { if (mConn.State == ConnectionState.Closed) mConn.Dispose(); mConn = null; } }public int ExecuteStoredProc(string strProcName, SqlParameter[] arProcParams){ openConnection(); int returnValue; SqlParameter param = new SqlParameter(); mDataCom.CommandType = CommandType.StoredProcedure; mDataCom.CommandText = strProcName; mDataCom.Parameters.Clear(); foreach (SqlParameter LoopVar_param in arProcParams) { param = LoopVar_param; mDataCom.Parameters.Add(param); } returnValue = mDataCom.ExecuteNonQuery(); closeConnection(); disposeConnection(); return returnValue;}protected void btnDelete_Click(object sender, EventArgs e){openConnection();string strProcName = "RegionDelete";SqlParameter[] arProcParams = new SqlParameter("@RegionID",txtRegionID.Text);int n = ExecuteStoredProc("RegionDelete", arProcParams);closeConnection();disposeConnection();}
I have one procedure that gets executed many times per day(thousands at least). I consistently get blocking in my database, as users compile this stored procedure. How can I keep this from recompiling all the time, and bogging down my database? I tried the keep plan option in the portion of the code that uses tembdb, and that doesn't work. thanks.
Hello,Can we lock stored procedure until its execution is complete ?I dont want 2 clients to simultenously execute the stored procedure inSql Server 2000.My front end is ASP.net 1.0Thanks in advance.waiting for the reply.
I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.
I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.
The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.
The table looks like this: Logid PK LogTypeID -- what category LogValue -- LogTime -- when it occurred
My imaginary stored procedure looks something like this:
CREATE PROCEDURE ShrinkDB AS -- 1) "lock table log" -- do I have to do something like this?
-- 2) select * into log_keep FROM Log where ( logtypeid <> 2020 AND -- activity played logtypeid <> 5020 AND -- database connected -- ....etc et..... about 10 different things I don't need to keep or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours ) -- 3) drop table log
-- 4) EXEC sp_rename 'log_keep', 'log' GO
I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??
Hopefully it works that way, but if not I assume I'll run into these two problems:
- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?
- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?
Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject.... :-)
When I run the SQL Profiler to detect lock escalation, I noticed it detected a lot of lock escalation with the textdata indicating "create procedure" on certain stored procedure.
Hello!I am trying to investigate strange problem with particular storedprocedure. It runs OK for several days and suddenly we start gettingand lotof locks. The reason being [COMPILE] lock placed on this procedure. Asaresult, we have 40-50 other connections waiting, then next connectionusingthis procedure has [COMPILE] lock etc. Client is fully qualifyingstoredprocedure by database/owner name and it doesn't start with sp_. I knowthese are the reasons for [COMPILE] lock being placed. Is theresomethingelse that might trigger this lock? When troubleshooting this issue, Inoticed there was no plan for this procedure in syscacheobjects. Thestoredprocedure is very simple (I know it could be rewritten/optimized butourdeveloper wrote it):CREATE PROCEDURE [dbo].[vsp_mail_select]@user_id int,@folder_id int,@is_read bit = 1, --IF 1, pull everything, else just pull unread mail@start_index int = null, --unused for now, we return everything@total_count int = null output, -- count of all mail in specifiedfolder@unread_count int = null output -- count of unread mail in specifiedfolderASSET NOCOUNT ONselect m1.* from mail m1(nolock) where m1.user_id=@user_id andfolder_id=@folder_id and ((@is_read=0 and is_read=0) or (@is_read=1))orderby date_sent descselect @total_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and ((is_read=0 and@is_read=0)or (@is_read=1))select @unread_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and is_read=0GOI was monitoring server for a couple of day before and I am not surewhythis happens every 3-4 days only!Any help on this matter would be greately appreciated!Thanks,Igor
Hi All,I have a table that holds pregenerated member IDs.This table is used to assign an available member id to web sitevisitors who choose to register with the siteSo, conceptually the process has been, from the site (in ASP), to:- select the top record from the members table where the assigned flag= 0- update the row with details about the new member and change theassigned flag to 1- return the selected member id to the web pageNow I'm dealing with the idea that there may be brief, high trafficperiods of registration, so I'm trying to build a method (storedprocedure?) that will ensure the same member id isn't returned by theselect statement if more than 1 request to register happens at thesame instant.So, my question is, is there a way, once a record has been selected,to exclude that record from other select requests, within the boundsof a stored procedure?ie:- select statement is executed and row is instantly locked; any otherselect statement running at that exact moment will receive a differentrow returned and sill similarly lock it, ad nauseum for as manysimultaneous select statements as take place- row is updated with details and flag is updated to indicate themember id is no longer unassigned- row is released for general purposes etcIf what I'm suggesting above isn't practical, can anyone help meidentify a different way of achieving the same result?Any help immensely, immensely appreciated!Much warmth,Murray
Hi, I have an integration solution that backs into two databases. Database A is custom and Database B is integrated from a COTS product. The manufacturer of B supplies a formal interface as stored procedures (and I intend to honor that contract by limiting my access to B through those stored procs). Now, I have a search that is going to involve a subselect in the form of the following pseudocode:
select a,b from dbA.user.TableX where exists (select a from dbB.user.TableY)
I am trying to avoid creating 2 temporary cursors in the integration application and doing things in-memory (b/c of performance concerns).
So, my q is: How can I make this happen within database A?
Error: The Script returned a failure result. Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out. Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 4/30/2008 2:25:41 PM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ostat_CDS_rpt47_2006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ostat_CDS_rpt47_2006] GO
set @tb1= 'margin..ncds0421' set @tb2='Report.dbo.ob_apr08'
set @sql= ' insert into '+@tb2+' select yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,0 isCallback, cast(callcost as decimal (10,5)) cost,count(*) attempt, sum(case when connectflg=1 then 1 else 0 end) connected,sum(talktime) Talktime from '+@tb1+' where yyyy='+ltrim(str(@pYear))+' and mm='+ltrim(Str(@pMonth)) +' and dd between '+ltrim(Str(@pDay1))+' and '+ltrim(Str(@pDay2))+' and talktime >0 and trunkin is null group by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5)) order by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5)) ' --print (@sql) exec (@sql)
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help. CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate )
dear all i am using VS 2005 and sqlserver 2005.... i created one procedure [dbo].[Rptdailyactivity_jul](@cond Varchar(8000), @Type varchar(100),@groupname4fsumm varchar(100) ,@Fromdate4fsumm datetime, @todate4fsumm datetime) i want to call this procedure from VS how??? i tried based on the following code... usig the following code i can able to manipulate tables...but how i can able to call procedure based on this code??and how i will pass parameters??? ************ Dim Conomandb As New System.Data.SqlClient.SqlConnection("Data Source=HJIOertsv;Initial Catalog=tr;Integrated Security=True") If Conomandb.State = 1 Then Conomandb.Close() Conomandb.Open() Dim Comomandb1 As New System.Data.SqlClient.SqlCommand(strSQL, Conomandb) Dim rsOman1 As System.Data.SqlClient.SqlDataReader = Comomandb1.ExecuteReader() If rsOman1.Read() Then fghghfghf End If Comomandb1.Cancel() Comomandb1.Dispose() rsOman1.Close() ''*********************
I have created a stored procedure only with an insert statement in sql server 2005. How can i call this stored procedure through code in ASP.NET page using vb. i want to pass one parameter that comes from a text box in asp.net page. my emailid is: g12garg@yahoo.co.in pls reply. Thank you Gaurav
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong?? Thanks
