Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,286 through 24,300 (of 59,102 total)

RE: Datetime difference converted to int does not return same result as datediff

Koen Verbeeck (2/26/2014)
If you convert Jan 31 2014 11:59PM to int and then back to datetime, you get Feb 01 2014 12:00AM. So it is a rounding "error", resulting 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)

February 26, 2014 at 9:19 pm

#1692518
RE: SQL Insert with Identity value AND incremental rollover column ?

Avoid the RBAR. Make a persisted computed column that uses a formula like MTRANS_ID%999+1. Yeah... it'll have gaps, now and again... just like the IDENTITY column
--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)

February 26, 2014 at 9:06 pm

#1692516
RE: Replace .TIF with .pdf

amandaallen243 (2/26/2014)
2Tall (4/12/2013)
Hi to all. I want to update a column that links to an external document. The external documents have been converted from .TIF[/url] to .pdf[/url]. The file name...
--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)

February 26, 2014 at 9:03 pm

#1692515
RE: Fixing CREATE TABLE

There are two answers that I've grown accustomed to when working with any flavor of SQL. The first, of course, is "It Depends". The second one, which seems...
--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)

February 26, 2014 at 8:57 pm

#1692514
RE: DB will become read only. We cannot make any modifications here, How to avoid this scenario

David Webb-CDS (2/26/2014)
Several possible ways to prevent the log filling:
Insert the records in batches and take log backups between the batches.
Switch the DB to 'Simple' recovery mode, insert in batches,...
--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)

February 26, 2014 at 8:50 pm

#1692513
RE: DB will become read only. We cannot make any modifications here, How to avoid this scenario

sankepalli (2/26/2014)
Hello Folks,
Suppose, there are 1 million records, if we insert all these records in the log file, it will become full & DB will become read only. We cannot...
--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)

February 26, 2014 at 8:39 pm

#1692512
RE: Invalid Cursor Position - No Cursor in Use...

S-I-M-P-L-I-F-Y 😉
I don't guarantee this will work because I don't have your tables to test it on but it should be pretty close. The key here is (as 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)

February 26, 2014 at 8:22 pm

#1692509
RE: Hours Calculation

waheed71 (2/26/2014)
Dear All,
I will appreciate if you can help me to write hour calculation report based on number of In and Out from site. The Problem I am facing here...
--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)

February 26, 2014 at 7:26 pm

#1692500
RE: SQL 2008 R2 - Need to create a CLR assembly that calls a Java function and implement as a UDF

sgmunson (2/26/2014)
The source code for the function is proprietary, and thus, unavailable. The number of rows it would apply to would be relatively small. As 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)

February 26, 2014 at 7:10 pm

#1692498
RE: Effect of Adding SYSADMIN Server Role

yocki (2/25/2014)
yes Jeff. you are right. I know i cant give SYSADMIN to some one else.. its just because the operators don't really have good understanding about sql. I grant...
--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)

February 25, 2014 at 10:53 pm

#1692131
RE: Caching

I've had the Developers cache semi-static and static data several times rather than calling stored procedures or doing embedded T-SQL to get the data. For the things I've used...
--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)

February 25, 2014 at 9:41 pm

#1692128
RE: Effect of Adding SYSADMIN Server Role

yocki (2/25/2014)
Hi Experts,
What are the effects of adding SYSADMIN Server Roles ?
I have this user called "SDE". It has PUBLIC Server Roles. Then I add SYSADMIN. All of a sudden,...
--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)

February 25, 2014 at 9:30 pm

#1692126
RE: Insert with self reference

ringovski (2/25/2014)
Thanks a lot chris, i have never heard of cross join before works.
It's not a CROSS [font="Arial Black"]JOIN[/font]. That's something completely different than the CROSS [font="Arial Black"]APPLY [/font]that...
--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)

February 25, 2014 at 9:26 pm

#1692125
RE: Parse Info Out of FileName

This should handle pretty much anything you can throw at it.
-- DROP TABLE #FileName
--===== Create a test table with some normal and "odd" file/path names.
SELECT FileName = 'C:/Location/Folder/Subfolder/filename20130101.xxx'
...
--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)

February 25, 2014 at 9:18 pm

#1692123
RE: Table Partitioning for 500gb in size

AK1516 (2/25/2014)
1. The first thing to ask is, what kind of table is it? Is it an OLTP table or an audit table where rows are written but never updated?
2....
--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)

February 25, 2014 at 8:47 pm

#1692122

Viewing 15 posts - 24,286 through 24,300 (of 59,102 total)