Jeff Moden

Forum Replies Created

Viewing 15 posts - 57,811 through 57,825 (of 59,101 total)

RE: duplicate entries in a field

Naga's update is good and nasty fast... it just needs a filter...
update Customer
set address = LEFT (address, LEN(address) - CHARINDEX(',', REVERSE(address)))
WHERE LEN(Address) - LEN(REPLACE(Address,',','')) = 2
--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)

September 2, 2006 at 10:31 pm

#658189
RE: How to tell if a character exists

Don't forget that ISNUMERIC sometimes allows an "e" or "d" and will also allow decimal points and dollar signs and etc, etc....
The method Ken uses is nasty fast and is...
--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)

September 2, 2006 at 10:17 pm

#658188
RE: Need help with date comparison

First of all, you shouldn't be inserting "Owner" into the Audits table because that's a form of denormalization you don't need here... bit, if you insist, here's a self supporting...
--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)

September 2, 2006 at 10:06 pm

#658187
RE: Build Concatenated List of Values

Good tool... bad answer ... you still haven't answered the "generic" question. David got it, though...
(and it appears that you are not paying for this form of advertising which might...
--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)

September 2, 2006 at 9:33 pm

#658186
RE: Help in T-SQL Query

SELECT i.*
   FROM IndSales i
   LEFT OUTER JOIN mstBranch b
                ON i.BankCode   = b.BankCode
               AND i.BranchCode = b.BranchCode
WHERE b.BankCode IS NULL
--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)

September 2, 2006 at 9:28 pm

#658185
RE: Can this Query be tuned ? God knows !!!

Try running the SELECT portion by itself... if the performance is similar to the performance of the whole INSERT/SELECT, then you might try using the INDEX TUNING WIZARD (and 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)

September 2, 2006 at 10:39 am

#658177
RE: Swaping Columns in Single Query

Just for grins...
DECLARE @Num1 FLOAT
DECLARE @Num2 FLOAT
SET @Num1 = -1.2
SET @Num2 = -3.4
SELECT Largest = (ABS(Num2-Num1)+(Num2+Num1))/2
--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)

August 26, 2006 at 1:52 pm

#657005
RE: Why is this not working?

Dunno if this is what you are looking for... and I haven't tested it 'cause I'm on vacation with no access to SQL...
declare @recipe table (recipeID numeric(18,0) NOT NULL)
insert...
--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)

August 26, 2006 at 1:34 pm

#657004
RE: Correlated subquery column referencing outer date range

I don't have access to SQL Server right now so I couldn't test this... I think it may work...
select
     OrderDate,
     ShipCountry,
     ShipCity,
     Freight,
     /* how do I get...
--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)

August 26, 2006 at 12:21 am

#656989
RE: Problem with control error in fecth

Thanks for the feedback, Gopi...
--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)

August 25, 2006 at 7:12 am

#656813
RE: create fixed width file using BCP

Ok... I get it... David Burrows beat me to the punch... take a look at his posting... I think that may be what you want. When you re-import, you'll probably...
--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)

August 23, 2006 at 10:09 pm

#656487
RE: Difficult data transformation problem..
Yep... copy and paste error... the cascade rename should look like...
```
--====== Cascade rename the tables.... (takes about 65 milliseconds)
TRUNCATE TABLE dbo.INV_Barcodes --Saves log space because is not logged
    DROP 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)
August 23, 2006 at 10:01 pm

#656486
RE: Trigger Audit Trails - who deleted my record?

That won't help either (the Created_by) because it is not automatically updated during an update unless you have a trigger to do that... and, as you pointed out, even that...
--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)

August 23, 2006 at 7:36 pm

#656479
RE: Query from a Dynamically built string representing a Server.Databasetable

Move all DECLARE's to the beinning of the function where they belong.... you cannot include DECLARE's in a WHILE LOOP.
--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)

August 23, 2006 at 7:16 pm

#656478
RE: Is there a lastmodified date stored in sql server when a s_proc is changed?

There is no "modify date" for schema changes in SQL Server 7, 2000, or 2005. You can however, add a new database trigger in SQL Server 2005 that will allow...
--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)

August 23, 2006 at 7:10 pm

#656477

Viewing 15 posts - 57,811 through 57,825 (of 59,101 total)