Jeff Moden

Forum Replies Created

Viewing 15 posts - 21,031 through 21,045 (of 59,098 total)

RE: Copying data between tables

richardmgreen1 (4/16/2015)
Hi Jeff
I did think of that, but it doesn't seem to persist.
This what I had 10 minutes ago:-
USE SystmOneData
EXEC sys.sp_MSforeachtable '
truncate table ?
begin try
set identity_insert ? on
end try
begin catch
end...
--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)

April 16, 2015 at 7:11 pm

#1791537
RE: best way to push records using linked server

mxy (4/16/2015)
what is the best way to push records using linked server. below is my query on Source server
insert into LSDestserver.DB.dbo.tablename
select * from #temp order by abc desc
I initially thought...
--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)

April 16, 2015 at 7:05 pm

#1791536
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Ummm.... are you sure that's ANSI Pig Latin??? 😀
--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)

April 16, 2015 at 5:57 pm

#1791532
RE: disk getting full

sql-lover (4/16/2015)
Zeal-DBA (4/14/2015)
my update query updating approx 50 million record which is causing transaction log to grow, and filling disk space and finally transaction is failing, please suggestany worl around.
My...
--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)

April 16, 2015 at 5:55 pm

#1791531
RE: Clustered Index

kk.86manu (4/16/2015)
Hi All,
Is it always the best practice to have the partition column also as the column for clustered index?
No. It seriously depends. If the clustered index 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)

April 16, 2015 at 5:52 pm

#1791530
RE: Auditing

Very cool. Thanks for posting what you found out. Not too many folks do that. I, for one, appreciate 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)

April 16, 2015 at 5:47 pm

#1791528
RE: Service Pack 1 for 2014 pulled temporarily

marc.heidemann (4/16/2015)
Notice: The SQL SSIS team has found an issue with SP1 installation if SSIS catalog is present in the SQL Server instance.They are currently investigating this issue including possible...
--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)

April 16, 2015 at 5:17 pm

#1791521
RE: Removing consecutive characters from the middle of a string

sKreetz! (4/16/2015)
Thanks again guys for the responses, and thank you lyncc! Does anybody know where I can find a complete list of sql functions with descriptions to study?
Yes. Sql...
--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)

April 16, 2015 at 5:06 pm

#1791519
RE: Rebuild / Reorg Index General Question?

SQL-DBA-01 (4/16/2015)
If you rebuild CLuster Index, by theory it also rebuilds all Non Cluster Indexes. Then why do we need to rebuild all Non Cluster Indexes? If only we can...
--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)

April 16, 2015 at 4:57 pm

#1791517
RE: Cleanup History has an extra output message. Unable to understand how it is appearing. Please help

As to your problem, you're taking the value of @@Rowcount before the EXEC for each command.
You've also made things a bit complicated. The following (for example)...
SET @rows_affected1 = (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)

April 16, 2015 at 4:53 pm

#1791516
RE: Cleanup History has an extra output message. Unable to understand how it is appearing. Please help

In a totally unrelated area, why are you using dynamic SQL for every command? It's not needed.
--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)

April 16, 2015 at 4:47 pm

#1791515
RE: Looking for a training book recommendation for SQL 2012

DataAnalyst011 (4/16/2015)
I have worked as a report writer with very little knowledge of the nuts and bolts of SQL (installation, setup, maintenance, optimization, etc.). My knowledge is very sporadic 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)

April 16, 2015 at 4:34 pm

#1791513
RE: How to create numerous directories on a server using data from sql table

Alan.B (4/16/2015)
What about a SQL Agent job that kicks off a script? Powershell perhaps?
If that's not an option, and if Lowell's solution (enable xp_cmdshell then disable when you're done)...
--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)

April 16, 2015 at 4:23 pm

#1791511
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Gary Harding (4/15/2015)
The (very small) fly in the ointment is that the 114 date style code in the CONVERT function returns milliseconds preceded by a colon rather than a period...
--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)

April 16, 2015 at 4:11 pm

#1791508
RE: How to create numerous directories on a server using data from sql table

GF (4/16/2015)
Thank you for the reply.
xp_cmdshell is disabled and our policy is to keep it disabled.
For SSIS I have no experience with SSIS.
Too bad. It's an incredibly useful tool...
--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)

April 16, 2015 at 3:49 pm

#1791505

Viewing 15 posts - 21,031 through 21,045 (of 59,098 total)