rice.tx (7/14/2013)

---

Thank you both for your responses. It seems the answer will involve explicit transaction and explicit locks. I was hoping to avoid that. I know there...

If you're learning about UNPIVOT, you might want to look into the CROSS APPLY VALUES approach to UNPIVOT (see first article in my signature links).

You're most welcome and thanks for letting me know it resolved your question.

I hate it when they do that to me. 😀

I would say that it improves the probability.

Any time you retrieve a record in one SQL statement that you want to be "current" and then use it in a second...

I'm not sure if this is exactly what you're looking for in the last query or not but give it a try:

SELECT doc1.col1.value('SalesOrderNumber[1]', 'varchar(50)') SalesOrderNumber

,doc.col.value('LotNumber[1]', 'varchar(50)') LotNumber

,doc.col.value('ExpiryDate[1]', 'datetime') ExpiryDate

,doc.col.value('Quantity[1]', 'decimal(13,5)')...

Steven - BTW.

If you do it the way you suggested, which is to break it into multiple SQL statements, you should consider 2 things:

1. Wrap the SQL statements in a...

Somebody's probably going to shoot me for suggesting it but (utilizing Steve's wonderful DDL and sample data), perhaps something like this:

CREATE TABLE #eventLog

(

...

Sean Lange (7/10/2013)

---

phoenix_ (7/10/2013)

---

To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂

So Instead of this...

Because it seems you have an unknown number of AccountIDs for a...

You can also use a pattern splitting FUNCTION like below:

WITH SampleData(String) AS(

SELECT 'Doe, John, H *^' UNION ALL

SELECT 'Doe, John, H, **' UNION ALL

SELECT 'Doe, John, H *¨#$')

SELECT String=Item

FROM SampleData

CROSS...

dan-572483 (7/11/2013)

---

Regarding the @query parameter, BOL states:

"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to...

How about like this?

Create table #tbl

(

ID int identity,

number varchar(10),

numstatus varchar(10)

);

INSERT INTO #tbl

Values

('V001','Active'),

('V002','Active'),

('V003','Active'),

('V004','InActive'),

('V005','Active'),

('V006','Active'),

('V007','Active'),

('V008','Active'),

('V009','Active'),

('V010','Active');

WITH SeriesRuns AS (

SELECT Start='V001', [End]='V005' UNION ALL SELECT 'V006','V010')

SELECT Start, [End], IDStart=MIN(ID),...

Sean Lange (7/11/2013)

---

OK I will show my true American colors here. My wife's company just got a new client in Newfoundland. She is going to have to travel there this...

I'm not sayin' this is the best or fastest way or nuthin' but it (or somethin' like it) might work for you:

CREATE TABLE #Responses

(CustID VARCHAR(10), SurveyID...