Jeff Moden

Forum Replies Created

Viewing 15 posts - 58,846 through 58,860 (of 59,067 total)

RE: Should i use global variables

Don't use @@IDENTITY which can be tricked by triggers... use SCOPE_IDENTITY() instead.
Always assign values captured in @@ROWCOUNT and @@ERROR immediately after the SQL that you want to check. You can...
--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 10, 2005 at 7:54 pm

#541710
RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Very Nice article. Covered a lot of ground and it's a pleasure to actually see commented formatted code. I've seen a method to write to text files using linked servers...
--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 7, 2005 at 10:18 pm

#541061
RE: Date Column with NULL value

Yup... I know the feeling... you should see the one at work. Glad to help.
--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)

January 28, 2005 at 4:22 am

#539501
RE: Query Help - Adding a column that sums other columns

Dunno, maybe I missed something....
SELECT
E.PAYTYPE
, SUM(CASE datepart(dw,E.TimeDate) WHEN 2 THEN E.Hours ELSE 0 END) as Monday
, SUM(CASE datepart(dw,E.TimeDate) WHEN 3 THEN E.Hours ELSE 0 END) as Tuesday
, SUM(CASE datepart(dw,E.TimeDate)...
--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)

January 27, 2005 at 7:59 pm

#539469
RE: Date Column with NULL value

It's a bit difficult to believe that you would ever have a NULL StartDate with a valid EndDate. Normally, it's the other way around. But, no worries...
For NULL StartDates, 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)

January 27, 2005 at 7:49 pm

#539467
RE: Time conversion

Antares,
I apologize if the time lag between our posts caused this...
I had reposted a solution as follows...
STUFF(STUFF(REPLACE(STR(Run_Time,6),' ','0'),5,0,':'),3,0,':')
The original question was how to convert the Run_Time column of...
--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)

January 27, 2005 at 6:56 am

#539327
RE: Time conversion

p.s.
If you are getting a "NULL' as a return, perhaps the code you wrote isn't actually finding what you think. Post your code and we'll take a look.
--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)

January 27, 2005 at 6:36 am

#539322
RE: Time conversion

Schalk and Antares,
I test this stuff before I post it and, you are incorrect. STUFF works just fine on an INT values thanks to implicit conversions. For example, 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)

January 27, 2005 at 6:27 am

#539321
RE: tempdb autogrow issue

Autogrow = Fragmentation
As some have already said on this thread, set TempDB to be a reasonably large initial size and set autogrow to100 to 500 mb. We currently have 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)

January 26, 2005 at 10:04 pm

#539266
RE: Time conversion

Since the column is in the format of HHMMSS (even though it's a number),
For just a pure string representation of the time...
STUFF(STUFF(run_duration,5,0,':'),3,0,':')
For a real time as 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)

January 26, 2005 at 9:57 pm

#539265
RE: Get a Percentage from Integers

You may experience some "wierd" answers when using FLOAT because it does it's math in a binary rather than decimal fashion.
--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)

January 16, 2005 at 2:01 pm

#537599
RE: subtraction of column doesn''''t work correctly

The problem is most likely that the datatype in one of the columns is FLOAT which using binary math instead of Decimal math with some very unexpected results. Recommend you...
--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)

January 14, 2005 at 9:32 pm

#537565
RE: how to return result of a query into a dos variable?

I believe that your outer parenthesis are the problem. Compare your script tot the previous answer.
--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)

January 14, 2005 at 9:28 pm

#537564
RE: Inserting into table with one column 1,2,3,4...

> If you are using this for a one time query, you really will want to do it that way
... unless the return is very large in which case you'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)

January 14, 2005 at 9:15 pm

#537563
RE: Question About Date Data

There is if you update other fields along with the "cased" field. Nulls will be put in the "cased" field.
--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)

January 14, 2005 at 9:11 pm

#537562

Viewing 15 posts - 58,846 through 58,860 (of 59,067 total)