Friday, March 8, 2013

[T-SQL] Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)?

[T-SQL] Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)?

Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)?

Posted: 27 Feb 2013 01:37 AM PST

Hi all, Below are two scenarios, however, what I am trying to accomplish is to catch deadlock errors and re-try the DML that was chosen as the deadlock victim. Does anyone know of way to accomplish what I am trying to accomplish without a doomed transaction given the constraints? I have seen many posts that this appears to be a limitation of the dbengine, but am looking for confirmation. In both scenarios, I have a SProc - let's called it "X" - that is called mostly from within triggers (that performs some denormalized calculations and cannot be changed at this time) - in which deadlocks are often occurring. Scenario 1 •In SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error•However, whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction" Scenario 2 •In SProc Y, that runs DML statements that will fire triggers that call SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error•If SProc Y is called outside any transaction, I can begin a new transaction and rollback if a deadlock occurred and successfully retry the DML•However, if SProc Y is called inside a transaction, I need to perform a SAVE TRAN and rolback to the savepoint. Whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.". For my logic to work, I cannot rollback the entire transaction, so SProc Y only performs as expected outside a transaction TIA, Dave

XML nodes extraction

Posted: 07 Mar 2013 09:02 PM PST

Hi, Thanks for your help in advance, working with XML nodes for the first time and finding it frustrating. I am trying to build a relational table from some xml. setting each xml level to a new column in a table.[code="sql"]CREATE TABLE ClientInfo( ID INT PRIMARY KEY IDENTITY, XMLInfo XML);INSERT INTO ClientInfo (XMLInfo)VALUES(N'<?xml version="1.0" ?><Root> <key>29</key> <children> <key>500</key> <children> <key>3000</key> <children> <key>70000</key> </children> </children> </children> <key>50</key> <children> <key>600</key> <children> <key>3600</key> <children> <key>9862</key> </children> <children> <key>9863</key> </children> <children> <key>9864</key> </children> </children> </children> <key>70</key> <children> <key>900</key> <children> <key>3700</key> <children> <key>7862</key> </children> <children> <key>6863</key> </children> <children> <key>5864</key> </children> </children> <children> <key>5400</key> <children> <key>7892</key> </children> <children> <key>6563</key> </children> <children> <key>5464</key> </children> </children> </children></Root>');select * from [dbo].[ClientInfo]SELECT aref.value('(key/text())[1]', 'varchar(50)') as Column1, bref.value('(key/text())[1]', 'varchar(50)') as Column2, cref.value('(key/text())[1]', 'varchar(50)') as Column3, dref.value('(key/text())[1]', 'varchar(50)') as Column4FROM ClientInfo CROSS APPLY XmlInfo.nodes('//Root') AS Level1(aref) CROSS APPLY aref.nodes('children') AS Level2(bref) CROSS APPLY bref.nodes('children') AS Level3(cref) CROSS APPLY cref.nodes('children') AS Level4(dref)GO[/code]When I run this query column 1 just has the first ID in this case 29 ? I would very much like it to report the other key's adjacent to their relevant children keys. Why is this not working for me.Any useful material you can forward on would be gratefully appreciated.Many Thanks,Oliver

Syntax error when using IN statement inside inner query

Posted: 07 Mar 2013 06:59 PM PST

Hi,I'm getting a syntax error on the following query designed to return the newest status from a one to many relationship based on a dynamic list of IDs passed via an IN statement. The inner query runs fine on its own, as does the entire query when the IN statement is removed. The error I keep getting is "Error in list of values in IN clause. Unable to parse query text." Any assistance with this would be greatly appreciated.SELECT ToBeActionedBy, COUNT(ToBeActionedBy) AS ActionCount FROM (SELECT (SELECT TOP (1) ToBeActionedBy FROM dbo.PStatus AS PStatus_1 WHERE (POID = dbo.POrder.ID) ORDER BY ID DESC) AS ToBeActionedBy FROM dbo.POrder INNER JOIN dbo.Address ON dbo.POrder.Company_Name = dbo.Address.Company_Name WHERE (AccountNumber IN ('PARISH') AND (dbo.Address.ID IN (11743,11899)) AND (Complete = 0)) AS A WHERE (ToBeActionedBy Is Not NULL) GROUP BY ToBeActionedBy

Time Problem

Posted: 07 Mar 2013 01:27 AM PST

Hi,I'm trying to figure out a way to calculate a weighted average, but having trouble with the time part.Here are some example records:Time Value2013-03-02 13:40:00 502013-03-02 14:00:00 1002013-03-02 14:20:00 2002013-03-02 14:30:00 1502013-03-02 14:50:00 3002013-03-02 15:00:00 200What I'm trying to do is figure a way to find the minute differences by each hour. For example:Time Minute Difference2013-03-02 14:00:00 202013-03-02 14:20:00 202013-03-02 14:30:00 102013-03-02 14:50:00 202013-03-02 15:00:00 102013-03-02 17:00:00 120Could someone please help me?Thank you.

Urgent help need to verify DateDiff is correct?

Posted: 07 Mar 2013 06:35 AM PST

Hi Friends,my requirement is- "SmartLaborII Work Order End Date" is NOT greater than "SmartLaborII Previous Work Order End Date"]-and the code I'm using is-//datediff(day,[WorkForce_WorkOrder].[Work Order End Date_WO] ,[WorkForce_WorkOrder].[Previous Work Order End Date_WO] ) >= '0'//Since I'm new to TSQL world, wanted to know if my above code for my requirement is correct ? if not what should be the right code and why please?Kind RegardsDhananjay

Query Help

Posted: 07 Mar 2013 06:49 AM PST

Hi, I am in need of some Sum and Grouping help. The below query is returning multiple rows and I only want one returned With the open amounts Summed and 1 voucher amount representing a summary of the three below rows.Thanks for any help you can provide.[IMG]http://i1306.photobucket.com/albums/s580/mtb1973/APTable_zps51b9e6be.jpg[/IMG] [code="sql"]SELECT 'SourceName' AS SourceDB , 'NAME' AS 'SiteName' , RTRIM(APIBH.IDVEND) AS VendorNumber , RTRIM(APIBH.IDINVC) AS VoucherNumber , APIBH.AMTGROSDST AS VoucherTotalDomestic , (APIBH.AMTGROSDST * APIBH.EXCHRATEHC) AS VoucherTotalUSD , (APIBH.AMTGROSDST - ISNULL(APTCP.AMTPAYM, 0)- APTCP.AMTERNDISC) AS OpenAmountDomestic , ((APIBH.AMTGROSDST * APIBH.EXCHRATEHC) - ISNULL(APTCP.AMTPAYM, 0)* APIBH.EXCHRATEHC)- (APTCP.AMTERNDISC * APIBH.EXCHRATEHC) AS OpenAmountUSD , dbo.udf_convert_int_date(APIBH.DATEBUS) AS PostedDate , dbo.udf_convert_int_date(APTCR.DATEBUS) AS AppliedDate , dbo.udf_convert_int_date(APIBH.DATEINVC) AS AgingDate , dbo.udf_convert_int_date(APIBH.DATEDUE) AS DueDate , dbo.udf_convert_int_date(APIBH.DATEINVC) AS DocumentDate , NULL AS ReceivedDate , CASE WHEN (APTCR.DATERMIT) = 0 THEN NULL ELSE dbo.udf_convert_int_date(APTCR.DATERMIT) END AS PaidDate , CASE WHEN (APIBH.DATEDISC) = 0 THEN NULL ELSE dbo.udf_convert_int_date(APIBH.DATEDISC) END AS DiscountDate , CONVERT(bigint, CASE WHEN APIBH.AMTGROSDST - ISNULL(APTCP.AMTPAYM, 0) = 0 THEN dbo.udf_convert_int_date(APTCR.DATERMIT) ELSE GETDATE() END - dbo.udf_convert_int_date(APIBH.DATEINVC)) AS AgeDays , CONVERT(bigint, dbo.udf_convert_int_date(APIBH.DATEDUE) - CASE WHEN APIBH.AMTGROSDST - ISNULL(APTCP.AMTPAYM, 0) = 0 THEN dbo.udf_convert_int_date(APTCR.DATERMIT) ELSE GETDATE() END) AS DueDays , APIBH.CODECURN AS CurrencyCode , APIBH.IDTRX, ISNULL(APTCP.AMTPAYM, 0) AS PayAmt ,APIBH.EXCHRATEHC AS EffectiveExchangeRateFROM MyTable.APIBH AS APIBH LEFT OUTER JOIN MyTable.APTCP AS APTCP ON APIBH.IDVEND = APTCP.IDVEND AND APIBH.IDINVC = APTCP.IDINVC INNER JOIN MyTable.APTCR AS APTCR ON APTCP.BATCHTYPE = APTCR.BTCHTYPE AND APTCP.CNTBTCH = APTCR.CNTBTCH AND APTCP.CNTRMIT = APTCR.CNTENTRWHERE (1 = 1) AND (APIBH.ERRBATCH = 0) --AND (APIBH.FISCYR >= '2010') AND (APIBH.IDTRX <> 32) AND APIBH.IDINVC = '010106'[/code]

Coalesce question?

Posted: 07 Mar 2013 02:55 AM PST

I am trying to concatenate the URL column into one csv value for each ApplicantID. I am not having luck achieving the results I am looking for. Any help most appreciated...SELECTFASTFacultyApps.LastName,COALESCE([FacultyApps_TEST].[dbo].[VideoLinks].URL + ',', '') + [FacultyApps_TEST].[dbo].[VideoLinks].URL AS VidListFROM[FacultyApps_TEST].[dbo].FASTFacultyAppsJOIN [FacultyApps_TEST].[dbo].[VideoLinks]ON[FacultyApps_TEST].[dbo].[VideoLinks].ApplicantID = [FacultyApps_TEST].[dbo].FASTFacultyApps.IDWHERE[FacultyApps_TEST].[dbo].FASTFacultyApps.isArchived = 0RESULT:[img]http://www.danforthcenter.org/GMDW/images/Results.jpg[/img]TABLES:[img]http://www.danforthcenter.org/GMDW/images/Vids.jpg[/img][img]http://www.danforthcenter.org/GMDW/images/Applicants.jpg[/img]TIA,Andrew

determine first instance as one type and all other instances as another type

Posted: 07 Mar 2013 03:06 AM PST

Need to identify the first / earliest certDate matching cert_id as 'Initial" and each additional cert_ID as 'Re-certification' for each Field_id for each ABR_ID. [code="plain"]ABR_ID CERT_ID Field_ID certDate31183 31996 DR 1987-12-13 00:00:00.00031183 31997 SV 1995-02-28 00:00:00.00031183 31998 SV 2004-07-16 00:00:00.000[/code]Given the example above cert_ids 31996 and 31997 would be considered as Initial certification and cert_ID 31998 would be considered a Re-certification.Build the table[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[jwmTestCert]( [abr_ID] [nvarchar](10) NOT NULL, [cert_id] [int] NOT NULL, [field_id] [nvarchar](3) NOT NULL, [certDate] [datetime] NOT NULL, [ExpireYear] [nvarchar](4) NULL, [status] [nvarchar](25) NOT NULL, [statusDate] [datetime] NOT NULL) ON [PRIMARY]GO[/code]Populate[code="sql"]insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'dr', '1987-12-13')insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31997,'sv', '1995-02-28')insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'sv', '2004-07-16')[/code]Thank you for your time.

RANK ISSUE

Posted: 07 Mar 2013 04:57 AM PST

NOTICE THE DESIRED OUTPUT ON THE BOTTOM. SELECT 'table1' TABLENAME,'col1' COLNAMEINTO #temp UNION ALL SELECT 'table1','col2'UNION ALL SELECT 'table1','col3'UNION ALL SELECT 'table2','col1'UNION ALL SELECT 'table2','col2'UNION ALL SELECT 'table3','col1'SELECT *FROM #temp --DESIRED OUTPUT ID COL:--ID TABLENAME COLNAME--1 table1 col1--2 table1 col2--3 table1 col3--1 table2 col1--2 table2 col2--1 table3 col1

How to add item No. in SELECT

Posted: 07 Mar 2013 02:29 AM PST

Hello,I have a table like :[code="sql"]DECLARE @TEST1 TABLE (C_NAME varchar(10), REQ_ITEM VARCHAR(5))INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('John', 'Item1')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('John', 'Item2')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item2')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item3')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item4')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item5')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Joe', 'Item1')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Joe', 'Item5')INSERT INTO @TEST1(C_NAME,REQ_ITEM) VALUES('Jane', 'Item3')[/code]How can I show something like :[code="plain"]C_NAME ITEM_NO REQ_ITEM-------------------------------------John 01 Item1John 02 Item2Jake 01 Item2Jake 02 Item3Jake 03 Item4Jake 04 Item5Joe 01 Item1Joe 02 Item5Jane 01 Item3[/code]Thanks in advance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog