|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to implement transactionsI have the sp below. I use @@Rowcount to see if the name is already present in the database and if so pass a message to the program. Thanks for the help Stephen K. Miyasato CREATE PROCEDURE REG_InsertPat --Will check for duplicte Names and if present will will not add name into the Names files @PatNo int, @AcctNo int, @AcctypeIns varchar(2), @Name varchar(30), @NameBirth varchar(30), @Birthdate dateTime, @SocSec varChar(10), @Provider varChar(2), @ReMD int, @Sex varchar(1), @Marital varchar(50), @Employ varChar(50), @Occupation VarChar(50), @Race varChar(50), @Email varChar(50) AS SET NOCOUNT ON DECLARE @FullName varchar(30) SET @FullName = (Select TOP 1 [Name] from Names where [Name] like @NameBirth +'%' ) IF @FullName is null --BEGIN TRANSACTION BEGIN insert into [Names] ([Name],AcctNo,PatNo,DateRegis, Sex, Status, provider ,LastUpdate,Birthdate) values (@NameBirth,@AcctNo,@PatNo,getDate(), @Sex, 'A',@provider ,getDate(),@Birthdate) Insert Into pat (PatNo, AcctNo, AcctypeIns, [Name], Birthdate, SocSec, Provider, ReMD, DateOpened, PatStatus, Sex,DateLastUpdated ) values (@PatNo, @AcctNo, @AcctypeIns, @Name, @Birthdate, @SocSec, @Provider, @ReMD, getdate(), 'A', @Sex, getdate()) Insert Into PatOther (PatNo, Marital, Employ, Occupation, Race, DateStamp, Email) values (@PatNo, @Marital, @Employ, @Occupation, @Race, getDate(), @Email) Insert Into Acct (AcctNo, AccType, [Name], DateOpened) values (@AcctNo, @AcctypeIns, @Name, getDate()) END RETURN @@Rowcount --COMMIT SET NOCOUNT OFF GO Stephen K. Miyasato wrote:
> I'm new to implementing transaction and can you help in this manner. You cannot commit transactions in a SP (at least in SQL Server 2k). Oh, > I have the sp below. > I use @@Rowcount to see if the name is already present in the database and > if so > pass a message to the program. and by the way: you don't *implement* transactions, you *use* them. Kind regards robert Hi Robert
By the way, you definitely CAN use transactions in a stored procedure, in any version. You can BEGIN, COMMIT or ROLLBACK. -- HTH Kalen Delaney, SQL Server MVP Show quote "Robert Klemme" <shortcut***@googlemail.com> wrote in message news:4mf5f3F5q2oiU2@individual.net... > Stephen K. Miyasato wrote: >> I'm new to implementing transaction and can you help in this manner. >> I have the sp below. >> I use @@Rowcount to see if the name is already present in the database >> and if so >> pass a message to the program. > > You cannot commit transactions in a SP (at least in SQL Server 2k). Oh, > and by the way: you don't *implement* transactions, you *use* them. > > Kind regards > > robert Kalen Delaney wrote:
> Hi Robert I know you can use them, but are they effective? When we used commit in > > By the way, you definitely CAN use transactions in a stored procedure, in > any version. You can BEGIN, COMMIT or ROLLBACK. a SP called via JDBC there was no real commit happening as SQL Server 2k treats this as a nested TX. Might be related to invocation via JDBC though. (Could be that the JDBC call implicitly opens a TX even for autoCommit=false.) It's been quite some while that we had this and we found another solution so I do not remember all the details. Kind regards robert What you describe sounds to me like a bug in the JDBC driver. I'd use Profiler to track that down.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Robert Klemme" <shortcut***@googlemail.com> wrote in message news:4mg7vmF62c1jU1@individual.net... > Kalen Delaney wrote: >> Hi Robert >> >> By the way, you definitely CAN use transactions in a stored procedure, in >> any version. You can BEGIN, COMMIT or ROLLBACK. > > I know you can use them, but are they effective? When we used commit in > a SP called via JDBC there was no real commit happening as SQL Server 2k > treats this as a nested TX. Might be related to invocation via JDBC > though. (Could be that the JDBC call implicitly opens a TX even for > autoCommit=false.) It's been quite some while that we had this and we > found another solution so I do not remember all the details. > > Kind regards > > robert AFAIK, tf you have autocommit = false, then the driver does do a begin tran
behind your back. See http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html. So you are correct, any Begin Transaction in the stored proc would be a nested transaction and the Commit doesn't really do a commit (although, rollbacks will do a rollback of everything the stored proc did PLUS anything that was done in this transaction before the stored procedure was called - and will raise an error if the tran count when leaving the stored proc won't be the same when you exit the procedure as when you entered). If you want the stored proc to handle the transaction, set autocommit = true before calling the stored proc. Tibor's suggestion to use Profiler is a good one (except I think the driver is working the way it is supposed to), you can see exactly what commands are being sent to your database. Tom Show quote "Robert Klemme" <shortcut***@googlemail.com> wrote in message news:4mg7vmF62c1jU1@individual.net... > Kalen Delaney wrote: >> Hi Robert >> >> By the way, you definitely CAN use transactions in a stored procedure, in >> any version. You can BEGIN, COMMIT or ROLLBACK. > > I know you can use them, but are they effective? When we used commit in a > SP called via JDBC there was no real commit happening as SQL Server 2k > treats this as a nested TX. Might be related to invocation via JDBC > though. (Could be that the JDBC call implicitly opens a TX even for > autoCommit=false.) It's been quite some while that we had this and we > found another solution so I do not remember all the details. > > Kind regards > > robert Tom Cooper wrote:
> AFAIK, tf you have autocommit = false, then the driver does do a begin tran Right, that's what we saw then.> behind your back. See > http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html. So > you are correct, any Begin Transaction in the stored proc would be a nested > transaction and the Commit doesn't really do a commit (although, rollbacks > will do a rollback of everything the stored proc did PLUS anything that was > done in this transaction before the stored procedure was called - and will > raise an error if the tran count when leaving the stored proc won't be the > same when you exit the procedure as when you entered). > If you want the stored proc to handle the transaction, set autocommit = true Hm, but isn't then also a TX started? Anyway, I'll check with the > before calling the stored proc. profiler what happens. > Tibor's suggestion to use Profiler is a good one (except I think the driver Good point. Although this is not a pressing issue to me ATM I will try > is working the way it is supposed to), you can see exactly what commands are > being sent to your database. that out once I find some time (I'm out of office this week, so if you are waiting for feedback you will have to be a little patient). Tom and Tibor, thanks for feedback and ideas! Kind regards robert Good catch, Tom. I god true and false for autocommit backwards. I tend to do that, "if you turn on
SET IMPLICIT_TRANSACTIONS, you turn off autocommit...". -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:JaSdneHAFbFibp_YnZ2dnUVZ_oydnZ2d@comcast.com... > AFAIK, tf you have autocommit = false, then the driver does do a begin tran behind your back. See > http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html. So you are correct, any > Begin Transaction in the stored proc would be a nested transaction and the Commit doesn't really > do a commit (although, rollbacks will do a rollback of everything the stored proc did PLUS > anything that was done in this transaction before the stored procedure was called - and will raise > an error if the tran count when leaving the stored proc won't be the same when you exit the > procedure as when you entered). > > If you want the stored proc to handle the transaction, set autocommit = true before calling the > stored proc. > > Tibor's suggestion to use Profiler is a good one (except I think the driver is working the way it > is supposed to), you can see exactly what commands are being sent to your database. > > Tom > > "Robert Klemme" <shortcut***@googlemail.com> wrote in message > news:4mg7vmF62c1jU1@individual.net... >> Kalen Delaney wrote: >>> Hi Robert >>> >>> By the way, you definitely CAN use transactions in a stored procedure, in any version. You can >>> BEGIN, COMMIT or ROLLBACK. >> >> I know you can use them, but are they effective? When we used commit in a SP called via JDBC >> there was no real commit happening as SQL Server 2k treats this as a nested TX. Might be related >> to invocation via JDBC though. (Could be that the JDBC call implicitly opens a TX even for >> autoCommit=false.) It's been quite some while that we had this and we found another solution so >> I do not remember all the details. >> >> Kind regards >> >> robert > > Check out the error handling articles at http://www.sommarskog.se/. Error handling is very closely
related to transaction handling. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Stephen K. Miyasato" <miya***@flex.com> wrote in message news:ODB0Nr50GHA.4228@TK2MSFTNGP06.phx.gbl... > I'm new to implementing transaction and can you help in this manner. > I have the sp below. > I use @@Rowcount to see if the name is already present in the database and if so > pass a message to the program. > > Thanks for the help > > Stephen K. Miyasato > > CREATE PROCEDURE REG_InsertPat > --Will check for duplicte Names and if present will will not add name into the Names files > @PatNo int, > @AcctNo int, > @AcctypeIns varchar(2), > @Name varchar(30), > @NameBirth varchar(30), > @Birthdate dateTime, > @SocSec varChar(10), > @Provider varChar(2), > @ReMD int, > @Sex varchar(1), > @Marital varchar(50), > @Employ varChar(50), > @Occupation VarChar(50), > @Race varChar(50), > @Email varChar(50) > AS > SET NOCOUNT ON > DECLARE @FullName varchar(30) > SET @FullName = (Select TOP 1 [Name] from Names where [Name] like @NameBirth +'%' ) > > IF @FullName is null > --BEGIN TRANSACTION > BEGIN > insert into [Names] > ([Name],AcctNo,PatNo,DateRegis, Sex, Status, provider ,LastUpdate,Birthdate) > values > (@NameBirth,@AcctNo,@PatNo,getDate(), @Sex, 'A',@provider ,getDate(),@Birthdate) > > Insert Into pat > (PatNo, AcctNo, AcctypeIns, [Name], Birthdate, SocSec, Provider, ReMD, > DateOpened, PatStatus, Sex,DateLastUpdated ) > values > (@PatNo, @AcctNo, @AcctypeIns, @Name, @Birthdate, @SocSec, @Provider, @ReMD, > getdate(), 'A', @Sex, getdate()) > Insert Into PatOther > (PatNo, Marital, Employ, Occupation, Race, DateStamp, Email) > values > (@PatNo, @Marital, @Employ, @Occupation, @Race, getDate(), @Email) > > Insert Into Acct > (AcctNo, AccType, [Name], DateOpened) > values > (@AcctNo, @AcctypeIns, @Name, getDate()) > > END > > RETURN @@Rowcount > --COMMIT > SET NOCOUNT OFF > > > GO > > This is my version after reading the link:
Any other suggustions? Thanks Stephen K. Miyasato ALTER PROCEDURE REG_InsertPat --Will check for duplicte Names and if present will will not add name into --the Names files @PatNo int, @AcctNo int, @AcctypeIns varchar(2), @Name varchar(30), @NameBirth varchar(30), @Birthdate dateTime, @SocSec varChar(10), @Provider varChar(2), @ReMD int, @Sex varchar(1), @Marital varchar(50), @Employ varChar(50), @Occupation VarChar(50), @Race varChar(50), @Email varChar(50) AS SET NOCOUNT ON DECLARE @FullName varchar(30), @err int SET @FullName = (Select TOP 1 [Name] from Names where [Name] like @NameBirth +'%' ) IF @@RowCount > 0 BEGIN ROLLBACK TRANSACTION RETURN @@RowCount END --Print '@FullName' --Print @FullName --PRINT '@err' --Print @err IF @FullName is null BEGIN TRANSACTION BEGIN insert into [Names] ([Name],AcctNo,PatNo,DateRegis, Sex, Status, provider ,LastUpdate,Birthdate) values (@NameBirth,@AcctNo,@PatNo,getDate(), @Sex, 'A',@provider ,getDate(),@Birthdate) IF @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END Insert Into pat (PatNo, AcctNo, AcctypeIns, [Name], Birthdate, SocSec, Provider, ReMD, DateOpened, PatStatus, Sex,DateLastUpdated ) values (@PatNo, @AcctNo, @AcctypeIns, @Name, @Birthdate, @SocSec, @Provider, @ReMD, getdate(), 'A', @Sex, getdate()) Insert Into PatOther (PatNo, Marital, Employ, Occupation, Race, DateStamp, Email) values (@PatNo, @Marital, @Employ, @Occupation, @Race, getDate(), @Email) IF @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END Insert Into Acct (AcctNo, AccType, [Name], DateOpened) values (@AcctNo, @AcctypeIns, @Name, getDate()) IF @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END END COMMIT TRANSACTION RETURN @@Rowcount IF @err = @@error IF @err <> 0 RETURN @err SET NOCOUNT OFF GO Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:O8y53q%230GHA.3656@TK2MSFTNGP04.phx.gbl... > Check out the error handling articles at http://www.sommarskog.se/. Error > handling is very closely related to transaction handling. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Stephen K. Miyasato" <miya***@flex.com> wrote in message > news:ODB0Nr50GHA.4228@TK2MSFTNGP06.phx.gbl... >> I'm new to implementing transaction and can you help in this manner. >> I have the sp below. >> I use @@Rowcount to see if the name is already present in the database >> and if so >> pass a message to the program. >> >> Thanks for the help >> >> Stephen K. Miyasato >> >> CREATE PROCEDURE REG_InsertPat >> --Will check for duplicte Names and if present will will not add name >> into the Names files >> @PatNo int, >> @AcctNo int, >> @AcctypeIns varchar(2), >> @Name varchar(30), >> @NameBirth varchar(30), >> @Birthdate dateTime, >> @SocSec varChar(10), >> @Provider varChar(2), >> @ReMD int, >> @Sex varchar(1), >> @Marital varchar(50), >> @Employ varChar(50), >> @Occupation VarChar(50), >> @Race varChar(50), >> @Email varChar(50) >> AS >> SET NOCOUNT ON >> DECLARE @FullName varchar(30) >> SET @FullName = (Select TOP 1 [Name] from Names where [Name] like >> @NameBirth +'%' ) >> >> IF @FullName is null >> --BEGIN TRANSACTION >> BEGIN >> insert into [Names] >> ([Name],AcctNo,PatNo,DateRegis, Sex, Status, provider >> ,LastUpdate,Birthdate) >> values >> (@NameBirth,@AcctNo,@PatNo,getDate(), @Sex, 'A',@provider >> ,getDate(),@Birthdate) >> >> Insert Into pat >> (PatNo, AcctNo, AcctypeIns, [Name], Birthdate, SocSec, Provider, >> ReMD, >> DateOpened, PatStatus, Sex,DateLastUpdated ) >> values >> (@PatNo, @AcctNo, @AcctypeIns, @Name, @Birthdate, @SocSec, @Provider, >> @ReMD, >> getdate(), 'A', @Sex, getdate()) >> Insert Into PatOther >> (PatNo, Marital, Employ, Occupation, Race, DateStamp, Email) >> values >> (@PatNo, @Marital, @Employ, @Occupation, @Race, getDate(), @Email) >> >> Insert Into Acct >> (AcctNo, AccType, [Name], DateOpened) >> values >> (@AcctNo, @AcctypeIns, @Name, getDate()) >> >> END >> >> RETURN @@Rowcount >> --COMMIT >> SET NOCOUNT OFF >> >> >> GO >> >> > I get an error on execution
The ROLLBACK TRANSACTION has no corresponding BEGIN TRANSACTION Any Idea on what I did wrong? Stephen K. Miyasato Show quote "Stephen K. Miyasato" <miya***@flex.com> wrote in message news:ut8JvtY1GHA.3752@TK2MSFTNGP02.phx.gbl... > This is my version after reading the link: > Any other suggustions? > > Thanks > > Stephen K. Miyasato > > > ALTER PROCEDURE REG_InsertPat > --Will check for duplicte Names and if present will will not add name > into > --the Names files > @PatNo int, > @AcctNo int, > @AcctypeIns varchar(2), > @Name varchar(30), > @NameBirth varchar(30), > @Birthdate dateTime, > @SocSec varChar(10), > @Provider varChar(2), > @ReMD int, > @Sex varchar(1), > @Marital varchar(50), > @Employ varChar(50), > @Occupation VarChar(50), > @Race varChar(50), > @Email varChar(50) > AS > > SET NOCOUNT ON > DECLARE @FullName varchar(30), > @err int > SET @FullName = (Select TOP 1 [Name] from Names where [Name] like > @NameBirth +'%' ) > > IF @@RowCount > 0 BEGIN ROLLBACK TRANSACTION RETURN @@RowCount END > > --Print '@FullName' > --Print @FullName > --PRINT '@err' > --Print @err > > IF @FullName is null > BEGIN TRANSACTION > BEGIN > insert into [Names] > ([Name],AcctNo,PatNo,DateRegis, Sex, Status, provider > ,LastUpdate,Birthdate) > values > (@NameBirth,@AcctNo,@PatNo,getDate(), @Sex, 'A',@provider > ,getDate(),@Birthdate) > > IF @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END > > Insert Into pat > (PatNo, AcctNo, AcctypeIns, [Name], Birthdate, SocSec, Provider, ReMD, > DateOpened, PatStatus, Sex,DateLastUpdated ) > values > (@PatNo, @AcctNo, @AcctypeIns, @Name, @Birthdate, @SocSec, @Provider, > @ReMD, > getdate(), 'A', @Sex, getdate()) > Insert Into PatOther > (PatNo, Marital, Employ, Occupation, Race, DateStamp, Email) > values > (@PatNo, @Marital, @Employ, @Occupation, @Race, getDate(), @Email) > > IF @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END > > > Insert Into Acct > (AcctNo, AccType, [Name], DateOpened) > values > (@AcctNo, @AcctypeIns, @Name, getDate()) > > IF @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END > > END > COMMIT TRANSACTION > > RETURN @@Rowcount > > IF @err = @@error IF @err <> 0 RETURN @err > > SET NOCOUNT OFF > > > GO > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in message news:O8y53q%230GHA.3656@TK2MSFTNGP04.phx.gbl... >> Check out the error handling articles at http://www.sommarskog.se/. Error >> handling is very closely related to transaction handling. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Stephen K. Miyasato" <miya***@flex.com> wrote in message >> news:ODB0Nr50GHA.4228@TK2MSFTNGP06.phx.gbl... >>> I'm new to implementing transaction and can you help in this manner. >>> I have the sp below. >>> I use @@Rowcount to see if the name is already present in the database >>> and if so >>> pass a message to the program. >>> >>> Thanks for the help >>> >>> Stephen K. Miyasato >>> >>> CREATE PROCEDURE REG_InsertPat >>> --Will check for duplicte Names and if present will will not add name >>> into the Names files >>> @PatNo int, >>> @AcctNo int, >>> @AcctypeIns varchar(2), >>> @Name varchar(30), >>> @NameBirth varchar(30), >>> @Birthdate dateTime, >>> @SocSec varChar(10), >>> @Provider varChar(2), >>> @ReMD int, >>> @Sex varchar(1), >>> @Marital varchar(50), >>> @Employ varChar(50), >>> @Occupation VarChar(50), >>> @Race varChar(50), >>> @Email varChar(50) >>> AS >>> SET NOCOUNT ON >>> DECLARE @FullName varchar(30) >>> SET @FullName = (Select TOP 1 [Name] from Names where [Name] like >>> @NameBirth +'%' ) >>> >>> IF @FullName is null >>> --BEGIN TRANSACTION >>> BEGIN >>> insert into [Names] >>> ([Name],AcctNo,PatNo,DateRegis, Sex, Status, provider >>> ,LastUpdate,Birthdate) >>> values >>> (@NameBirth,@AcctNo,@PatNo,getDate(), @Sex, 'A',@provider >>> ,getDate(),@Birthdate) >>> >>> Insert Into pat >>> (PatNo, AcctNo, AcctypeIns, [Name], Birthdate, SocSec, Provider, >>> ReMD, >>> DateOpened, PatStatus, Sex,DateLastUpdated ) >>> values >>> (@PatNo, @AcctNo, @AcctypeIns, @Name, @Birthdate, @SocSec, >>> @Provider, @ReMD, >>> getdate(), 'A', @Sex, getdate()) >>> Insert Into PatOther >>> (PatNo, Marital, Employ, Occupation, Race, DateStamp, Email) >>> values >>> (@PatNo, @Marital, @Employ, @Occupation, @Race, getDate(), >>> @Email) >>> >>> Insert Into Acct >>> (AcctNo, AccType, [Name], DateOpened) >>> values >>> (@AcctNo, @AcctypeIns, @Name, getDate()) >>> >>> END >>> >>> RETURN @@Rowcount >>> --COMMIT >>> SET NOCOUNT OFF >>> >>> >>> GO >>> >>> >> > > Stephen K. Miyasato wrote:
Show quote > I get an error on execution This ROLLBACK TRANSACTION is being called before the BEGIN TRANSACTION> The ROLLBACK TRANSACTION has no corresponding BEGIN TRANSACTION > > Any Idea on what I did wrong? > > Stephen K. Miyasato > > "Stephen K. Miyasato" <miya***@flex.com> wrote in message > news:ut8JvtY1GHA.3752@TK2MSFTNGP02.phx.gbl... > > This is my version after reading the link: > > Any other suggustions? > > > > Thanks > > > > Stephen K. Miyasato > > > > > > ALTER PROCEDURE REG_InsertPat > > --Will check for duplicte Names and if present will will not add name > > into > > --the Names files > > @PatNo int, > > @AcctNo int, > > @AcctypeIns varchar(2), > > @Name varchar(30), > > @NameBirth varchar(30), > > @Birthdate dateTime, > > @SocSec varChar(10), > > @Provider varChar(2), > > @ReMD int, > > @Sex varchar(1), > > @Marital varchar(50), > > @Employ varChar(50), > > @Occupation VarChar(50), > > @Race varChar(50), > > @Email varChar(50) > > AS > > > > SET NOCOUNT ON > > DECLARE @FullName varchar(30), > > @err int > > SET @FullName = (Select TOP 1 [Name] from Names where [Name] like > > @NameBirth +'%' ) > > > > IF @@RowCount > 0 BEGIN ROLLBACK TRANSACTION RETURN @@RowCount END > > Damien  |
|||||||||||||||||||||||
Other interesting topics