Jeff Moden

Forum Replies Created

Viewing 15 posts - 53,401 through 53,415 (of 59,089 total)

RE: Calculating Work Days

Heh... If you're going to copy code 100%, the least you could do is put my name on it 😉
First, why are you using this as a proc... why...
--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 26, 2007 at 12:33 pm

#762774
RE: Next Sequence Values

Lowell, as always, brings up some excellent points and I, too, hate these types of sequence tables. They're a real pain and if the code isn't written perfectly, 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)

December 26, 2007 at 12:09 pm

#762771
RE: query tool that supports client side variables?

The best thing to do would be for your organization to hucker down and fix the poorly written code by turning them into viable stored procedures or views.
Than notwithstanding, what's...
--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 26, 2007 at 11:31 am

#762759
RE: Stored Proc passing Table Name Fails - Help

You must use Dynamic SQL to do such a thing...
CREATE PROCEDURE [dbo].[sp_vcGetStop]
@CheckTable varchar(15),
@Sequence numeric(10,0)
AS
EXEC ('SELECT CUID As Rt, WorkType FROM ' + @CheckTable + ' WHERE Sequence = '...
--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 26, 2007 at 11:26 am

#762756
RE: INSERT INTO OPENROWSET

There is a registry setting that prevents SQL Server from being able to use OpenRowSet... look in the registry for "DisallowAdhocAccess" and change it's value to zero.
Also, OpenRowSet creates temporary...
--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 26, 2007 at 11:14 am

#762752
RE: calculate number of days

I agree with Steve, but the code is so simple I wouldn't make a stored procedure or function for it... too much overhead for such a simple thing.
--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 26, 2007 at 11:04 am

#762749
RE: do DBAS need local system admin privileges? and why?

Which type of DBA? If it's the "System" DBA, you'd better trust him/her... they need total access to really do the job correctly. If it's an "Application" DBA,...
--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 26, 2007 at 11:02 am

#762747
RE: Temporary Tables

I agree with Steve in avoiding ## Global Temp Tables... if two procs (or the same proc running more than once) attempt to create a GTT (Global Temp Table) 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)

December 26, 2007 at 10:59 am

#762746
RE: Primary Key

Although you can have only 1 Primary Key (hence the word "Primary"), you can, in fact, have multiple UNIQUE keys in the form of UNIQUE indexes. Keep in mind...
--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 26, 2007 at 10:57 am

#762745
RE: how to avoid sp_cursorprepexec and sp_cursorunprepare?

Without seeing the offending code, the short answer would be simply to rewrite the code so that it's performance enabled. Step 1... avoid cursors. Step 2... avoid 99.9%...
--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 26, 2007 at 10:51 am

#762742
RE: CTEs and Trees

Great code and explanations, but no test data...
--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 26, 2007 at 10:47 am

#762738
RE: Calculating Work Days

First, dunno how to post CF code on this forum mostly because I've never used CF.
Still need to see code... I see that you've created a function to use 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)

December 26, 2007 at 10:28 am

#762733
RE: Delete and Insert Blocking

Outstanding! Thank you so much for the feedback, Troy. Very happy to have been able 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)

December 26, 2007 at 10:23 am

#762732
RE: Alter / Update a Stored Procedure from within ASP.NET

vinno369 (12/26/2007)
Jeff, unfortunately dropping the stored procedure in to Master is not an option for me.
Then, unfortunately, you're gonna need some form of loop and some dynamic SQL with 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)

December 26, 2007 at 10:21 am

#762731
RE: Alter / Update a Stored Procedure from within ASP.NET

jacob sebastian (12/25/2007)
Jeff Moden (12/25/2007)
I know a lot of people hate the idea but this is where I'd say "enough is enough" and drop the proc into the Master database...
--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 26, 2007 at 10:11 am

#762727

Viewing 15 posts - 53,401 through 53,415 (of 59,089 total)