Nesting SQL Transactions

Mar 8, 2006

Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.

First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend. 

My SQL database is relational and is used to store names, addresses, etc of other companies. 

What I need to be able to do is have 2 transactions, one nested within the other.  In pseudo-code:

BEGIN TRANSACTION1



BEGIN TRANSACTION2

INSERT INTO COMPANY TABLE

COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2

Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit.  Therefore, I can't retrieve the companyID halfway through.

Is what I'm trying to do even possible?  Thanks in advance for the help!

 

 

View 2 Replies


ADVERTISEMENT

Nesting A SP Within Another SP?

Jul 20, 2005

I have a stored procedure that calls some UDF User Defined Functions,the purpose of which is to create row strings out of numerous columnstrings for matching uniqueIDs.The problem is I need to join that SP with some other tables.The SP I have reads something like:mySPName@myUserID intSELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningStringFROM myTEMPTableNameGROUP BY myUniqueIDWHERE myTEMPTableName.UserID = @myUserIDI need to join that result with myTableName on myUniqueID such as:Select myTableName.myField1, myTableName.myField2,mySPName.myRunningStringFrom ...-- joining myTableName.myUniqueID = mySPName.myUniqueIDCan this be done?The reason I don't just do it with a View instead of an SP is that Ihave that parameter that must be passed to filter the records inmyTEMPTableName.Any help is appreciated.lqoh...the UDF looks like:Create Function dbo.fn_myFunctionName(@myUniqueID as int) returnsnvarchar(500)ASBEGINDECLARE @ret_value nvarchar(500)SET @ret_value=''SELECT @ret_value=@ret_value + ';' + myStringFROM myTEMPTableNameWHEREmyUniqueID =@myUniqueIDRETURN RIGHT(@ret_value,Len(@ret_value)-2)END

View 1 Replies View Related

Nesting Cursors

May 20, 2004

Hi, i have to nest 2 cursors together and frankly i'm lost.

Here's what I have so far

CREATE PROCEDURE SeeAllColumns
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strTableVARCHAR(100)
DECLARE @strCommandVARCHAR(250)

SELECT @strMessage = 'SELECT ALL TABLES'
PRINT @strMessage
DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

OPEN crsTables
FETCH NEXT FROM crsTables INTO @strTable
WHILE (@@FETCH_STATUS = 0) BEGIN

SELECT @strMessage = 'SELECT ALL COLUMNS'
PRINT @strMessage

SELECT @strCommand = ' SELECT ' + @strColumn + ' FROM ' + @strTable

EXECUTE (@strCommand)

FETCH NEXT FROM crsTables INTO @strTable

END

DEALLOCATE crsTables


PRINT 'DONE'

What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
Can someone help me?

Thanks!

View 3 Replies View Related

Role Nesting

Dec 27, 2005

Hi,

I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.

So i build a admin
                         |
                        boss
                         |
                        worker
                         |
                         subworker

Nested Role

 

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

SELECT     ID, Label
FROM         dbo.tblUniqueLabel WITH (NOLOCK)
WHERE     (ID IN
                          (SELECT     dbo.tblUniqueLabel.ID
                            FROM          dbo.tblUniqueLabelMarking WITH (NOLOCK)
                            WHERE      (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.

What am i doing wrong?

Thanks.

View 1 Replies View Related

'un-nesting' Procedures

Apr 6, 2008

Hi!

If I have two procedures that call each other, I run out of the 32 level maximum recursion.

Is there a workaround to rewrite that without hitting the 32 level maximum?

Any help is appreciated!

-Bahman

View 9 Replies View Related

Nesting Parameters

Jun 12, 2007

I'd like to have a chart with a category "group on" expression like this:



=Fields![Parameters!MyParameter.Value].Value



The above doesn't work. Is it because it can't be done, or because I don't have the right syntax?

View 1 Replies View Related

Changing Connection Transactions To Database Transactions

May 22, 2005

Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try   'execute commands   myTransaction.Commit()Catch ex As Exception   Response.Write(ex.Message)   myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated

View 3 Replies View Related

Nesting Stored Procedure

Jul 23, 2004

hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:

CREATE PROCEDURE dbo.GetSharesTransactionsbyDates

(
@Startdate as char(10),
@Enddate as char(10)

)

AS

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @Startdate and @Enddate

I get complain that dbo is incorrect syntax


ST.Proc. which I try to call call is basicly a select statement with no parameters:

ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATE

View 4 Replies View Related

Nesting Case Statements...

Dec 13, 2001

I am wondering if there is a way that we could do a nesting case statement in an SQL Query?

This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END

What I want:

FicaWages = CASE
WHEN
('Sql Statement') = 'GRM'
THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END


I hope this is clear enough.

View 1 Replies View Related

SQL Query- Confused With Nesting...

Jul 20, 2005

It's me, one more time. My last request didn't quite filter all my records,I have one more field I need to evaluate in the script.DESCR TYPE SELL StartDate EndDate Number65048 04 Price A 4/21/2004 4/26/2004 3545665048 06 Price C 4/20/2004 4/27/2004 3545965048 08 Price B 4/22/2004 4/28/2004 3455965049 04 Price A 4/19/2004 4/24/2004 3859565049 06 Price B 4/22/2004 4/25/2004 3859465049 06 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 3811265050 06 Price B 4/18/2004 4/28/2004 3855065050 07 Price C 4/17/2004 4/29/2004 38110Descr, Type, Sell and Number are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with the followingcriteria:#1. "Date I Enter" >= Startdate#2. "Date I Enter" <= Enddate#3. Highest TYPE for each DESCR#4. Highest NUMBER for each TYPE/DESCR (the tie-breaker)Results for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/2004 3455965049 09 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 38112I REALLY appreciate the help!Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

View 1 Replies View Related

Strict Transaction Nesting Bug With CLR

Oct 27, 2006

Dear all,

I am constantly running into this error when calling CLR functions from triggers (even when the triggers are CLR functions themselves): The context transaction which was active before entering user defined routine, trigger or aggregate "[name of CLR sp]" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.

However, there is no cyclic nesting going on here. In fact, the error only appears when the CLR function is writing to the database. Only executing SELECT statements inside the statements doesn't freak out the SQL server (2005 SP1 in my case). I am not ending any transaction at all. This error shows up in many places: as exceptions from ASP.NET web pages, as errors in the msmerge_conflicts_info etc. Now I saw in the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457818&SiteID=1
..that this was supposedly a known bug in CLR. But in my case, it appears that I can't call a single CLR inside a trigger if the CLR is supposed to write something to the database, which is a pretty severe limitation of CLR imho.

I was trying to figure out if I can avoid this error by escaping the transaction context, I tried that inside CLR (Using New TransactionScope(TransactionScopeOption.Suppress)) but it didn't work. Also, calling COMMIT TRANSACTION before calling the CLR doesn't seem to solve the problem since it's not suppose to end the ambient transaction of the trigger according to BOL, and in many cases it doesn't seem wise to do so, e.g. if the trigger is fired in merge replication as in my case.

View 13 Replies View Related

SQL 2000 -&> SQL 2005 Nesting Error

Apr 27, 2007

I have sort of a weird error when attempting to create a database in SQL 2005 (migrating from SQL 2000 to SQL 2005).

I am able to run the CREATE PROC script just fine in SQL 2000, but I get this error in SQL 2005.



Msg 191, Level 15, State 1, Procedure udf_JCMS_ConcurrencyCheck, Line 2518
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries


I can't easily past up the whole script(it's 2655 lines), but it basically takes the following form:

CREATE FUNCTION dbo.Ugly (
@PK1 int,
@PK2 int,
@PK3 int,
@TableID int,
@UpdateBy char(30),
@LastUpdate datetime ) RETURNS BIT

DECLARE @UpdatedStatus as bit
DECLARE @RecordCount as smallint

SET @RecordCount = 0
If @TabelID = 1-- Comment about TableName
BEGIN

SELECT
@RecordCount =count(UNIQUE_ID)
FROM
dbo.MyTable
WHERE
UNIQUE_ID = @PK1
ANDPROXY_ID = @PK2
ANDLTRIM(RTRIM(ISNULL(LAST_UPDATE_BY,'')))= LTRIM(RTRIM(ISNULL(@UpdatedBy,'')))
AND
(
isnull(LAST_UPDATE_DATE,'') = isnull(@UpdatedDt,'')
or
convert(datetime,convert(varchar(10),LAST_UPDATE_D ATE,101)
+ ' ' + convert(char(8), LAST_UPDATE_DATE,8)) = @UpdatedDt
)
END

If @TableID = 2 -- Comment about TableName
BEGIN
.
.
.
END

If @TableID = 3 -- Comment about TableName
BEGIN
.
.
.
END

...

If @TableID = 156 -- Comment about TableName
BEGIN
.
.
.
END


IF @RecordCount = 0
SET @UpdatedStatus = 1
ELSE
SET @UpdatedStatus = 0

RETURN @UpdatedStatus



First, THIS IS NOT MY CODE.

Second, it appears that they are trying to create a function to test if a record has been updated (I cut out a bit of that).

I don't really get why SQL 2005 throws an error here. Yes, the code is hideous and not something that I would write myself. But it's not like the function is calling itself recursively or anything.

Can anyone shed any light?

Regards,

hmscott

View 3 Replies View Related

Excel Export Error In RS200SP2 When Nesting Tables Within Matirx

Dec 5, 2006

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

View 5 Replies View Related

Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded

Aug 3, 2005

Hi all,

I have writen a Function which call's the same function it self. I'm getting the error as below.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.

CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

View 4 Replies View Related

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)

May 29, 2002

Hello,

I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


Please let me know what should be the reason?
Thanks,
Ravi

View 7 Replies View Related

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32

Dec 1, 2004

Hi,

I face this error when i try to run my store procedure.
The sample of store procedure as following:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON

DECLARE @COUNTER INT
SET @COUNTER = 0

DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END

OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)

SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END

CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF


anyone can help me identify this error?


Thanks


Regards,
Jojomay

View 1 Replies View Related

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)

Jan 9, 2006

Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?

View 1 Replies View Related

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32).

Oct 16, 2007

I have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?

View 4 Replies View Related

Nesting A Looping Query Withing A Looping Query

Mar 28, 2008

Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
     Category                    SubCategory           (PK)CategoryName      (PK) SubCategoryNameCategoryID                    SubCategoryIDDate                                Date                      (Just shows the date inserted)                                  (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?

View 5 Replies View Related

Transactions

Aug 13, 2003

Hi All,
Can anybody suggest me a website where I can find articles on Managing transactions with Sql server. Also a scenario where the transactions take place in a environment involving 2 different databases, Like the bank account and credit card transactions (specifically of 2 way kind)
Thanks

View 5 Replies View Related

Transactions

Apr 6, 2005

I have a web application with a shopping cart, how do I stop all the shopping cart transaction from going into the db log? Is this possible?  These are are only transient data movements, and will never be need to to restore to, and they are cause log bloat. Or is there a better way to stop log bloat?
 
Thanks

View 2 Replies View Related

TRANSACTIONS IN DTS

Apr 17, 2000

Hi All,

How can we change connection properties in a DTS pkg with connection?
You can loop through the connection count but the connection ID is not static one.So can’t rely on that.
Is there another way of changing connection properties?

Thanks in Advance

Barath

View 4 Replies View Related

Help With DTS Transactions

Sep 5, 2000

Hi all....

I am currently designing a DTS Package to import data that is processed daily into a large database.

I have to design the package such that if any step fails when importing, I roll back the entire transaction.

I have designed the package with this in mind, checked "join transaction if present" and "rollback transaction on failure" in all of the workflows. I have also made all workflows serialized.

However, when I run the package, it fails on one of the data pumps with the error:

Transaction context in use by another session.

Any ideas?

Thank you,

Brian

View 2 Replies View Related

I Want To See My Transactions

Dec 1, 2000

I am replicating (finally!!) and on my publishers agent history I can see it says xx transactions with xx commands were delivered. (xx being the number)
Where can I look to see what the transactions or commands are?

Is there a place the system stores this information?

View 1 Replies View Related

Max. Transactions

Oct 17, 2003

what is maximum limit of no. of transactions per sec. in sql server 2000

View 2 Replies View Related

Transactions

Sep 6, 2004

Is there a point to wrapping a single UPDATE or INSERT statement in an explicit TRANSACTION:


BEGIN TRANSACTION

INSERT INTO Table (...) VALUES (...)

COMMIT TRANSACTION


I understand ACID and concept of transactions. However, I thought they were only necessary for multi-statement operations. I'm maintaining code that does this and am wondering if this is necessary. Does SQL Server guarantee ACID for single statements? Are single UPDATE/INSERT statements prone to race condition like affects without using explicit transactions?

View 2 Replies View Related

Transactions

Apr 17, 2008

Are there any scenarios where an un-commited transaction would block further queries?

View 1 Replies View Related

Transactions

May 27, 2008

If you run the Begin Transaction code and then run a create such as an update query and you see that it effects the number of rows that you wanted it to effect is there a way to look at the actual data that changed before you Commit Transaction?

Thanks!

View 7 Replies View Related

Transactions/Sec

Jul 1, 2007

I have a table with around 240 columns and one of the column in the Table is the Inserttime ( DATETIME ) and I using a GETDATE() function in the stored Proc, when we insert data into the table. In the same Milli second 2007-06-27 09:32:58.303 , I have around 7600 records in the database. The Stored Proc is called for each Individual record and we don't bunch the transactions. Is this possible.

I did some bench marking on this server and I can insert only 700 - 800 records approx / sec on this particular table.

Thanks

View 6 Replies View Related

Transactions

Aug 1, 2007

I have a small database that I have been testing.I get an error about a transaction deadlock.The code is in stored procedures and I added transactions to the sp'sbut the error happened again.I wrapped the whole sp in just one transaction and I don't have anyindex on the tables.When I test just by running a program that sends 3 calls at a time itwill get a deadlocked transaction as I send 6 or 9 at a time.I am not sure how it can have a deadlocked transaction after I usedtransactions(begin and commit) in the sp's.Steve

View 4 Replies View Related

Transactions

Oct 8, 2007

I am working with transactions and use try catch to capture errors and in the event of an error i have to rollback the transaction. How can i perform this?, most of the errors which i forsee are either insertion of null values into non nullable columns or violation of Primary keys while inserting duplicates.
I started by coding the following way but it does not rollaback apparently the try catch does not work for above kind of errors..Can somebody help..



DECLARE @REPORTING_PERIOD VARCHAR(6)

BEGIN TRY

BEGIN TRANSACTION

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET @REPORTING_PERIOD =(Select REPORT_PERIOD_ID from dbo.T_REPORT_PERIOD where C_FLAG_ACTIVITY=1)


--Step 1


INSERT INTO [dbo].[T_COUNTRIES]

([C_COUNTRY]

,[LB_COUNTRY]

,[C_REGION]

,[FK_REPORTING_PERIOD])

SELECT [C_COUNTRY]

,[LB_COUNTRY]

,[C_REGION]

,@REPORTING_PERIOD

FROM [dbo].[IN_T_COUNTRIES]


--Step 2


INSERT INTO [dbo].[T_FLE]

([FK_P_FLE]

,[C_COSMOS]

,[LB_FLE]

,[C_PARETO]

,[C_OPCO_SCOPE]

,[C_LEVEL]

,[C_FLE_TYPE]

,[C_ACTIVITY]

,[F_MATERIAL]

,[C_MATERIAL_PRIORITY]

,[C_CALCULATION_METHOD]

,[F_CREDIT_RISK_MATERIALITY]

,[V_PARTICIPATION]

,[FK_REPORTING_PERIOD])

SELECT Null as [FK_P_FLE]

,[C_COSMOS]

,[LB_FLE]

,[C_PARETO]

,[C_OPCO_SCOPE]

,[C_LEVEL]

,[C_FLE_TYPE]

,@REPORTING_PERIOD

FROM [dbo].[IN_T_FLE]

COMMIT TRANSACTION


END TRY


BEGIN CATCH


SELECT

ERROR_NUMBER() as ErrorNumber,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage;

-- Test XACT_STATE for 1 or -1.

-- XACT_STATE = 0 means there is no transaction and

-- a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.

IF (XACT_STATE()) = -1

BEGIN

PRINT

N'The transaction is in an uncommittable state. ' +

'Rolling back transaction.'

ROLLBACK TRANSACTION;

END;

-- Test whether the transaction is active and valid.

IF (XACT_STATE()) = 1

BEGIN

PRINT

N'The transaction is committable. ' +

'Committing transaction.'

COMMIT TRANSACTION;

END;





END CATCH;


View 3 Replies View Related

Sql Transactions

Nov 8, 2006

Hello All,

When i am working with Transactions i got one doubt.

If i am inserting any records into a table with primary key if a transaction is rolled back i am finding one primary ID is missing. Is it so.

View 3 Replies View Related

Transactions

Apr 2, 2007

I've been searching around and haven't found anything that simply states what I want to know.



I want to use a transaction within my CLR Stored Proc, to do so I've got System.Transactions referenced and I can access the current transaction via Transaction.Current.



My questions are

Will there always be a current transaction?

Do I need to create a new transaction if one doesn't already exist?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved