Jeff Moden

Forum Replies Created

Viewing 15 posts - 34,891 through 34,905 (of 59,102 total)

RE: drop temp table in dynamic SQL

Craig Farrell (2/21/2011)
Jeff Moden (2/21/2011)
Umm... nope... I believe that's a problem, as well.
<snip proof>
The only way to do this using the same Temp Table name is with dynamic SQL.
Dangit, you're...
--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)

February 27, 2011 at 5:17 pm

#1292095
RE: Group by and Concatenate just one field?

jason-772533 (2/23/2011)
Thanks for the pointer, Lutz. I've searched SSC as suggested, and it seems that the XML functionality is only available in SS2005 and later. Unfortunately, although I'm...
--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)

February 27, 2011 at 5:12 pm

#1292093
RE: Split coulumn value

infodemers (2/25/2011)
That did niot do the trick!
I found my answer here -->
Thanks anyway!
Could you post the code that didn't do the trick? 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)

February 27, 2011 at 5:07 pm

#1292092
RE: Kindly explain the case query.

It's actually not the same result. The answer would appear in one or the other column depending on the bit map. Now, WHY they needed it that way,...
--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)

February 27, 2011 at 4:49 pm

#1292091
RE: Why not use PK with Clustered Index instead of PK with Unique Nonclustered Index

Kevin Feasel (2/14/2011)
This is a just-so story that I'm coming up with, but it sounds like it makes sense, at least to me... 🙂
I don't know about it being...
--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)

February 27, 2011 at 4:30 pm

#1292089
RE: Bulk Insert

george.frewin (2/18/2011)
thanks
I am now getting stuck into SSIS 🙂
To put a different slant on it, I think that SSIS is overkill. 😉 A nice, short, sweet BULK INSERT does...
--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)

February 27, 2011 at 4:22 pm

#1292087
RE: Real Blonde Moment - Statement Help

SQL_Student (2/18/2011)
I am truly my own teacher... I got it to work:w00t:
select code, MAX(effective_date) as eff_date
from LIST_PRICES
group by CODE
having MAX(EFFECTIVE_DATE) > 0
Well done! I love it when folks science...
--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)

February 27, 2011 at 4:19 pm

#1292086
RE: how to the data type of columns #temporary tables and other info on #temp tables?

If you have a session that created a Temp Table, just do the following without closing the session and without changing sessions (windows in SSMS)...
EXEC TempDB..sp_help #nameoftemptable
--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)

February 27, 2011 at 4:11 pm

#1292085
RE: If I convert the data types like below will this the data types stick in the selected into temp

I'll also add that if you use ISNULL as the final "step" in any of the column derivations, it will create a NOT NULL column in the Temp Table.
--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)

February 27, 2011 at 4:03 pm

#1292084
RE: Simple Query

As a bit of a sidebar... what happens when one of your team members changes her name due to marriage? What happens when you have two people employed with...
--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)

February 27, 2011 at 4:01 pm

#1292083
RE: RAISERROR within CATCH not propagated to C# Caller

Gadgetman Stewart (2/27/2011)
Hi Paul,
I understand your point about ExecuteScalar() will only "see" one result set, so indeed if my SP contains:-
BEGIN TRY
DECLARE @s-2 INT;
SET @s-2 = 1/0;-- Cause an...
--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)

February 27, 2011 at 3:48 pm

#1292082
RE: storage and performance issues of using NVarchar(Max)

Let's look at it another way. When would you NOT check either table of Notes? I suspect the answer is that you'd always have to check both which...
--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)

February 27, 2011 at 3:38 pm

#1292081
RE: How to solve this 2 query

Craig Farrell (2/27/2011)
Jeff Moden (2/27/2011)
Interesting... why do you think this is such a bad practice especially if you don't have the Enterprise Edition? What would you use instead...
--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)

February 27, 2011 at 3:26 pm

#1292079
RE: Grouping and crosstab like behaviour.... hairy query

Heh... thanks for the feedback, ZA. Most people don't get the pun built into that particular table name. 😛
Just as a reminder and a friendly suggestion... the method I...
--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)

February 27, 2011 at 2:36 pm

#1292073
RE: How to solve this 2 query

LutzM (2/27/2011)
Please clarify the contradiction between your aforementioned statement and your comment from one of your other posts
i have deadline on monday and if i dont complete it ,i will...
--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)

February 27, 2011 at 2:28 pm

#1292069

Viewing 15 posts - 34,891 through 34,905 (of 59,102 total)