How To Manage Stored Procedure Transaction Involving Update In Several Tables
Jul 22, 2004
I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.
Is it any solution to rollback transactions update already done before
the code breaks due to error ?
I was thinking about combining all stored proc on a big one and use
Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
I seem to have a little problem with my SQL. I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS). It's a very simple search setup. The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result. Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items). This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested. But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS. Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL. Now this is where it starts playing up. The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount 2 ( 3 @SearchQuery nvarchar 4 ) 5 AS 6 SET NOCOUNT ON 7 8 SELECT COUNT(*) 9 FROM StoreName_Products 10 WHERE Title LIKE '%' + @SearchQuery + '%' Nice and Easy. Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005 and running a 'New Query', it retrieves the correct results. (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system. Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number. So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested. Bingo. It's returning the full number of the products.So my question is... What's the deal? How can the same query return two completely different results? and most of all... How do I overcome this? Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL? I'm a bit confused. Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke
- Since the parameters are optional user can enter either one or can leave both blank. - If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL - If user enters SD, query should use @StartDate as the SD and GetDate() as the ED - If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs The query is as below
AS IF ( @StartDate IS NULL) Select @StartDate = MIN(DateInputted) from Document
Select FName as 'First Name', LName as 'Last Name', ID as 'Student ID', Orphan as 'Orphan', DocumentType as 'Document Type', DocDesc as 'Description of the Document', DateInputted as 'Date Entered', InputtedBy as 'Entered by'
From Document,DocumentTypeCodes Where FName LIKE ISNULL(@FName,'%') AND LName LIKE ISNULL(@LName,'%' + NULL) AND ID LIKE ISNULL(@ID,'%' + NULL) AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL) AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated Thanks in advance :)
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes
Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated
Here is the SQL code :
ALTER procedure [dbo].[UpdateEmp] @EmpName nvarchar(100), @Nationality nvarchar(30), @Passport nvarchar(20), @ContractDate date, @HealthDate date
hello all....can anyone tell me how can i manage the stored procedure permissions in SQL Server Express. Untill now i have developed in SQL Server 2000 and there i used SQL Enterprise Manager for this problem..in SQL Express i can't handle it...Manny thanks.....
Hi all,can somebody help to write this stored procedure Table1 Table2LogID MigIDUserMove LogIDUserNew Domain User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up. The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser]( [AutoNumber] [int] IDENTITY(1,1) NOT NULL, [SubmittedDate] [datetime] NULL, [FirstName] [varchar](100) NOT NULL, [LastName] [varchar](100) NOT NULL, [Department] [varchar](100) NOT NULL, [StartDate] [datetime] NULL, [DepartDate] [datetime] NULL [RequestedBy] [varchar](100) NOT NULL, [UpdatedBy] [varchar](16) NOT NULL, [CurrentStatus] [varchar](10) NOT NULL , [DateCurrentStatus] [datetime] NULL, [FormType] [varchar](10) NOT NULL, ) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing. I want to do this for each record: Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user Convert Department to a 4 character department number Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser SET DateCurrentStatus = b.DateMostRecent, CurrentStatus = (b.FormType + a.Department CASE WHEN 'Roads Department' THEN '3000' WHEN 'Engineering and Survey Services' THEN '1900' WHEN 'Waste Management' THEN '8999' WHEN 'Kern Air Pollution Control District' THEN '9149' WHEN 'Environmental Health' THEN '4113' WHEN 'Building Inspection' THEN '2625' WHEN 'Animal Control' THEN '2760' WHEN 'Planning Department' THEN '2750' WHEN 'Community and Economic Development' THEN '5940' WHEN 'Resource Management Agency' THEN '2730' WHEN 'Code Compliance' THEN '2620' WHEN 'Roads Kern Regional Transit' THEN '8998' END) FROM HelpDesk_NewUser a JOIN UserMostRecent b ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl]( [ID] int IDENTITY(1,1) PRIMARY KEY, [Name] varchar(100), [Password] varchar(100), ) go
create table [Printer_Tbl( [ID] int IDENTITY(1,1) PRIMARY KEY, [Name] varchar(100), [PaperCost] int ) go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
1. RubricReport 2. RubricReportDetail and 3. RubricReportTemplate
RubricReportDetail has columns ReportID and Age RubricReportTemplate has columns IndicatorID and Salary
I want to insert a row in RubricReport Table with ReportID = @ReportID (RubricReportDetail) and IndicatorID = @IndicatorID (of RubricReportTemplate). How can I do this?
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
Hi,I am quite puzzled how SQLServer manages transactions.Whatever the isolation level I set when performing an insertion, otherconnections do not have access to the table in select mode.Example in SQL Analyzer:create table foo (id numeric(10),data varchar(100))On Connection 1SET TRANSACTION ISOLATION LEVEL READ COMMITTEDGOBEGIN TRANSACTIONinsert into foo(id,data) values (1,'data');On Connection 2SET TRANSACTION ISOLATION LEVEL READ COMMITTEDselect * from foo-> QUERY HANGSOn Connection 1COMMITOn Connection 2Get the resultUsing READ COMMITTED level, I was expecting not to lock the table whenperforming the select.Thanks in advance for your help,Cedric
hi need help how to send an email from database mail on row update from stored PROCEDURE multi update but i need to send a personal email evry employee get an email on row update like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
I have a stored procedure that registers a user. It also checks whether an email or username exists before registering them.Does this need to be in a transaction to prevent duplication of email/username? The data is highly sensitive and should not be duplicated at all.I tried writing methods in .net like CheckEmail() and CheckUsername() but that probably wont protect against duplicates if 2 people submit at the same time on a busy server. So the next best thing is a stored procedure but should it be in a transaction or not is what I'm wondering.Thanks again,you guys are great!
I have a function in which I use a stored procedure and a transaction.I use the following code, but I get the exception e (last try-catch): This SqlTransaction has completed; it is no longer usable.(btw, the SendEmail function currently returns False, so the transaction should ALWAYS roll back) Public Shared Function SaveGBEntry(ByVal myGBEntry As GBEntry, ByVal Language As String) As Boolean Dim bSuccess As Boolean = False Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewGBEntry", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True Dim UserCodeOwner As Integer = UserFunctions.GetUserCode(myGBEntry.UserNameOwner) Dim OwnerEmail As String = UserFunctions.GetUserEmail(myGBEntry.UserNameOwner) cmd.Parameters.Add(New SqlParameter("@UserCodeSender", myGBEntry.UserCodeSender)) cmd.Parameters.Add(New SqlParameter("@GBText", myGBEntry.Text)) cmd.Parameters.Add(New SqlParameter("@GBUpdateDate", myGBEntry.UpdateDate)) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCodeOwner)) cmd.ExecuteNonQuery() myTrans.Commit() If MailFunctions.SendEmail(myGBEntry.UserNameOwner, OwnerEmail, ConfigurationManager.AppSettings("emailINFOname"), ConfigurationManager.AppSettings("emailINFOAddress"), True, "NewGBEntry", Language) Then bSuccess = True fBeginTransCalled = False End If Catch ex As Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", ex.Message) Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", e.Message) End Try End If MyConnection.Close() End Try Return bSuccess End Function
hello, i'm executing a select statement inside the BEGIN TRANSACTION block on a Stored Procedure. my question is, does it lock the table? actually, i'm getting the maximum value of a field and i don't want other users to read until the transaction is done. do i still need to use tablockx to manually lock the table or does the transaction handles the locking? please help thanks!
i have to update two tables from ASP pages with same data but i wantthat both of them should be updated at one time. If either of them isnot updated then my transaction should roll back.I want this thing tobe in a stored procedure. so that i have to write an execute statementonly on the ASP page and pass the parameters.Looking forward for ur replyDEEPAK
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRYINSERT INTO [syl_Categories] VALUES( @CategoryName, @LanguageID) SELECT SCOPE_IDENTITY() AS [CategoryID] RETURNEND TRY BEGIN CATCH --Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1 END CATCH END
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
I have a stored procedure I want to use in a transaction...I have no idea where the problem is, since I dont receive an error...but tblMessages and tblUsersAndMessages are not updated..(hence something is wrong ;))...it might be the sp or just my code...anyway..here's all the data:STORED PROCEDUREset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo ALTER PROCEDURE [dbo].[spNewMessage]@MessageID int,@UserIDSender uniqueidentifier,@MessageTitle nvarchar(50),@MessageContent text,@MessageType int,@UserID uniqueidentifier ASBegin Set NoCount on DECLARE @WhateverID INT INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType) SET @WhateverID=SCOPE_IDENTITY() INSERT INTO tblUsersAndMessages(MessageID,UserID)VALUES (@WhateverID,@UserID) End
TABLE DEFINITIONtblUsersAndMessages allow nullsMessageID int falseUserID uniqueidentifier falseNew bit false *default set to ((1)) tblMessages allow nullsMessageID int false *PKUserIDSender uniqueidentifier falseMessageTitle nvarchar(50) trueMessageContent text trueSentDateTime datetime false * default set to (getdate())MessageType int false THE CODE Public Shared Function SendMessage(ByVal SenderName As String, ByVal To As String), ByVal MessageTitle As String, ByVal MessageContent As String, ByVal MessageType As String) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewMessage", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True Dim UserIDSender As Guid = UserFunctions.GetUserID(SenderName) Dim UserIDReceiver As Guid = UserFunctions.GetUserID(To) 'create message cmd.Parameters.Add(New SqlParameter("@UserIDSender", UserIDSender)) cmd.Parameters.Add(New SqlParameter("@MessageTitle", MessageTitle)) cmd.Parameters.Add(New SqlParameter("@MessageContent", MessageContent)) cmd.Parameters.Add(New SqlParameter("@MessageType", CInt(MessageType))) 'userid receiver cmd.Parameters.Add(New SqlParameter("@UserID", UserIDReceiver)) cmd.ExecuteNonQuery() myTrans.Commit() bSuccess = True fBeginTransCalled = False Catch ex As Exception Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I want to use 3 transactions in a single stored procedure.First it should update the value and then if the 1st transaction executed successfully then 2nd transaction should start,if 2nd trans executed successfully then 3rd trans should execute.After that only i want to commit all the transactions that is 1,2,3.If any of the trans fails the other shouldn't execute.How do i do this?
USE [recruit] GO /****** Object: StoredProcedure [dbo].[Import] Script Date: 10/15/2014 17:13:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Proc [dbo].[Import] @CustId int
Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful? Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records ======================================= create stored procedure combine_sp as begin call delete_sp -- have rollback transaction in the delete_sp -- what to do if following has error occured, but we already deleted the records above? call insert_sp -- have rollback transaction in the insert_sp end go ======================================= Thanks a lot.
Hi. I am managing the SQL script files for constructing tables,views,and so on with Visual Source Sefe.
But once I run these scripts with a tool like Query Analyzer for the database in SQL Server, I do not know How to get the versions of the scripts which were installed now, with using Enterprise Manager or any tool associated with SQL Server2000.
Please tell me How are you managing and know which version of scripts have been installed on database in SQL Server.
begin try declare @param2 int begin transaction exec proc2 @param2 commit transaction end try begin catch if @@trancount > 0 rollback transaction end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[AddPhoto] @AlbumID int, @Caption nvarchar(MAX) AS INSERT INTO [Photos] ( [AlbumID], [Caption], [Location], [LastModified]) VALUES ( @AlbumID, @Caption, 'tmpLocation', /* tmpLocation needed because app broke when Location column set to Allow NULLs */ GetDate()) /* Retrieve generated PhotoID */ DECLARE @PhotoID int SET @PhotoID = SCOPE_IDENTITY() /* Build unique location path from album and photo ID */ DECLARE @Location nvarchar(MAX) SET @Location = '' + CONVERT(nvarchar(10), @AlbumID) + '' + CONVERT(nvarchar(10),@PhotoID) + '.jpg' /* Update photo with new location path */ UPDATE [Photos] SET [Location] = @Location WHERE [PhotoID] = @PhotoID
/* Update photo with new location path */ ******************************************The code never executes the statement below******************************************** INSERT INTO [PhotoDefault] ( [pidm], [defaultPhoto], [activityDate]) VALUES ( '1234', 'test', getdate() ) /* Return PhotoID and Location */
