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.
Any advise highly appreciated !
View 2 Replies
ADVERTISEMENT
Feb 22, 2006
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')
View 7 Replies
View Related
Jun 4, 2008
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
View 11 Replies
View Related
Mar 7, 2005
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @StartDate and @EndDate
@StartDate Datetime = null
@EndDate Datetime = null
- 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
CREATE PROCEDURE SearchDocumentTable
@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null
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 :)
View 7 Replies
View Related
Mar 31, 2008
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.
View 1 Replies
View Related
Apr 19, 2014
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
[Code] ......
View 4 Replies
View Related
Nov 27, 2006
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.....
View 3 Replies
View Related
Jun 13, 2008
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
View 1 Replies
View Related
Oct 25, 2007
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)
Thank you, forum participants. You are the best!
View 4 Replies
View Related
Jan 8, 2005
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
create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
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)
Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--------- ---------------- --------------- ---------------- ----
HR 5 7 12 .84
Finance 10 15 25 1.75
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!
View 2 Replies
View Related
May 11, 2007
I have three tables:
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?
Kindly help me with this. Thanks in advance.
View 1 Replies
View Related
Apr 1, 2006
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.
i'm somewhat at a loss of where to begin.
View 6 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Nov 23, 2006
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
View 3 Replies
View Related
Oct 19, 2015
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies
View Related
Jun 10, 2006
hi
i want to know that how to manage the sql server transaction in a
big desktop networking application?
for example
there are 200 tables in
database. many of that tables have relatationship. i have a form in
frontend which holds nere ablut 50 tables deffrent calculated amount. i
want to ADD, EDIT and DELETE, SAVE(commit), CANCEL(roll back) them and
same time another use will do the same work from another pc. if there
are 100 user does the same work (add, edit, delete, save, cancel) how
could i magage my sql server data???
i also want to know that
1 kind of
proffessionsl way to use sql server and vb.net?
2 what kind of septs to
develop a desktop networking application with sql server?
3 how i could
manage the transaction in sql server when there are many-many user
working in the same database(add, edit, delete, save-commit,
cancel-rollback).<img src="images/emoticons/smile_baringteeth.gif">
View 1 Replies
View Related
May 27, 2008
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
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jul 20, 2005
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?
View 1 Replies
View Related
Jul 30, 2007
Hello,
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?
View 6 Replies
View Related
Jun 22, 2006
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!
View 2 Replies
View Related
Nov 10, 2006
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
View 1 Replies
View Related
Oct 17, 2007
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!
View 2 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Feb 7, 2008
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
View 2 Replies
View Related
Feb 7, 2008
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 TRY
INSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURN
END 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
View 3 Replies
View Related
Aug 21, 2006
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
View 3 Replies
View Related
Oct 15, 2014
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
[code].....
View 2 Replies
View Related
May 6, 2007
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.
View 3 Replies
View Related
Jan 12, 2004
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.
Sorry for my poor English.
Thanks for reading.
View 10 Replies
View Related
Nov 24, 2014
create proc proc1 (@param1 int)
as
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.
View 3 Replies
View Related
Feb 26, 2015
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?
View 6 Replies
View Related
Nov 29, 2005
I use the context connection for the "normal" work in the CLR procedure.
View 7 Replies
View Related
Feb 14, 2007
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 */
SELECT @PhotoID, @Location
RETURN
Thanks,
Jason
View 3 Replies
View Related
