Viewing 15 posts - 6,196 through 6,210 (of 7,614 total)
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2014 at 9:10 am
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2014 at 4:05 pm
Grant Fritchey (3/25/2014)
ScottPletcher (3/25/2014)
Grant Fritchey (3/25/2014)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2014 at 3:39 pm
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,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2014 at 3:25 pm
Grant Fritchey (3/25/2014)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2014 at 3:22 pm
Best guess with what's known so far:
Create an index on "order":
key = ( claim_id ); include = ( order_id, created_dtm )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 24, 2014 at 12:35 pm
GilaMonster (3/15/2014)
ALTER DATABASE with the WITH MOVE clauseTake the database offline
Copy the files to their new location
Bring the DB online.
Test in a dev/test environment first.
I'm not aware of "ALTER DATABASE...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 21, 2014 at 1:50 pm
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)...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 21, 2014 at 1:42 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 21, 2014 at 8:09 am
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 4:35 pm
RexHelios (3/20/2014)
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 4:10 pm
RexHelios (3/20/2014)
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 3:41 pm
Luis Cazares (3/19/2014)
You could try something like this:
WHERE somecolumn NOT LIKE '%[^0-9.]%'
This won't be completely safe as it will return false positives for values with more than one point(.).
WHERE
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 3:31 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 3:27 pm
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,
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 3:18 pm
Viewing 15 posts - 6,196 through 6,210 (of 7,614 total)