Roughly like below. You may have to adjust the "SINGLE_NCLOB" to "SINGLE_CLOB", depending on the format of the input file.

DECLARE @file_text varchar(max)

SELECT @file_text = file_text

FROM OPENROWSET(BULK 'd:\full\path\to\file\filename.txt', SINGLE_NCLOB) AS...

Gotta' admit, I prefer working in SQL/TSQL, so even for (moderately) large text files I'll often just do an:

OPENROWSET(BULK '<path_and_file_name>', SINGLE_CLOB) --or NCLOB, if req'd

to load the whole file at...

Grant Fritchey (3/25/2014)

---

ScottPletcher (3/25/2014)

---

Grant Fritchey (3/25/2014)

---

I can't see the query since it exceeds the size of what's stored in the plan, so suggestions from the plan alone are going to...

Hard to provide more details without the rest of the query and with no index info on the tables.

Edit: Looks like a covering index is needed on SubOrderTrans at least,...

Grant Fritchey (3/25/2014)

---

I can't see the query since it exceeds the size of what's stored in the plan, so suggestions from the plan alone are going to be a bit...

Best guess with what's known so far:

Create an index on "order":

key = ( claim_id ); include = ( order_id, created_dtm )

I'm not aware of "ALTER DATABASE...

My best quick guess is that a StartTime of NULL indicates an unused entry. Even if not, the code below may give you some ideas for the correct column(s)...

You could set specific byte(s) in CONTEXT_INFO() in the trigger, then have other relevant SQL code check those byte(s) and set the isolation level appropriately based on the value of...

No, I'm sorry, I didn't realize you literally wanted the sort order in the result.

We will have to use ROW_NUMBER() for that, but I don't think we have to use...

RexHelios (3/20/2014)

---

Scott,

I think we have a slight disconnect here :). Your query is selecting from @ItemListOutput, which is where I included "ItemSortOrder" column. I did this to show my desired...

RexHelios (3/20/2014)

---

Scott,

Thanks for your suggestion, but your query won't give me the sort order id for each individual item of an order. Please look at the last column of @ItemListOutput...

WHERE

...

I think the best try might be to use a LOGON trigger. Hopefully you can identify those Excel logins using APP_NAME() and/or HOST_NAME() and/or ORIGINAL_LOGIN() (and/or whatever else).

Within the...

I don't think you have to go thru all that.

SELECT *

FROM @ItemListOutput

ORDER BY

OrderID,

CASE WHEN ItemTypeID = '4006' THEN 1 ELSE 2 END,

...