Jeff Moden

Forum Replies Created

Viewing 15 posts - 48,541 through 48,555 (of 59,099 total)

RE: conversion error

Heh... do you think that 150,000,000,000 is gonna fit in an INT in SQL Server? 😉
--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 30, 2008 at 4:14 pm

#865414
RE: OPENROWSET select statement performance -- runs in 4 seconds on test server vs. 3 minutes on production server.

Does the Oracle table have the same number of rows in test and prod? Does the Oracle table in test and prod have the same indexes? When 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)

August 30, 2008 at 4:11 pm

#865413
RE: allow varchar value to string and decimal parts

Just a thought... why not keep the alpha and numeric portions separate and combine them only in a calculated column??? Then you wouldn't have to mess with all 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)

August 30, 2008 at 4:05 pm

#865411
RE: allow varchar value to string and decimal parts

Mike Mullen (8/29/2008)
Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.
Yes, it...
--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 30, 2008 at 4:03 pm

#865410
RE: allow varchar value to string and decimal parts

sqluser (8/29/2008)
Hi,
try the below one.
DECLARE @Text VARCHAR(16)
SET @Text = 'ABC222'
SELECT SUBSTRING(@Text,PATINDEX('%[0-9]%',@Text),LEN(@Text)) + 100
---
Doesn't return the "ABC" part... 🙂
--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 30, 2008 at 3:54 pm

#865408
RE: BULK UPDATES in 2005

Here's the times on my box
P5 1.8 GHZ, twin 80GB IDE hard drives, 1GB Ram
208 Update
629 Cursor
--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 30, 2008 at 3:23 pm

#865404
RE: String or binary data would be truncated.

rbarryyoung (8/30/2008)
balbirsinghsodhi (8/29/2008)
It's kind of wired error. when I create stored procedure on one of the database I get following error.
String or binary data would be truncated.
but when I create...
--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 30, 2008 at 1:57 pm

#865398
RE: BCP out 41 tables

raym (8/29/2008)
I've attached the the spreadsheet with the tables I want to bcp out
Your largest table contains 1.2 BILLION rows... the average output for BCP in the native format 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)

August 30, 2008 at 1:52 pm

#865397
RE: BULK UPDATES in 2005

I've gotta go with Gus on this one... even the seemingly unsurmountable task of doing a running total can be done at extremely high speeds, even in SQL Server 2000,...
--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 30, 2008 at 9:54 am

#865378
RE: Problem with the text data column, variable

I gotto go with Jack on this one... use a Tally table instead of a While loop. In fact, here's an article on what a Tally table is and...
--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 30, 2008 at 9:48 am

#865377
RE: String or binary data would be truncated.

Damned smiley faces... 🙂
Ok... here it is again without the smiley faces... it's not as "pretty", but it'll do...
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used 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)

August 30, 2008 at 9:04 am

#865370
RE: String or binary data would be truncated.

balbirsinghsodhi (8/29/2008)
Sure, here is the procedure. if tried creating this procedure in small segment but it is failing at following area but I can create this table without any 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)

August 30, 2008 at 8:56 am

#865366
RE: TSQL LAB 2 - Writing Conditional WHERE Clauses

You're parsing 200 million rows of data in 0.4 seconds or less? Gotta see that one... would you mind posting the code?
--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 29, 2008 at 1:36 pm

#865242
RE: How to calculate running balance for a requested date in reports using Reporting Services 2005?

All of the methods used where a correlated sub-query uses the < or <= relationship is doomed to performance failure. If you want a very high speed (1 million...
--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 29, 2008 at 10:59 am

#865156
RE: What do you think about four days workweek?

Heh... if you're already working 10 or 12 hour days, a 4 day work week would be very welcome! 😉
--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 29, 2008 at 10:50 am

#865150

Viewing 15 posts - 48,541 through 48,555 (of 59,099 total)