Jeff Moden

Forum Replies Created

Viewing 15 posts - 48,241 through 48,255 (of 59,099 total)

RE: Invalid views

rbarryyoung (9/18/2008)
Actually it's much easier than that and you don't need any evil Cursors either:
Declare @sql as NVarchar(MAX)
Set @sql = ''
Select @sql = @sql + 'EXEC sp_refreshview ''['
...
--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 18, 2008 at 10:40 pm

#874011
RE: finding file existence

It's even easier than that...
SELECT *
INTO #MyFiles
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
...
--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 18, 2008 at 10:33 pm

#874010
RE: How to trim the decimal places?

Much simpler than all that... check out the ROUND function or the STR function (which also right justifies if you need that for a report or file). 🙂
--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 18, 2008 at 10:27 pm

#874009
RE: Row to Column conversion

Heh... and the PIVOT operator is slower and uglier than the CrossTab that Chris showed. Think not? Check it out...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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 18, 2008 at 10:26 pm

#874008
RE: store output of stored proc into temp table

Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.
Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
...
--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 18, 2008 at 10:18 pm

#874007
RE: SQL script to import data from .txt table using BCP

Heh... absolutely... we need a copy of the file, a copy of the record layout, and copy of the CREATE statement for the target table, and any gotcha's that 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)

September 18, 2008 at 9:39 pm

#874003
RE: ~Split ~double~~symbol

karthikeyan (9/18/2008)
For what YOU did, yes. But you moved lines of code I posted out of order. You have to figure out where the Pipe character is first...
--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 18, 2008 at 9:29 pm

#874002
RE: Test Environment

In that vein, I normally make the test boxes less robust than the production server. If it's fast on the test box, imagine what it will do on 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)

September 18, 2008 at 9:10 pm

#873998
RE: last time table is accessed

Steve Jones - Editor (9/18/2008)
I'd rename the table. That will tell you if it's still being used.
Quick.
Heh... that's my favorite method. But, I believe the OP might have 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 18, 2008 at 9:07 pm

#873997
RE: last time table is accessed

bkDBA (9/18/2008)
Jeff Moden (9/18/2008)
Ummm... that'll find the last date a table was SELECTED from?
Yes, provided the new 2008 database audit events are set up:
http://blogs.msdn.com/euanga/archive/2008/05/28/sql-2008-how-do-i-tell-if-a-specific-table-is-still-being-used-in-an-app.aspx
2008 BOL says: "SQL Server audits consist...
--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 18, 2008 at 9:05 pm

#873996
RE: Trouble with Rounding Columns

Problem:
SELECT 1/10
Solution:
SELECT 1/10.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)

September 18, 2008 at 2:37 pm

#873926
RE: syntax error

dva2007 (9/18/2008)
You are creating a table or declaring a table? there is no syntax which you are using. Please use create table syntax.
Ever heard of "Table variables"?
--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 18, 2008 at 2:34 pm

#873923
RE: Joining the darkside

PaulB (9/18/2008)
abba (9/18/2008)
Syntax will probably be the least of your concerns.
:w00t: I respectfully disagree 🙂
I'll agree with that disagreement... syntax changes will be a doozy. Wait until...
--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 18, 2008 at 2:31 pm

#873921
RE: Can any one help me to prepare basic check list for database server for SQL server 2005.

Jason Wisdom (9/18/2008)
Tune the TempDB.
TempDB is so much more important in SQL 2005 than it was in earlier versions.
And the default settings of 8MB / 1MB with 10% growth 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 18, 2008 at 2:26 pm

#873920
RE: last time table is accessed

Ummm... that'll find the last date a table was SELECTED from?
--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 18, 2008 at 2:20 pm

#873917

Viewing 15 posts - 48,241 through 48,255 (of 59,099 total)