Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,391 through 30,405 (of 59,101 total)

RE: Modified DateTimeStamp - No Default on record creation

opc.three (4/9/2012)
L' Eomot Inversé (4/9/2012)
Jeff Moden (4/8/2012)
I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 10, 2012 at 3:59 pm

#1471580
RE: Running xp_cmdshell under non system admin

I'll search for the error message when I get home but this is going to be a tough one to troubleshoot from here. I'm not sure there's anything I'll...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 10, 2012 at 3:57 pm

#1471577
RE: Create script to split databases into 5 groups based on database size

pamozer (4/10/2012)
Thank You. So now looking at the results more clearly, this does give me closer to what I am looking for the only thing is when you have...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 10, 2012 at 3:55 pm

#1471574
RE: How to get multiple of Rows of data into one single row in the CSV format

sbj1411 (4/10/2012)
Resolved the issue in TSQL using the CTE.
Thanks everyone for your suggestion and help 🙂
Would you post your code, please? It might help someone else. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 10, 2012 at 3:52 pm

#1471570
RE: Running xp_cmdshell under non system admin

balbirsinghsodhi (4/10/2012)
Thanks Jeff for the script.. I ran the script but still getting the same error.
A call to 'CreateProcessAsUser' failed with error code: '1314'.
I am sure that this error related...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 10, 2012 at 9:17 am

#1471280
RE: Recursive CTE for Item supersession question

dwain.c (4/9/2012)
One more question though. Who is Bob and why is he my uncle? 🙂
It's "British". It means the same as when we Americans say "and you're done"!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 11:47 pm

#1471066
RE: Recursive CTE for Item supersession question

Actually, if it were my code, I'd probably use the recursive CTE just to number the rows in the correct order using a Hierarchical Path concatenation and dump it into...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 11:44 pm

#1471065
RE: Recursive CTE for Item supersession question

dwain.c (4/9/2012)
Jeff,
That neat little trick with HRoot that you did in the CTE is just what I needed to get rid of the silly OR that I did on my...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 11:34 pm

#1471063
RE: Recursive CTE for Item supersession question

CELKO (4/9/2012)
This is more of a temporal problem than a recursive problem.
At first glance, I thought so, as well. Then I looked at the values in the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 11:26 pm

#1471060
RE: Recursive CTE for Item supersession question

Using the provided data except as Temp Tables instead of Table Variables, here's a recursive CTE solution without the Cartesian Product problem. Notice the "HRoot" column which allowed me...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 11:09 pm

#1471056
RE: Recursive CTE for Item supersession question

I've not sussed the problem yet but the recursive method is going to get pretty darned slow as Dwain indicated. The recursive CTE essentially causes a cross join (Cartesian...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 10:10 pm

#1471041
RE: Recursive CTE for Item supersession question

shannonjk (4/9/2012)
I will put along some sample data here today. Sorry this is not the only critical thing I have to do today! 😀
Not to worry. Wasn't critical for...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 9:49 pm

#1471040
RE: DB whitespace

rummings (4/9/2012)
the higher ups are super stingy with disk space.... we have to squeeze out all we can to run the system.
BWAAA-HAAAA!!!! The first mistake was someone telling the higher...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 8:32 pm

#1471036
RE: SQL request performance problem

My first step would be to add the recommended missing index. FTS is quite fast when properly indexed. It's really not much more than the output of a...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 8:24 pm

#1471034
RE: Create script to split databases into 5 groups based on database size

pamozer (4/9/2012)
Does this make sense?
Perfectly.
WITH
ctePreAgg AS
( --=== Find the total MB of each database and number both forwards and backwards
SELECT [RowNumAsc] = ROW_NUMBER() OVER (ORDER BY SUM(af.) ASC...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

April 9, 2012 at 7:56 pm

#1471030

Viewing 15 posts - 30,391 through 30,405 (of 59,101 total)