Jeff Moden

Forum Replies Created

Viewing 15 posts - 31,606 through 31,620 (of 59,099 total)

RE: Defining local procedure

Actually, yes you can (sorry Dev). You can have one stored procedure create a temporary stored procedure that works just fine. See "Create Proceedure" in Books Online 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)

December 21, 2011 at 11:14 pm

#1424583
RE: To VIEW or Not To VIEW ... That is the Question.

David Moutray (12/21/2011)
I probably shouldn't ask, but I can't help myself: what, pray tell, is a "high velocity pork chop?" :unsure:
Several years ago, some manager posted about a developer...
--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)

December 21, 2011 at 11:06 pm

#1424579
RE: First name And last Name in SQL

Dev (12/21/2011)
"Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"
Title FName LName Degree
Agree on Names but Titles & Degrees shouldn't be in source data.
"Shouldn't be" and...
--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)

December 21, 2011 at 10:50 pm

#1424573
RE: Extended Stored Procedures - xp_cmdshell / Wait Type msql_xp

When an xp_cmdshell job executes, two spids are active... one for the "calling proc" and the other for the xp_cmdshell call.
If the xp_cmdshell job fails for any reason, the spid...
--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)

December 21, 2011 at 10:48 pm

#1424571
RE: To VIEW or Not To VIEW ... That is the Question.

David Moutray (12/21/2011)
Is anyone aware of an incident where a database administrator "went postal" on an application development department?
Yep... me. I got tired of some real crap code and...
--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)

December 21, 2011 at 10:34 pm

#1424568
RE: To VIEW or Not To VIEW ... That is the Question.

David Moutray (12/21/2011)
Many developers seem to write code as if the compiler is charging by the character.
Heh... think "cloud". 😛
--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)

December 21, 2011 at 10:08 pm

#1424564
RE: First name And last Name in SQL

Borrowing shamelessly from Cadavre, the following includes the original test data he built as well as the 3 outlying cases I provided as well as an answer on the current...
--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)

December 21, 2011 at 10:02 pm

#1424561
RE: First name And last Name in SQL

Cadavre (12/21/2011)
Or if you want to strip out anything after a space, then something like this: -
You forgot multi-word last names...
BEGIN TRAN
CREATE TABLE yourTable (
[yourTable ID] INT IDENTITY NOT NULL,
[First...
--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)

December 21, 2011 at 9:36 pm

#1424560
RE: First name And last Name in SQL

Cadavre (12/21/2011)
Either a REPLACE like this: -
Still think that might be a good idea? Try your code with the following names...
BEGIN TRAN
CREATE TABLE yourTable (
[yourTable ID] INT IDENTITY NOT...
--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)

December 21, 2011 at 9:33 pm

#1424559
RE: First name And last Name in SQL

x002548 (12/21/2011)
This is a presentation issue that should be handled in the front end
Maybe not. What if there is no front end or it's simply a conversion required 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)

December 21, 2011 at 9:28 pm

#1424558
RE: Creating a sum column without losing records

CROSS APPLY is certainly a marvel... but you don't need it here.
SELECT ID, OrderNumber, Total,
OrderTotal = SUM(Total) OVER (PARTITION BY OrderNumber)
...
--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)

December 21, 2011 at 9:11 pm

#1424557
RE: Drop-Down showing all years between 2 dates

This is what I'd likely do, instead.
WITH
cteNormalizeData AS
(
SELECT ProjectID,
StartYear = MAX(CASE WHEN [Attribute Name] = 'Start Date' THEN DATEPART(yy,DateTimeValue) ELSE 0...
--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)

December 21, 2011 at 9:04 pm

#1424555
RE: Drop-Down showing all years between 2 dates

MattieMich7 (12/21/2011)
I figured it out. Here's the code:
create table #Table1 (ProjectID int, [Attribute Name] nvarchar(50), DateTimeValue datetime)
insert into #Table1
select 1,'Start Date','8/8/2008'
union all
select 1,'End Date','4/30/2011'
union all
select 2,'Start Date','10/1/2011'
union all
select 2,'End...
--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)

December 21, 2011 at 8:43 pm

#1424550
RE: Random Slowness in Insert statment

Aspet Golestanian Namagerdi (12/21/2011)
Also,I notice this slowness happens every 10 minutes.
Transaction log backup up or replication could both cause this.
--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)

December 21, 2011 at 8:36 pm

#1424548
RE: where does that estimated number come from?

assistan11 (12/20/2011)
Hi Jeff..
You're absolutely right! It was the "FAST 5"...I can't believe I've missed something that obvious 😀
Thanks a lot
You should see what I miss before I've had my 2nd...
--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)

December 21, 2011 at 8:28 pm

#1424547

Viewing 15 posts - 31,606 through 31,620 (of 59,099 total)