Viewing 15 posts - 8,416 through 8,430 (of 14,953 total)
My first question would be why you have a column that's defined as an incrementing number, and the data type is char(10).
Second, what do you get if you select from...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:48 pm
All the proc does is populate a temp table, then run that sp_transform proc.
This means that there are three things that could be slow:
1. The view
2. Creating the...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:43 pm
That's why my version of the trigger is "For Update", not "Instead of Update". That's one of the key differences.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:38 pm
Well, for code objects, you could select from sys.sql_modules. That'll get you procs, views and functions. For tables, you'd need to select from sys.columns, and a variety of...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:36 pm
If you have a Numbers table (Tally table, Sequence table), it's easy:
select dateadd(day, number-1, '9/1/2009')
from dbo.Numbers
where number <= datediff(day, '9/1/2009', dateadd(month, 1, '9/1/2009'));
If you don't have a Numbers table, and...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:32 pm
gboyer (9/4/2009)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:27 pm
Doesn't matter if the DML has the ID column in it or not. Triggers have two "tables", one called "inserted", one called "deleted". The inserted one is a...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:16 pm
Try this:
CREATE TRIGGER trg_UpdateDateModified
ON Employees
FOR UPDATE
AS
UPDATE Employees
SET DateModified = getdate()
FROM Employees
INNER JOIN inserted
on Employees.ID = inserted.ID;
I'm assuming the existence of an ID column in the Employees...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:04 pm
Good that you solved it.
It's probably a minor difference in the way the engine code was written for selects vs for deletes. But that's just me guessing. You...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:21 am
If the dynamically created table is a persistent table, not a temp table, you could export it to csv pretty easily with bcp.
If you don't want to keep the table,...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:13 am
It looks, from your example, like @name is supposed to have two different values. Is that what you're trying to accomplish?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:09 am
If you just want to get rid of the trailing zeroes from a number, you can reverse it, cast that as an Int, cast back to varchar and re-reverse.
Is that...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:07 am
I'd have to know what sp_transform does to even begin debug this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:04 am
Have you tried running the sub-selects separately to see what you get from those?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 11:02 am
RBarryYoung (9/4/2009)
Jeff Moden (9/3/2009)
REVERSE? Nah... just wicked dyslexic this time of day. 😛Hmm, for some reason I always thought that that word was spelled "cixelsyd". 😛
Lysdexia is jo...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 7:45 am
Viewing 15 posts - 8,416 through 8,430 (of 14,953 total)