Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,226 through 24,240 (of 59,099 total)

RE: Copy from Excel file into SQL table using BCP

Koen Verbeeck (3/4/2014)
I'm not sure you can read Excel with bcp (you can write to Excel with bcp though).
You can use FROM OPENROWSET to read an excel file, or just...
--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)

March 4, 2014 at 3:03 pm

#1694117
RE: Remove non alpha chars from a column

I'll test these but similar code has been quite a bit slower in previous testing.
--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)

March 4, 2014 at 2:10 pm

#1694091
RE: How to shrink log in 2008 r2? URGENT!!!!!!

psamyn (3/4/2014)
Strange, why does ssms then suggest this line when you apply this on a lig file when you choose for the option shrink file.
I checked it on my test...
--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)

March 4, 2014 at 12:41 pm

#1694040
RE: How to shrink log in 2008 r2? URGENT!!!!!!

psamyn (3/4/2014)
This should do it also.
DBCC SHRINKFILE (N'<DATABASE_LOG_FILE_NAME>' , 0, TRUNCATEONLY)
To backup what Gail has said, the only time you want to shrink the log file to zero is if...
--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)

March 4, 2014 at 12:36 pm

#1694035
RE: random records

Personally, I use a dart board. 😛
--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)

March 4, 2014 at 12:32 pm

#1694032
RE: Query of 100 mil rows with multiple parameters

ChrisM@home (3/4/2014)
Jeff Moden (3/2/2014)
I guess my first recommendation would be to make the PK a nonclustered index. It's currently way too wide to be of any practical use as...
--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)

March 4, 2014 at 12:30 pm

#1694031
RE: DBA Dreams

SQLRNNR (3/3/2014)
phonetictalk (3/2/2014)
"I've encountered DBAs who seemed ill equipped to administer a home grocery list and checkbook"
This made me laugh out loud. Thankfully I haven't encountered such DBAs.
Yeah that 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)

March 3, 2014 at 4:36 pm

#1693727
RE: Data is upload into txt files through job

arooj300 (3/3/2014)
Hi All,
I have created stored procedure for checking the cpu usage and connection string.
----check the number of users---
EXEC sp_who2 'Active'
-----check the number of connection----
SELECT
DB_NAME(dbid)...
--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)

March 3, 2014 at 12:44 pm

#1693672
RE: Log size growing very fast

bubby (3/3/2014)
can I get a syntax to check the data file size and log file size please,
sp_helpdb dbnamehere
--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)

March 3, 2014 at 10:24 am

#1693645
RE: Issue with sp_prepare

MyDoggieJessie (3/3/2014)
To get around the cache bloat (and you may have already configured this), ensure you have the "optimize for ad hoc workloads" option enabled on your SQL Server.
OR... start...
--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)

March 3, 2014 at 9:42 am

#1693619
RE: Most Basic SQL Doubt - Why is it called a Table in the first place?

It comes from the second definition for "Table" in the dictionary...
a set of facts or figures systematically displayed, esp. in columns.
--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)

March 3, 2014 at 6:55 am

#1693542
RE: Creating a log table

aalianebhan (3/3/2014)
I want to create a (generic) trigger that will place into a logging table all adjustments to numerous (indeterminate) tables...
The log table appears generally like this:
TableName
TableKeyValue
ID identity
ChangeDate
ColumnName
OldValue
NewValue
Realized I'd just...
--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)

March 3, 2014 at 6:51 am

#1693540
RE: Time of Day Trigger

Tergum Rufus (3/2/2014)
Thanks everyone for responses. JM - it's for clients with SQL EXPRESS Editions
TR
Ah... got it. Thanks.
If you have Windows security running, SQLCmd does't require exposed credentials. ...
--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)

March 3, 2014 at 6:29 am

#1693534
RE: Trouble looping through a cursor

The cursor will become a performance and resource usage problem. Do you have someone on-site that could help you turn this into a proper "upsert"?
--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)

March 3, 2014 at 6:25 am

#1693531
RE: Lazy CPUs ?? 7 Idle, 1 maxed out

I don't believe this is a TempDB issue even if the symptoms say it is. I think it's either a resource intensive query that might need some tweeking because...
--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)

March 3, 2014 at 6:16 am

#1693525

Viewing 15 posts - 24,226 through 24,240 (of 59,099 total)