Viewing 15 posts - 6,736 through 6,750 (of 7,613 total)
If XACT_STATE() returns -1 [uncommitable transaction], you can't do anything else except rollback the current transaction. That is a logical restriction within SQL Server itself.
In that case, you would...
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".
February 27, 2013 at 1:35 pm
Look into clustering the table on column A: that will likely address the issue more effectively and efficiently than a slew of nonclus indexes.
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".
February 27, 2013 at 11:41 am
Yes, add a new tempdb data file, at exactly the same size as the current data file. Use KB to get the exact size. [That way you will...
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".
February 27, 2013 at 11:37 am
Here's a quick script to demonstrate the warning and then the actual error that occurs:
USE tempdb
CREATE TABLE test_index (
long_column_to_index varchar(8000)
)
...
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".
February 26, 2013 at 4:32 pm
appClinic is the only column(s) that is(are) longer than 900 bytes.
If the total bytes in the index keys are 900 or less, that's not an issue for SQL, so you...
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".
February 26, 2013 at 4:18 pm
It's just a warning message, unless you actually have a value in that column that exceeds 900 bytes.
You could put that one index create in a separate step, and for...
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".
February 26, 2013 at 4:01 pm
The integer value for the first day of the month is even easier:
SELECT BOM = @ThisYear * 10000 + @ThisMonth * 100 + 1,
but you must CAST it to char(8)...
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".
February 26, 2013 at 3:43 pm
GilaMonster (2/25/2013)
ScottPletcher (2/25/2013)
GilaMonster (2/25/2013)
CTEs don't store results
Never??? What if enough memory does not exist to store the results? Surely the results would spill to disk then, presumably...
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".
February 25, 2013 at 4:06 pm
Not a lot to go on, but maybe something like this:
DECLARE @rows_updated TABLE (
area <same_datatype_as_in_table>,
year <same_datatype_as_in_table>,
benchmark <same_datatype_as_in_table>
...
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".
February 25, 2013 at 3:01 pm
SELECT
COALESCE(t1.comp_name, t2.comp_name, t3.comp_name, t4.comp_name) AS comp_name,
...
FROM dbo.Tbl1 t1
FULL OUTER JOIN dbo.Tbl2 t2 ON
t2.comp_name = t1.comp_name
FULL OUTER JOIN dbo.Tbl3...
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".
February 25, 2013 at 11:00 am
GilaMonster (2/25/2013)
CTEs don't store results
Never??? What if enough memory does not exist to store the results? Surely the results would spill to disk then, presumably to tempdb...
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".
February 25, 2013 at 10:55 am
IF EXISTS(SELECT 1 FROM VERSION
WHERE VERSION LIKE '7.[01234567]%')
BEGIN
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".
February 25, 2013 at 10:39 am
Usually views contain lots of columns that aren't really needed when several of them are joined to produce a new result set.
Stop using the views and code directly against 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".
February 22, 2013 at 1:48 pm
SELECT
CASE WHEN GETDATE() >= DATEADD(DAY, 1, [current_month_3rd_friday]) THEN
--current day of month is past 3rd Fri, so calc next...
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".
February 22, 2013 at 1:43 pm
Select
A, B, C,
sum(case when A between 1 and 100 and B between 1 and 100 and C...
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".
February 22, 2013 at 1:27 pm
Viewing 15 posts - 6,736 through 6,750 (of 7,613 total)