Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,741 through 40,755 (of 59,102 total)

RE: Replace cursor with while loop / compare cursor with while loop / optimize cursor

TheSQLGuru (12/10/2009)
I haven't checked for certain Jeff, but I have seen these types of manipulations make proper dates for range queries:
SET @ldtStartDate = ISNULL(@dStartDate,DATEADD(month,-1,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))
SET @ldtEndDate = ISNULL(@dEndDate,DATEADD(ms, -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)

December 10, 2009 at 9:22 am

#1090651
RE: What do I do now? (have fun with this...)

I was thinking the same thing, Gus... that's why I posted the retrospective. It took me a while but I remembered running into someone like this 10 or...
--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 10, 2009 at 9:19 am

#1090648
RE: Regarding FindString()

Sorry Jags... I just don't know that much about SSIS. Hopefully, someone will see this and lend a hand.
As a sidebar, I typically do this type of stuff in...
--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 10, 2009 at 7:08 am

#1090519
RE: spliting the data using replace function

I won't be able to get to this until I get home from work. If you study the code I've offered so far, you'll probably figure it out 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)

December 10, 2009 at 7:06 am

#1090517
RE: Manipulating Decimal Format 0000000090.00000

Thanks, Arun... the only thing I haven't done is test it for performance... that's because I don't normally do such formatting in SQL Server. The only time I do...
--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 10, 2009 at 7:03 am

#1090513
RE: File move

igloo21 (12/10/2009)
CirquedeSQLeil (12/9/2009)
Have you considered using a maintenance plan, or an SSIS package to do this?
Both seem like viable alternatives as well (in addition to Steve's suggestion).
Yes I have but...
--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 10, 2009 at 7:01 am

#1090508
RE: Cannot bulk import CSV file with double quotes

Yep... you've got it. Select/Into FROM OpenRowSet also works.
You mentioned this was for big files. I'd first see if I could get the vendor to make the delimiters...
--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 10, 2009 at 6:57 am

#1090502
RE: Regarding FindString()

I'm not sure what it is in SSIS but, in T-SQL, CHAR(9) is the tab character. In some places, it may be just \t or "\t".
--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 10, 2009 at 12:52 am

#1090386
RE: Cannot bulk import CSV file with double quotes

This should do it... use the SELECT as if it were a table...
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
...
--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 10, 2009 at 12:47 am

#1090385
RE: Manipulating Decimal Format 0000000090.00000

You can actually do it a tiny bit simpler...
SELECT REPLACE(STR(350,16,5),' ','0')
STR returns NVARCHAR values.
--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 10, 2009 at 12:39 am

#1090384
RE: How to do a Grouped, Comma Separated List

FredS-1001785 (12/9/2009)
This it faster for the operator to scan with their eye for what they are looking for.
Ah... finally... someone with a practical reason. Thanks, Fred.
--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 10, 2009 at 12:30 am

#1090379
RE: Urgent Help required

I do understand... that's why I you should post the data in the format requested... you'll get help a lot faster.
--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 10, 2009 at 12:28 am

#1090377
RE: spliting the data using replace function

You bet, Anitha. Thank you for the feedback.
--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 10, 2009 at 12:26 am

#1090376
RE: Regarding FindString()

Why don't you split it on the way in? I'm no SSIS wizard, but I believe they have a BULK task of some sort to load and split files...
--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 10, 2009 at 12:26 am

#1090375
RE: Resulting message whit Bulk Insert

I'm pretty sure that it's not working correctly at all. The default delimiter for BULK INSERT is the tab character and you're using something else. You've also not...
--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 10, 2009 at 12:21 am

#1090373

Viewing 15 posts - 40,741 through 40,755 (of 59,102 total)