Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,936 through 49,950 (of 59,091 total)

RE: Combine selects to one row.

If you were to dump the data into a Temp Table with an IDENTITY column, you're life would get a lot easier on this problem.
--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)

June 17, 2008 at 10:42 pm

#830187
RE: Double Check

We do the double check thing for every piece of SQL that hits the database in the form of Peer Reviews. It's prevented a good number of "accidents" before...
--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)

June 17, 2008 at 10:38 pm

#830185
RE: Implicit conversion from data type datetime to varbinary is not allowed

Heh... Denial is a good word for this... 😀 I've seen lot's of folks try to make test data this way... Cross Join would suit better for 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)

June 17, 2008 at 10:27 pm

#830183
RE: How to round to nearest nickel?

Sergiy (6/17/2008)
Here's one of my favorites to go along with that...
declare @A decimal(3,0), @B decimal(18,0),@F FLOAT
SET @A = 3
SET @B = 3
SET @F = 3
SELECT 1/@A*3.0, 1/@B*3.0,1/@F*3.0
SELECT 1/@A*3, 1/@B*3,1/@F*3
--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)

June 17, 2008 at 10:16 pm

#830180
RE: Virtual PM's for Jeff...

Heh... I've got 100 PM's that I really want to keep... and Steve hasn't developed a way for me to download them, yet and I've been too busy to think...
--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)

June 17, 2008 at 9:24 pm

#830176
RE: Theoretical question about importing from Excel using SSIS

Oh yeah... I forgot the "other part"... Dirtree...
Lots of people know that Dirtree can take 2 parameters... one for Directory and 1 for how many levels deep to go... Very...
--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)

June 17, 2008 at 9:02 pm

#830173
RE: Theoretical question about importing from Excel using SSIS

Found 'em...
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=yes',
'SELECT * FROM [Sheet1$a4:c]')
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=Yes',
'SELECT LastName,FirstName FROM [Sheet1$a4:c]')
... and I've attached the example spreadsheet I used in 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)

June 17, 2008 at 8:56 pm

#830172
RE: Get date of last Sunday of every month

catriona_brown (6/12/2008)
I figured it out. An no still not an exam question.
C'mon... don't be shy... everyone else posted their solution... it's your turn... 🙂
--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)

June 17, 2008 at 8:46 pm

#830169
RE: BCP

Get something that's capable of opening the file in the "binary" mode... I'm betting that the end of line character is neither a carriage return (0d in hex) nor 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)

June 17, 2008 at 8:39 pm

#830166
RE: Query gone haywire

So... are you required to use the ZipCode/State view? If not, call the tables directly, instead. Also, Views of Views generally suck the life out of code... avoid...
--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)

June 17, 2008 at 8:20 pm

#830160
RE: There must be an easier way - 600k INSERTs

jcrawf02 (6/17/2008)
My manager has been fighting with our corporate folks to try and get us a dedicated environment where we can build our own tables and test suites, this may...
--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)

June 17, 2008 at 8:14 pm

#830158
RE: Theoretical question about importing from Excel using SSIS

OpenRowSet does this very nicely... and with a little help from our friend "Dirtree", you can make it "auto-magic". Lemme see if I can find my sample script 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)

June 17, 2008 at 8:07 pm

#830155
RE: Implicit conversion from data type datetime to varbinary is not allowed

Matt Miller (6/17/2008)
If the two tables weren't created in exactly the same way, you could easily end up with column mismatches.
Good point... but look again...
insert into t
select *...
--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)

June 17, 2008 at 8:03 pm

#830153
RE: Assign seqnumber to a column

Matt Miller (6/17/2008)
Take a look at this article - will help describe the technique....
Man... you're right... you have been recommending this and the Tally table articles a lot! 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)

June 17, 2008 at 7:55 pm

#830150
RE: combining rows with common index values

Dupe post... please continue at the following so all the answers stay in the same place...
http://www.sqlservercentral.com/Forums/Topic518657-149-1.aspx
--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)

June 17, 2008 at 7:52 pm

#830149

Viewing 15 posts - 49,936 through 49,950 (of 59,091 total)