Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,676 through 6,690 (of 59,072 total)

Reply To: NPI Data

It worked find for me as just \ n (without the space). The 0x0a thing will also work. For those that are reading this and don't know, that is 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)

July 30, 2020 at 6:32 pm

#3776079
Reply To: SSMS Generate Script Replaces Quoted Identifiers with Square Brackets

Jason A. Long wrote:
I know it's an option in SQL Prompt... but... I do not think the inclusion/exclusion of quoted identifiers is a configurable scripting setting in SSMS.
Ah... my apologies. It's only...
--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)

July 30, 2020 at 6:26 pm

#3776077
Reply To: SSMS Generate Script Replaces Quoted Identifiers with Square Brackets
Steve Jones - SSC Editor wrote:
Andy Mallon has a procedure to remove them.
https://am2.co/2018/07/removing-square-brackets-from-generated-ssms-scripts/
~~It's no longer needed if you have the later versions of SSMS. It's actually a setting now and, IIRC, it also...~~
- This reply was modified 5 years, 11 months ago by Jeff Moden.
--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)
July 30, 2020 at 5:06 pm

#3776066
Reply To: Faster Integrity check

Admingod wrote:
I did run some tests. On SQL 2014 it is taking approx 8 - 10 hrs and on SQL 2017 it is taking less than 2 hrs. I can't...
--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)

July 30, 2020 at 5:04 pm

#3776063
Reply To: The Computing Revolution

Heh... "Computing Revolution". I call it the "Dust Revolution" because data and code is just like dust... it accumulates on horizontal surfaces (and sometimes sticks to vertical surfaces in 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)

July 30, 2020 at 4:52 pm

#3776054
Reply To: SSMS Generate Script Replaces Quoted Identifiers with Square Brackets

It's the nature of the beast and, to be honest, the brackets are the preferred method (in SQL Server, anyway) for using reserved words and object names that contain spaces,...
--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)

July 30, 2020 at 3:21 pm

#3776034
Reply To: NPI Data

BTW... I left a bit of a test in the table creation code and you should change it. The first column should be a BIGINT and not VARCHAR(11).
--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)

July 30, 2020 at 3:14 pm

#3776031
Reply To: NPI Data

p.s. Try it with my changes to the table and the BULK INSERT command first without doing the powershell stuff. I don't know if I just had a bit 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)

July 30, 2020 at 1:38 pm

#3775993
Reply To: NPI Data

The Powershell code will work on any PC. Although it doesn't need to be, I installed it as a .bat file in the same directory as the file. That makes...
--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)

July 30, 2020 at 1:36 pm

#3775992
Reply To: NPI Data

Ok... I got this working BUT...
The bastards lied on their record layout. The "HEALTHCARE_PROV_TAXONOMY_GROUP_xx" columns aren't 10 wide in the data... they're up to 43 characters wide. I changed the...
Attachments:
You must be logged in to view attached files.
--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)

July 30, 2020 at 4:39 am

#3775898
Reply To: NPI Data

That particular problem is because there's a head row that you're not skipping. Add the following to the options in the BULK INSERT command...
FIRSTROW = 2,
I tried the same thing...
--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)

July 30, 2020 at 2:09 am

#3775886
Reply To: SSIS 4 Minute Job Takes Over 19 *Hours* on New Server

Speaking of tenacity, that's some Awesome feedback! Thank you for taking the time to share what you found all across the board... especially the 4 space duplicate key error. I've...
--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)

July 29, 2020 at 11:04 pm

#3775863
Reply To: The Old Way or the New Way

For me, it's not a matter of being resistant to change or something new. My problem is that I've seen all manner of supposed "improvement" and supposed "better ways to...
--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)

July 29, 2020 at 7:46 pm

#3775794
Reply To: How to Identify Slow Code

jakegordon1997 wrote:
In a specific Query, how can you easily identify how long each piece of code is taking? My query is taking over 20 minutes so I can't just comment...
--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)

July 28, 2020 at 8:18 pm

#3775495
Reply To: Insert all columns except for Identity ID

To add to Phil's correct answer, if there are a lot of columns, open the Object Explorer window, locate the table you need the column names for, find the "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)

July 28, 2020 at 8:13 pm

#3775494

Viewing 15 posts - 6,676 through 6,690 (of 59,072 total)

Jeff Moden

Forum Replies Created

Attachments: