Jeff Moden

Forum Replies Created

Viewing 15 posts - 22,561 through 22,575 (of 59,099 total)

RE: TSQL Query Help

I had eye surgery today and can't see very well, yet. I'll have to get back to this. Hopefully, someone will jump in in my place because I'm...
--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)

October 30, 2014 at 10:04 pm

#1755960
RE: do you have a StripNonNumeric ITVF function?

Now we're cooking with gas! Well done! I had eye surgery today (lens replacement) and can't see so well yet and can't spend much time in front 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)

October 30, 2014 at 9:58 pm

#1755959
RE: Database design of more years

tony28 (10/29/2014)
vincyf1 (10/29/2014)
My Suggestion would be to use Table Partitioning instead of new DB creation every year.
I know this function, but I tried one times if I learned sql 2years...
--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)

October 29, 2014 at 8:02 pm

#1755665
RE: Case in SQL

Just to know, how many possible values do you have for the Server_Excluded column? Does it allow NULLs, as well?
--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)

October 29, 2014 at 7:51 pm

#1755664
RE: Need some help with the error.

It sounds like your app may have a connection leak (same symptoms as what we have at work but we can't change it... 3rd party hairball code). Less likely...
--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)

October 29, 2014 at 7:49 pm

#1755663
RE: Copy SSRS reports from Dev to Stage to Prod.

Thanks for the tip. I found the following website on the subject.
http://execsql.org/rs-scripter-tool-handy-tool-to-create-deployment-script-for-reporting-services
One of the steps is to provide a path the RS.exe file so I think it's purely an...
--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)

October 29, 2014 at 6:31 pm

#1755660
RE: TSQL Query Help

inayatkhan (10/29/2014)
If object_id('tempdb..#LD') is not null drop table #LD
create table #LD(ID int identity, LA_Type_ID int, LA_Value int, LID bigint unique, MText varchar(255))
insert into #LD values
(22901, 1652, 1231, 'ABC'),
(22901, 1652,...
--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)

October 29, 2014 at 5:14 pm

#1755656
RE: Show missing date

Robert klimes (10/29/2014)
imfairozkhan (10/29/2014)
using calendar table is the only way???
you could dynamically create calendar table with recursive cte but I am not sure it would scale well on a large...
--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)

October 29, 2014 at 1:28 pm

#1755631
RE: Large mdf

SqlScared (10/29/2014)
Thank you Gail and Gianluca. The data files are in separate filegroups so I'm out of luck.
Maybe not. Transfer some of the tables to the other 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)

October 29, 2014 at 1:17 pm

#1755630
RE: do you have a StripNonNumeric ITVF function?

Alan.B (10/29/2014)
So I made the following change to Eirikur's function to so that it takes a pattern as a parameter:
Interesting... (our posts crossed paths). I'll take a look at...
--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)

October 29, 2014 at 9:26 am

#1755556
RE: do you have a StripNonNumeric ITVF function?

First, hats off to Eirikur Eiriksson for the reminders about the performance of dedicated rather than general purpose functions and the speed associated with integer comparisons rather than character-based comparisons....
--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)

October 29, 2014 at 9:24 am

#1755554
RE: Need Create View Script for all tables

Paul White (10/28/2014)
Jeff Moden (10/28/2014)
[...]The way to prevent the problem of someone missing the "column shift" problem if someone changes the underlying table is to create the table using...
--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)

October 29, 2014 at 9:10 am

#1755551
RE: Copy SSRS reports from Dev to Stage to Prod.

That's great amplifying information. I'm also glad to see that you can also promote data sources with the understanding that you might need to change some credentials. I'm...
--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)

October 28, 2014 at 9:26 pm

#1755401
RE: Need Create View Script for all tables

DonlSimpson (10/28/2014)
In case anyone thinks this example is a good idea I just want to add my vote to what Sean already suggested, namely: Never, never use SELECT * 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)

October 28, 2014 at 9:21 pm

#1755399
RE: do you have a StripNonNumeric ITVF function?

Eirikur Eiriksson (10/28/2014)
Little clean up in the logic, now it looks pretty good
😎
/********************************************************************
-- Stripping out any non-numerical characters
-- EE 2014-10-28 Inital coding
-- EE ...
--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)

October 28, 2014 at 9:08 pm

#1755398

Viewing 15 posts - 22,561 through 22,575 (of 59,099 total)