ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,466 through 6,480 (of 10,143 total)

RE: maximum recursion error

Gangadhara MS (11/17/2011)
Hi Experts,
One of the stored procedure is failing with displaying below error.Till now it was runnign fine.
Pls suggest
The statement terminated. The maximum recursion 100 has been exhausted...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 17, 2011 at 2:16 am

#1409026
RE: Recursive query or not

DROP TABLE #Doc_Version
CREATE TABLE #Doc_Version (ID INT,DocID INT, OldVersionID INT)
INSERT INTO #Doc_Version (ID, DocID, OldVersionID)
SELECT 1, 233, 230 UNION ALL
SELECT 2, 233, 232 UNION ALL
SELECT 3, 232, 241
DECLARE @OldVersionID...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 16, 2011 at 7:12 am

#1408483
RE: Starting with "WITH"

SQL Kiwi (11/13/2011)
L' Eomot Inversé (11/13/2011)
OK, going to 1000 yours has 67% of the logical (row) reads that the naive one has and only half the CPU usage. Neither...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 16, 2011 at 6:46 am

#1408460
RE: I have a coworker....

Lowell (11/15/2011)
I have a coworker who heats up things in the microwave.
(He or She) clicks the Instant 30 seconds button, waits 10 seconds, watching the count down, and then clicks...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 15, 2011 at 10:19 am

#1408044
RE: Help On StoredProc....Running for more than 2 minutes

There are seven functions in your output list. Guessing from the function names and the passed parameters, a couple of them perform quite complex calculations touching several tables. Your first...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 11, 2011 at 4:40 am

#1406180
RE: Are the posted questions getting worse?

Kiara (11/10/2011)
ChrisM@Work (11/10/2011)
Kiara (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...
I'm sure we've all had days like that...
I inherited a query like that back in 99. It was...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 10, 2011 at 9:47 am

#1405890
RE: Are the posted questions getting worse?

Ninja's_RGR'us (11/10/2011)
Kiara (11/10/2011)
Ninja's_RGR'us (11/10/2011)
SQL Kiwi (11/10/2011)
Ninja's_RGR'us (11/10/2011)
Actually I caught the very moment it happened!
You need a hobby.
Right back at ya :hehe:.
Father in hospital, house flooded. Need some R&R atm.
Sorry...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 10, 2011 at 8:14 am

#1405800
RE: Are the posted questions getting worse?

Kiara (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...
I'm sure we've all had days like that...
I inherited a query like that back in 99. It was a 500...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 10, 2011 at 8:07 am

#1405787
RE: Making a SUM of COUNT

This should do it:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN 1 ELSE 2 END
* CASE WHEN e.BOOKING_EQ_REQ_DETAIL_ID IS NULL THEN 0 ELSE 1 END) AS BookedTEU
This gives you a 1...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 10, 2011 at 6:57 am

#1405719
RE: GROUPING CASE

It's going to be something like this:
SELECT
tr_trrefn,
tr_abbrv,
Disbs = SUM(CASE
WHEN tr_abbrv = 'BIL' THEN tr_disamt
WHEN tr_abbrv = 'ABT' THEN - tr_disamt
WHEN tr_abbrv...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 10, 2011 at 6:28 am

#1405696
RE: Update/Set/Append

John Michael Robertson (11/8/2011)
Breakwaterpc (11/8/2011)
However, one more issue with these tables. I know need to update part of a string that i placed in several different columns in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 10:01 am

#1404690
RE: Update/Set/Append

John Michael Robertson (11/8/2011)
Breakwaterpc (11/8/2011)
For some reason, i'm getting all twisted up and bumfuzzled here.
First off, where is this variable "NewValue" getting used once it's cast? Doesn't appear...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 9:09 am

#1404644
RE: Update/Set/Append

Breakwaterpc (11/8/2011)
Thanks. However, i'm getting the following with the Select statement:
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the add...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 8:49 am

#1404624
RE: Update/Set/Append

-- run this first for an eyeball check
SELECT r.*, r.comments + ' ' + s.other
FROM civil.dbo.RECEIPTS r
INNER JOIN civil.dbo.subjects s
ON s.Receiptno = r.Receiptno
-- this is the update...from statement
UPDATE r
SET...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 8:38 am

#1404607
RE: Starting with "WITH"

Jeff Moden (11/7/2011)
...like Cursors and WHILE loops, I just want to make sure that people understand that the use of rCTEs should ~~probably~~ be avoided if there's a decent set...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 7:04 am

#1404501

Viewing 15 posts - 6,466 through 6,480 (of 10,143 total)