The Heap™ – Consultancy ©® - 2014-10-07 (page 1 of 2)

billinkc

01:16

Work of art

Also, I need to install snagit on this VM

swasheck

02:44

@AaronBertrand ... @Zane ...

►

user285oo6

06:07

hi

Andriy M

07:09

What could they possibly mean?

Use your string space and tab many times and see the result of position. it gives wrong position. — Navneet 31 mins ago

Simon Righarts

07:52

@AndriyM Could be he's expecting a tab character to count as four or eight characters when in actuality it's only one? Other than that I got nothing

Andriy M

@SimonRigharts Indeed, he might be talking about visible placement on the screen, I didn't think of that. Hopefully, his reply can clarify that.

ypercube

09:08

This table has no keys, constraints or indexes. Why is my query slow?

0

Q: Tuning performance on SELECT statement from a medium-large table

I've got a table with 45 columns and 600,000 rows. I need to tune up a SELECT statement from this table, which for now lasts about 41 seconds. I need to return all the columns and i don't use *. This table has no keys, constraints or indexes. It also has no unique value which could be used as a p...

ypercube

09:56

Tell me about bad practices. stackoverflow.com/questions/26231261/…

Sachin Verma

Anyone who can help me with a complex query?

It might take just a minute for an expert

ypercube

@SachinVerma Add a question at the site.

Sachin Verma

@ypercube can you help me here? It needs a conversation to understand. Please help me

@ypercube you are awesome, you might be able to help me within a minute. Mind if I ask?

Phil

11:59

Kill, shows no effort:

-2

Q: oracle sql-developer specific query required

DELIMITER $$ DROP FUNCTION IF EXISTS `GetAncestry` $$ CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024) DETERMINISTIC BEGIN DECLARE rv VARCHAR(1024); DECLARE cm CHAR(1); DECLARE ch INT; SET rv = ''; SET cm = ''; SET ch = GivenID; WHILE ch > 0 DO ...

sql oracle oracle-10g oracle-sql-developer

bluefeet

There seems to be an increase of 1 rep users posting questions on dba. I wonder where dba.se has been linked.

ConcernedOfTunbridgeWells

How would people feel about pre-christmas drinkies on 7 or 14 Nov?

3

ypercube

12:21

@AaronBertrand This question is similar to some of which you have answered. Not sure if I spotted the best one to be used as a duplicate target.

1

Q: Unexpected output when using "where" condition after an inner query

I was trying out a query and it was returning an error which I cannot explain. (5 th query ). Can someone help me understand what is causing the error. DROP TABLE #temp SELECT * INTO #temp FROM ( SELECT 'A_100' Col union all SELECT 'A_101' union all SELECT 'A_102' union all...

sql-server tsql

JNK

morning

bluefeet

morning @JNK

Kermit

13:03

morning @JNK @bluefeet

Aaron Bertrand

@ypercube I did a quick search and didn't spot a better candidate so I closed it. Should also suggest TRY_CONVERT if 2012+ (I'm going to edit my answer)

@swasheck awesome

@ypercube edited here is probably the better dupe (too late for me to fix it now):

5

A: Why would YEAR fail with a conversion error from a Date?

I assume that RValues is a string column of some type, for some reason. You should fix that and store date data using a date data type (obviously in a separate column than this mixed bag). If you can't fix that, then you can prevent what Damien described above by: CASE WHEN ISDATE(RValues) = 1 ...

@bluefeet any chance you can make this question point to this duplicate instead (or in addition, if that is possible after the fact)?

bluefeet

13:27

@AaronBertrand you want the older one to be a dup of the newer one?

Aaron Bertrand

Oh crap, hold on, bad clipboard

Sorry, it is this question that I closed as the wrong duplicate. I think this answer describes the problem and lists better workarounds more concisely. IMHO.

It's about dates and not ints, but the underlying problem is the same.

bluefeet

corrected the dup

Aaron Bertrand

Damn these gold-badge-earned-binding-close-votes

bluefeet

I believe you could reopen it with a single gold vote

Aaron Bertrand

@bluefeet thank you

@bluefeet true but then I couldn't VtC again anyway

(and that would leave it open and subject to more answers in the meantime)

bluefeet

13:31

I know, the sadness of vtc

my mod powers can be helpful sometimes

billinkc

13:48

Dupe, de dupe de dupe

0

Q: Identical Servers but different query execution plans

We have two server with identical hardware configuration and SQL server configuration. The only difference in the server's being: Server SQL1 - 256 GB RAM Server SQL2 - 128 GB RAM The same database has been restored on both servers. The query execution plan for a query on both servers is diffe...

sql-server query-performance

Aaron Bertrand

@billinkc what happened to that restore to dev/prod stuck in restoring question? Can't find it now - assume the OP deleted it

billinkc

That was what I was looking for. Was too lazy to check my tweets

Guessing the restore finished or they went to polish their resume

Aaron Bertrand

Well the funny thing was, ok, you clicked the wrong button in the UI somewhere, but presumably you still have the backup, so you can just start over

Unless you tried restoring the dev database as a replacement of the production database. That's a resume editing event.

billinkc

dba.stackexchange.com/questions/78524/…

Aaron Bertrand

How did you find it?

billinkc

14:03

Moorehouse had asked to see the post

ypercube

@ConcernedOfTunbridgeWells probably fine, not sure yet. I'll be away for a week in November but haven't booked yet. Fix the date and I can arrange my schedule.

Michael J Swart

14:25

0

Q: How do I Continue After a Failure in SSIS

I want to implement something like the following: In this package, I want to loop through a list of database names, dynamically modifying connection manager settings. However, the first failure on "Test Connection" will halt and fail the package execution. But I'd like to continue my Foreach ...

ssis-2012

Any thoughts SSIS folks?

Lamak

you need to edit the MaximumError thingy

so, even if some test connection fails, the foreach continues to run

Michael J Swart

That's listed as among the things I've tried. Do I have to do it for all parent containers and the package?

Lamak

@MichaelJSwart yes

Michael J Swart

Does 0 mean unlimited errors? or do I have to specify something like 9999999

Lamak

I mean, you need to change that property on the "Test Connection", the "Foreach" and the package

^^^^^^don't really know if all of them are necessary, but you should try

@MichaelJSwart don't remember if 0 works

billinkc

14:31

SSIS is for dumbdumbs

@MichaelJSwart Is there a reason you're using an Execute SQL task to test whether the connection works vs a script task? Easier to control the failure there

Lamak

why do you need to criticize everything dude?

joking

Michael J Swart

... 0 works, and setting maxerrorcount = 0 in all parent containers and packages works too as I wanted (yay)

Lamak

hooray

Michael J Swart

@billinkc Okay, so why is a script task easier? I don't understand how the flow control is any different. And the test connection sql is just "SELECT @@SERVERNAME"

Lamak

@MichaelJSwart because that way you can control the failure so the script taks doesn't fail

If I had to guess (or doing it myself)

Michael J Swart

14:40

So the script task doesn't fail, then what? We do flow control with variables? It doesn't sound easier than a Execute SQL task.

Okay, I see. we adjust these precendent constraints as an expression rather than success/completion/failure

Lamak

Well, you can control the flow with variables yes. And you also don't need to set the MaximumErrorCount to 0, which can be fairly dangerous if some other thing fails. But, I can't say what was the specific thing that @billinkc wanted to say

@MichaelJSwart yes

Michael J Swart

Okay, thanks guys (over and out)

billinkc

Lamak is correct on all accounts. My theory was that with a Script Task, you were in control of your own destiny versus trying to stamp down the default SSIS behaviour of raising an error

Once I have a few minutes, I'll gin up a suitable package to demonstrate both approaches

Lamak

@billinkc nice

Zane

14:56

Well that all got wrapped up nice and easy.

I am working at the Minneapolis office and it is Amazing!

I biked to work.

And we are in a basement level that is completely abandoned.

Lamak

cool

Zane

I already built a cube fort that I'm going to each my lunch in later.

Lamak

that sounds fun

Zane

Indeed. I even found a foam Soccer ball. For someone as juvenile as I am this is quite fantastic.

Lamak

is it a permanent relocation?

Zane

15:02

Nah. Just on Tuesdays.

I'm officially back on the ETL team. Half of them are out here so they come out Friday and we go out Tuesday.

Lamak

ah, ok, still sounds fun

Zane

@billinkc quick question about 2012 SSDT

NVM got it.

billinkc

Glad I could be of help

Zane

It was nice of you to go out of your way.

Lamak

he's a good guy

Zane

15:13

So he keeps saying.

I have my doubts.

JNK

Hi Mikael XMLsson

Mikael Eriksson

<Hello>

5

JNK

perfect response

Mikael Eriksson

sorry forgot </Hello>

JNK

Well <Hello />

Zane

15:18

And that's why XML sucks.

JNK

hey man its super flexible

Thats why we need XML pros

things being hard is job security

Zane

Yeah it gives me tons of ways to fuck it up :)

JNK

Me too

case sensitivity killed me for like a year

I kept having stuff come back NULL because I would capitalize something in an element name

Zane

We had all sorts of problems like that at Wand. We were using Telerek reporting for some reason and it would hand back parameters as XML strings. Which sucked the big one.

JNK

yeah we use that for a couple of things

Zane

15:21

I was never impressed with it to be honest.

It was even less flexible than reporting services.

Lamak

Isn't SSRS mostly XML anyway?

Zane

Probably in the background. SSIS spits out essentially a gigantic XML file as well.

billinkc

Is it a MS technology? Yes. Then there's XML involved

Zane

I actually had to fix a package by editing XML once...once.

Mikael Eriksson

@JNK Does not bother me much. I'm enough CDO to always get it right.

swasheck

15:31

rewrite SSIS in JSON

Zane

@swasheck Lol

billinkc

That's how the Biml engine works. It consumes the raw XML and emits their own. No MS libraries involved

Michael J Swart

@billinkc Hey Bill, I've managed an answer. Thanks for your help

0

A: How do I Continue After a Failure in SSIS

So Jamie Thomson's Verify A Connection Before Using It was helpful here. I replaced the Test Connection task with a script task. The script task was almost exactly like Jamie's script except that I Only used one connection manager, not all of them. the task result was always success I set a n...

Zane

@billinkc you and your fancy shmancy BIML.

bluefeet

@Zane he is the BIML king

billinkc

15:41

I would not want to make SSIS packages in any quantity without using it

Lamak

@MichaelJSwart did he even help?

;-)

Zane

@billinkc I'd love to give it a whirl but I think I do quite well with Vanilla.

Lamak

I'm still waiting for his answer

Zane

I'd do one for the non script task method if I wasn't untangling a web of despair.

Michael J Swart

@Lamak He helped by agreeing with you. "Lamak is correct on all accounts." You can't object to that :-)

Lamak

15:47

ah, yes, he's helpful like that :-)

billinkc

You can search chat history, that is not a phrase often associated with Lexmark

Zane

Ugh! Why is every source control aweful.

swasheck

BAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAH

scontent-b.xx.fbcdn.net/hphotos-xfp1/v/t1.0-9/s720x720/…

Zane

Goofball.

Zane

16:33

1

Q: How to automatically grant permissions to application created databases in SQL Server 2008 R2?

So I have an application that automatically creates a new database after the database it is currently in reaches a fixed number of rows. I need to be able to automatically assign a user within the installation the db_datareader role for the automatically created database. I'm kind of at a loss he...

sql sql-server windows

Wierd.

Aaron Bertrand

If you creating databases grammatically why not add GRANT ACCESS to those scripts? — Vladimir Oselsky Jan 21 at 16:42

swasheck

anyone in here particularly learned when it comes to SQLIO?

Shawn Melton

Belkin sure did just screw themselves.

Aaron Bertrand

What'd they do?

Shawn Melton

@swasheck I can tool around with it but Brent Ozar's video is on speed dial for me

Aaron Bertrand

16:44

sigh

What? Some down voted my post? There needs to be a requirement to add a comment to filter out the haters and snipers. — Andy Evans 50 mins ago

Shawn Melton

I am not sure what they did or how. Charter is supporting that they pushed a firmware but mine does not show it updated. This caused something internal to one particular model (lucky me) and everyone has the issue no matter what ISP they are on.

You can see most of the comments on hastag twitter.com/hashtag/belkin

Aaron Bertrand

Oh I thought you meant some company decision (like bought or sold some company or division)

No Belkin gear in this house, so I'm a-ok. :-)

Shawn Melton

I've had no really issue with it until now. I was not aware they could push out something to this magnitude that their product does not even show it received an update.

I have workaround until I can find money to buy another one I guess.

Aaron Bertrand

@ShawnMelton wow, that looks pretty bad, yup

Shawn Melton

I heard Belkin has turned off their support line and say their online chat system is now offline as well....good move yall

swasheck

16:50

@ShawnMelton yeah. i need a bit more than that. i'm getting

> init_thread: VirtualAlloc (0x04000000 bytes for I/O Buffer): Not enough storage
is available to process this command.

with sqlio -kW -t64 -s38 -dE -o128 -frandom -b512 -BH -LS \DB52\testfile.dat

on a 50MB test file

i guess i dont understand how sqlio works

because it seems like it's calculating something that exceeds my storage. it's related the the -t switch

Shawn Melton

@swasheck What version of SQLIO are you using?

swasheck

1.5.SG on WinSrv 2012 R2

Shawn Melton

Guess you probably came across this thread already social.msdn.microsoft.com/Forums/en-US/…

swasheck

yeah.

Shawn Melton

I'd throw up the question on sqlhelp good many folks use it

swasheck

16:54

i posted on twitter to #sqlhelp but decided to delete it because it didnt seem like a well-thought-out question

haha. you read my mind

billinkc

I don't care about unicorn points. I don't.

I will let it go. It does not bother me.

Right

Thank you voter but IT WAS IN THE FREAKING ANSWER. Yes, a little buried which has been corrected but grumblegrumblegrumble

Lamak

And this kind of behavior is as bad as random downvoting

I wouldn't worry about it, people downvote all the time for no apparent reason. You can have a +1 from me to make up for it! — DavidG 1 hour ago

Zane

@AndyEvans people can downvote for whatever reason they want. Maybe they don't like the color of the shirt on your gravatar. Don't worry about it man. — Zane 13 secs ago

Lamak

I mean, the question is well written and I could have upvoted it, but to net a downvote is the wrong reason

Zane

@AaronBertrand Linksys or did you get the Air?

Aaron Bertrand

17:03

@Zane I have airport - extreme at the router and multiple expresses acting as repeaters throughout the house. Weakest spot gets about 50Mbps

Also have a NetGear but that's a separate network, used for home security system

Andriy M

17:15

@Lamak I don't understand that behaviour either but well, people can both downvote and upvote for whatever reason they like, can't they. Including "compensation" voting.

Aaron Bertrand

Not going to happen, because the snipers and haters have earned the right to keep their voting patterns (and reasons behind them) private. Also, @DavidG, that is the wrong thing to do IMHO - up-vote to offset a down-vote that may have a (yet unexplained) valid reason. You can vote however you like, but you should not be voting out of pity or spite. — Aaron Bertrand 24 secs ago

@AndriyM yeah that is pretty subjective, but it seems quite wrong to me.

Lamak

@AndriyM yup, absolutely

Aaron Bertrand

"Just write the code to do the thing." This isn't really an answer, it should be a comment. Primarily because the biggest obstacle in this case is going to be creating a #temp table with a dynamic number of columns with dynamic names and data types. You'll need to create the #temp table in dynamic SQL, which means you'll have to also insert and do anything else you're going to do with this #temp table - throughout the entire procedure - inside dynamic SQL. Teaching someone how to change a tire is not just "here's the jack, there's the tire iron, go nuts!" — Aaron Bertrand 6 secs ago

Zane

You didn't think that was a great answer?

:)

Andriy M

@AaronBertrand They seem to use "stored proc" and "function" interchangeably. The first time it was a stored procedure, in the edit they said "function".

bluefeet

17:36

seems weird

0

Q: tsql query with defensive measure to prevent filling up tempdb

I recently wrote a query that surprisingly filled our tempdb. I was simply doing a string comparision on several thousand records, and I imagine because the strings where so long that the transactional data in the tempdb filled. I would like to prevent this in the future by adding logic to my qu...

tsql tempdb

Aaron Bertrand

Do you want to think about maybe being sure that this is actually what caused the problem before you write code that supposedly cures the "problem" you're not even sure is the problem? — Aaron Bertrand 22 secs ago

Andriy M

17:50

@AaronBertrand Would it be possible to add such a check anyway? SQL is a declarative language, so it would probably need to be some kind of a query hint. Are there such hints in T-SQL?

billinkc

Hook extended events and kill any query that touches tempdb. Piece of cake

Aaron Bertrand

@AndriyM you could simply wrap the problematic query (I assume he knows the exact query that he believes caused the problem) in an IF conditional that checks the status of tempdb size/space before running. This would simply prevent the query from running, not cancel the query after it started. I don't think you could put anything in the query itself that said "uh oh, I'm using too much tempdb, better bail."

JNK

@AaronBertrand unless you did the changes in a loop of X rows at a time and check after each iteration

in which case just do it php or some garbage

Zane

I'm finding all sorts of cool stuff in this abandoned office.

billinkc

Like a body

JNK

17:59

@billinkc it would only be cool if it had been there a while

Zane

Like a typewriter.

And some maracas!

JNK

18:11

TIL ROW_NUMBER() will order your result set implicitly

Zane

Really?

I also found a picture of some old mans head on a stick.

Aaron Bertrand

@JNK you can't rely on that

Zane

This place is awesome.

@AaronBertrand Do tell.

bluefeet

@Zane he will

Zane

I'm excited.

Aaron Bertrand

18:19

It may take me a while to come back to that, but in the meantime just trust me, row_number() can't possibly enforce outer query ordering in all cases.

Zane

Oh I trust you sir. However I'm very interested in seeing how this plays out.

JNK

@AaronBertrand no I realize that

ypercube

Simple proof: put two columns with ROW_NUMBER() and different OVER (ORDER BY)

JNK

I just didn't realize it would order the returned data

DECLARE @t TABLE (id int, col char(1))
INSERT INTO @t
VALUES
(1, 'c')
,(2, 'b')
,(3, 'a')
,(4, 'd')

SELECT id, col, ROW_NUMBER() OVER (ORDER BY col)
FROM @t

Aaron Bertrand

It can but won't always

JNK

18:27

Again, I 100% understand not trusting it without and ORDER BY

it just surprised me, I didn't expect it to do that

I wrote that test code to prove someone else wrong about the ordering

ypercube

JNK you should be ashamed for putting no ; there.

Zane

It's okay chat.stackexchange.com hardly ever runs my queries anyway.

Aaron Bertrand

@JNK you should just tell them that no matter what, it's a box of chocolates, unless you have an explicit order by. What you've told them so far is just promoting that they should be writing queries without it in the first place. :-)

JNK

No no I was advocating explicit order by

and I actually just added explicit order by and checked it in :P

Aaron Bertrand

nice

JNK

18:32

I do a RN() on insert

but there's no order in the select

however the RN() column is the second key in the CI

so it's just HAPPENING to always come back ordered

(first key i the CI is the parentID)

user965347

helo

JNK

...copter?

2

user965347

anyone know how to repair MySQL?

Checking MyISAM file: ./table_data_cache.MYI
Data records: 33978815 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last
repair failed
- check file-size
myisamchk: warning: Size of indexfile is: 3853438976
Should be: 2048
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: Unknown error 126
myisamchk: error: Can't read indexpage from filepos: -1 - check record links
myisamchk: error: Recordlink that points outside datafile at

Zane

Erm. Nope.

Aaron Bertrand

How many people really change primary key columns often enough that all triggers must be written to accommodate it?

I want to be able to handle the situation where someone changes a primary key column — FireLizzard 23 mins ago

JNK

18:34

cd\MySQLDirectory
del *.*

user965347

lol

Aaron Bertrand

@user965347 not here, I'm afraid. I'd suggest posting a question on the site, but those are just spam-bait for some recovery toolbox software that promises to repair/recover databases but can't possibly deliver. Next best option: use a real database.

...you know, or, restore from backup. You have backups, right?

user965347

use real database?

like what?

no backup

JNK

yeah we get a ton of spam posts for MySQL repairs

billinkc

postgres, oracle, sql server

Aaron Bertrand

18:35

No backup? How's your resume looking?

user965347

resume?

3

ypercube

@user965347 Post a question at the site. Or search for similar problems.

user965347

what is that?

Aaron Bertrand

(I wouldn't list this achievement.)

JNK

Resume = job history etc. you send to get a new job

user965347

18:36

no i just want to know

is it recoverable?

Aaron Bertrand

@user965347 a CV, a thing you create to send out to potential employers when you get let go from this job. :-)

ypercube

MYI files are just index files anyway, it should not be a serious problem losing them.

JNK

@user965347 post a question on the site

Zane

@user965347 Doubtful.

user965347

ok

JNK

18:36

Nobody active in here is very familiar with MySQL

If this were SQL Server we could probably help

or at least pretend to help

HOWEVER

Zane

@JNK Without a back-up I'd still say he's boned even with SQL Server.

bluefeet

I'd really like to know what's been happening in here the past week?

user965347

ok i will post

JNK

Let this be a lesson to always have backups

Aaron Bertrand

Well, I'd say we're familiar enough with it to know not to use it

billinkc

18:37

We should remove the mysql/nosql tags from the room

JNK

@bluefeet what do you mean?

user965347

wow its crowd in here at night :)

ypercube

@bluefeet what do you mean?

JNK

Seriously backups are crictical

Zane

It's like 1 in the afternoon for most of us...

Maybe 2

bluefeet

18:38

@JNK and @ypercube we've had strange dropins

JNK

@bluefeet I noticed that too

bluefeet

@Zane @billinkc do you know what I mean?

ypercube

@bluefeet OK, I thought this was what you meant.

JNK

there is a chat link from the supercollider dropdown

ypercube

The Heap must have been linked somewhere.

bluefeet

18:39

that's my guess

the Heap and DBA.SE

JNK

We can start making new users fill out a survey before they chat

Zane

@bluefeet are you referring to people popping in and dropping a code wall and asking for our help?

bluefeet

the # of questions by 1 rep users has increased a lot in the past few days

@Zane yes, yes I am

Zane

In a situation that they are 10000000000% boned.

JNK

> How did you hear about us? (Twitter) (Google) (Other)

Aaron Bertrand

18:40

What kind of questions are you going to ask? () challenging () lame () anything about MySQL

JNK

@AaronBertrand B and C are redundant

Zane

I'm staring the "Resume?" comment as it slayed me.

Aaron Bertrand

C is really, really, lame

JNK

@Zane well that's just a language thing

Could be cultural too

Not sure if they call them like CVs elsewhere or something

Zane

@JNK Doesn't make it any less humorous to see.

billinkc

18:41

It's 11 p.m. for India Standard Time

JNK

yeah we have contractors there

I'm unfortunately familiar with what time it is in India

Zane

Our overnight India people are pretty solid. They only know basic stuff but they don't really break anything which is good.

JNK

Our folks are 10 hours ahead of me

so it's almost 1am

Ours are end users for the stuff I maintain

ypercube

Yes, it's 00:13 in Delhi now.

JNK

Could be worse, they could be in New Zealand right?

billinkc

18:44

Shhh shhh Jack's here

JNK

It's like Old Zealand but with an updated paint job and fresh wallpaper

(and cooler people)

ypercube

@MaxVernon "make use" (not "make us"): dba.stackexchange.com/review/suggested-edits/32346

I guess we have to wait for your edit to be approved.

Max Vernon

@ypercube I just corrected it; not sure if that means it will need to be approved even more

Zane

It's settled I'm moving to this office. It's sooo much better here.

Max Vernon

@ypercube good catch, by the way

thanks!

Aaron Bertrand

19:03

Aaron, can you give us some examples of optimal settings to ensure that the idle mode doesnt cause substandard process? Our system is written in stored procedures for every process. So our understanding is that stored procedures have executionplan and they carry throughout. Shouldnt this avoid this problem? If not, can you give us an example of an executionplan that stands alone? — Nick Uresin 42 mins ago

where to start

I think by just logging out

Also this guy, he's been trying to get this working for two days? And didn't think to search for the error message?

0

Q: How to connect to SQL Server 2012 from vb.net

I've looked through the Web for the better part of 2 days trying to find how to connect to SQL Server 2012 that came with VS Express 2013 for Desktop. Based on some things I found on various sites, in my connection string (starting with "") I've tried Server=.\MSSQLEXPRESS;Database=BrightStar...

sql-server vb.net visual-studio-2013

Zane

19:21

Oh noes my Caffeine is wearing off.

billinkc

Find moar maracas

Zane

I think I've found all of the interesting things in this particular graveyard.

Lamak

@bluefeet I've also noticed this

So, googling the Heap, I noticed this link: dba.blogoverflow.com

The memories

And I've also found some SE scrappers that have the content from DBA.SE

swasheck

19:56

@Zane ...

> Stats of the Week No. 5: Minnesota first-half possession results at Green Bay: punt, punt, punt, punt, punt, interception, interception, fumble, half ends.

Zane

Ponder man.

Green Bay know exactly what they were getting. Rush the quarterback constantly until he proves he can make a throw anyway.

bluefeet

20:12

Ok so we have a data issue that I'm trying to figure out how to fix. Can I pick your brains?

swasheck

yes. but not our noses

bluefeet

never

swasheck

never say never

Mike Fal

We're never ever ever getting back our quarterback

swasheck

mind := blown

and you know you like Pascal

Mike Fal

20:13

No. No I don't

swasheck

Blaise is the man, man

bluefeet

We get a flat file of data from one of our vendors. We load via SSIS and then have a process that joins on one of the raw data columns. We have a list of the expected values but just realized that the vendor is altering the expected values - ever so slightly. So we have items that are getting lost. We want to do some fuzzy matching. It looks like the vendor is using a similar pattern so I wrote the following query to "match" the data -- sqlfiddle.com/#!3/170c16/4

the data they send us always has that "21" characters either on the front or end of the string we join on.

We also get rows without that "plan from..." - I'm trying to account for all cases via some matching

I'd prefer not to assume the length is 21 but not sure how else to fuzzy match - ideas?

Lamak

@bluefeet in this cases is that data also in the begining or end of the string?

bluefeet

@Lamak yes, beginning, end or not included

Zane

@bluefeet have you tried the SSIS fuzzy matching?

bluefeet

20:19

@Zane I looked at it quickly yesterday and then wrote this query instead. :)

Lamak

@bluefeet ah, so there could be rows where neither the "plan from" nor the "04/11-04/14" are included?

bluefeet

@Lamak yes

Zane

@bluefeet I don't know a large amount about it but I've heard lots of good things.

bluefeet

@Lamak we could have this -- sqlfiddle.com/#!3/f7433/3

Lamak

@bluefeet can you just join the tables like:

select *
from RawRates c
inner join ActualRates rp
	on c.RawData LIKE '%' + rp.Data + '%'

bluefeet

20:21

@Zane honestly I looked at the output of it and said - forget it :)

Lamak

?

billinkc

Fuzzy components are enterprise only features

Lamak

@bluefeet or is it too broad?

billinkc

Not sure of your environment but lest that pooch your approach

bluefeet

@Lamak looking but probably. I think I was overthinking it. I'm checking it against our process to see if it will work

billinkc

20:25

Brain too fried, are you matching "Plan from " text or the blah blah blah?

Nevermind, results says blah blah blah. So, is it that you always want blah to match blah or do you need to make blah = bloh

bluefeet

@billinkc blah...

billinkc

blah to blah? If so, then you won't want the fuzzy matches

I'd strip the Plan From text from your data as you import it. Store it separate if you need.

Lamak

@bluefeet hopefully it does works

billinkc

Could probably fake the cleansing with a computed column but that string matching logic in tsql would not be something I want to maintain

A script component would make short work of the Plan from pattern with the .NET string library, possibly a regex

bluefeet

@billinkc you are speaking jibberish

:)

billinkc

20:30

Pretty standard really

bluefeet

for someone who knows SSIS as well as you do

billinkc

msdn.microsoft.com/en-us/library/…

Lamak

bluefeet

I'll look at the regex via ssis and the fun query that @Lamak gave me. It might not even be my story in the next sprint I just was messing around

swasheck

GORRAM!!!!!!!!!! UNFSCK MY SQLIO!!!!!!!!!!!!!!!!!!!!!! HAY-ZEUS!

billinkc

20:35

Some people, when confronted with a problem, think “I know, I'll use regular expressions.”
Now they have two problems.

regex.info/blog/2006-09-15/247

bluefeet

anyone care to help this user?

0

Q: Where Case When ....Then

I have 2 fields I want to send values to within a WHERE statement. If a variable = 0 then set 2 field values to 100. If the variable = 1 then set those same 2 field values to 101. In my imaginary world, somehow this would worK: Where CASE WHEN @ReportType = 0 THEN od.StatusCd = 100 AND odm...

sql sql-server tsql case where

billinkc

Is there any value in retaining the Plan From data or do you just want the blah blah blah?

bluefeet

I'm too lazy to write an answer

@billinkc not sure yet. IMO we'd keep it because it came from the vendor so we have unaltered record of, well, the record

billinkc

kk

Transcript for

The Heap™ – Consultancy ©®