The Heap™ – Consultancy ©®

swasheck

4:44 AM

@MikeFal hellooo, my eyes are up here

Andriy M

9:03 AM

Could this

0

Q: Oracle SQL Summing Values From Two Different Tables

Hey all so I am trying to add to (or subtract from) a column of different values based on a separate column in another table. Here is a example of what I am tying to do: Table 1: (Current Inventory) ItemName | Quantity ----------|---------- Baseball | 5 Football | 10 Jersey ...

be closed as a duplicate of this

11

Q: How to update a table from a another table

I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. So how to implement that ? BTW I am using oracle Thanks

oracle sql

bluefeet

12:21 PM

@MikeFal awww, shucks, thanks! I can also validate that Mike is awesome, and a great presenter!! But y'all already knew that, I'm just late to the party

However, I was wearing shoes so he couldn't really validate the blueness of my feet.

Aaron Bertrand

12:45 PM

Could someone try this code and tell me in what situations it "doesn't work"?

DECLARE @percentage DECIMAL(5,2), @min_row_count INT;
SELECT @percentage = 10, @min_row_count = 1000;

;WITH x([table], [fwd_%]) AS
(
SELECT s.name + N'.' + t.name, CONVERT(DECIMAL(5,2), 100 * CONVERT(DECIMAL(18,2),
SUM(ps.forwarded_record_count)) / NULLIF(SUM(ps.record_count),0))
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),
t.[object_id], i.index_id, NULL, N'DETAILED') AS ps

I'm guessing 80 compatibility mode.

JNK

12:59 PM

mornin all

@AaronBertrand won't parse for me

oh nvm

compiles now but I get

Msg 413, Level 16, State 1, Line 4
Correlated parameters or sub-queries are not supported by the inline function "sys.dm_db_index_physical_stats".

ConcernedOfTunbridgeWells

1:27 PM

@MikeFal Oh noes .. First we find that @gbn doesn't actually have a mullet. Now @Bluefeet's feet aren't actually blue ...

@TimStone Obviously @MikeFal's an extrovert.

Q: How do you tell an extrovert computer scientist?

A: When he talks to you, he looks at your feet.

Aaron Bertrand

@jnk huh. What version?

Mark Sinkinson

@AaronBertrand Works fine (although returns nothing) for me on Microsoft SQL Server 2012 - 11.0.5556.0 (X64)

Lamak

@AaronBertrand got the same message as @JNK

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

JNK

@AaronBertrand 2008 r2

Phil

1:43 PM

I'm voting to close this question as off-topic because editing a plain text file is too simple for this site — Phil 13 secs ago

Too harsh?

JNK

are they really asking how to edit a text file/

Phil

Maybe I should answer it with a perl script :D

gbn

@ConcernedOfTunbridgeWells I may grow one as I get (even) older

Phil

Like "Open the file with a text editor -> Make changes -> Save file (or Save as) -> Send the new version to your friend"? — ypercube 5 mins ago

@ypercube Thank you so much. That solved my problem. — dj1 1 min ago

What the actual F

ConcernedOfTunbridgeWells

@gbn Eagerly waiting in anticipation ...

ypercube

1:55 PM

@Phil Lets just close the question. He was probably doing something silly, not saving or emailing wrong file.

Phil

He's deleted it

Lamak

@Phil that's hilarious

gbn

2:10 PM

@ConcernedOfTunbridgeWells The missus is under strict instructions that I should be euthanased promptly in case of 1. Moustache 2. Beard 3. Mullet

The beard because I'd look way too much like my father

ConcernedOfTunbridgeWells

@gbn Oh dear. So, no mullet then ...

JNK

2:29 PM

I'm not sure if he has one or not. It's a NULLet.

Mike Fal

@ConcernedOfTunbridgeWells It's true!

billinkc

@Lamak 2008 R2 (RTM)? Bro, do you even patch?

JNK

we JUST installed SP1 in production a week ago

(for 2008r2)

Lamak

@billinkc yeah, I know, I'm not only not in charge of that, but I've already informed the people responsible for it, so....

billinkc

Hermano, incluso paraches?

Is that at all accurate?

VtC opinion/broad/not programming related/shopping list: your choice

0

Q: What are benefits of Project Deployment model in SSIS?

My project is not a core DW project. We use SSIS packages to export\import files and related operations e.g. zip and FTP the file\encrypt and email the files etc. All packages are not related to each other, hence we store all packages on msdb database and execute those using custom application or...

ssis ssis-2012

Lamak

2:42 PM

@billinkc not at all

Mike Fal

Patch at me, bro

JNK

Oh god I'm sick of shovelling

and about to do it again

Kris Gruttemeyer

@JNK It's that time of year. At the end of winter it's always that much more of a pain to get out there.

Because you know it will all come to an end soon

JNK

yeah very soon I hope

Mike Fal

It will end. Only to start again later this year.

JNK

2:45 PM

This last month was the coldest on record for CT too

which doesn't make it more fun

billinkc

More fodder for closing

0

Q: Centralized SMTP client

I have a requirement of sending mail using SMTP. But the to Mail ID's will be retrieved from database, based on the given category(success/error).Then only I am able to send the mail. The above requirement needs to be implemented in the following application types Winforms SSIS packages asp.ne...

c# asp.net winforms ssis smtpclient

Mike Fal

@JNK When I was down in Phoenix, it was mid 70s. I was very sad to come back to sub freezing temps in Denver.

Kris Gruttemeyer

@JNK Don't I know it. February was the coldest month on record, EVER, for Syracuse. Average high temp was like 8.5 degrees. I'm so happy to see double digits and temps above freezing now.

Double edged sword, though. Those 4 feet of snow now have to melt. Welcome to the muddy season

JNK

Avg here last month was 16.1

ypercube

Close as dupe. The OP agrees: dba.stackexchange.com/questions/94180/…

@MikeFal That's 40 years ago? ... ;)

Mike Fal

2:49 PM

@ypercube Yup, that's a classic.

bluefeet

@MikeFal It was nice this weekend, except for the rain

ypercube

@MikeFal I really thought you meant that. Until I read the rest of the sentence (and other comments).

Mike Fal

@bluefeet Yeah, even the rain wasn't that bad. Friday was gorgeous, should have gone hiking or something.

@ypercube That might have bee a subconscious inclusion. Considering the age of the joke, it's probably embedded in our collective geek psyche.

Aaron Bertrand

When JavaScript countdowns go wrong

ypercube

@MikeFal or perhaps, I'm used to Celsius.

billinkc

3:05 PM

how was Greece?

bluefeet

BTW @MikeFal your session was the best one I attended on Saturday! :)

ypercube

@billinkc Nice. A bit rainy on Monday, sunny the rest.

Mike Fal

4:00 PM

@bluefeet Thanks very much. Glad you enjoyed it.

ypercube

4:13 PM

> Also tell me why there is no incremental back up in SQL-Server. But DB2 has.

...

-4

Q: Incremental BackUp in sql server

I got a task for backing up my clients data through internet. Client has about 50 GB of data there. So I need to back up the data and pass through internet, but it takes about 8 to 9 hours for this process because our internet connection is slow. So I can't resolve client issues in time. So I d...

sql-server-2008-r2 backup database-administration

billinkc

Because... aliens

ypercube

Not sure why this q went to -4. I guess the bold did not help.

Phil

4:38 PM

Why do security audits always generate such dipshit requirements? I've had to deal with a few heads of IT security for large firms before, and they just don't get it. Been so many crappy questions on here as a result recently. Auditing BLOBs was just LOL

Mike Fal

4:51 PM

@Phil Are your BLOBs encrypted so that people can't accidentally read them from within the database? ;)

Phil

@MikeFal aaaarrrggghhhhhhhhhhhh

Zane

0

Q: Caching results in SQL Server

I have a stored procedure with 20-30 optional parameters. There are inline TVF's corresponding to these parameters which are called when parameters passed to the stored procedure. There is a common dbo.Match function called by these TVF's that looks up for String Matches in Table A and does fair...

sql-server caching stored-procedures

swasheck

5:07 PM

every joke can have its truth, but now the joke's on you

bluefeet

6:03 PM

crickets

Kris Gruttemeyer

Yeah, quiet today

billinkc

Post sql saturday malaise

Zane

I've been trying to get my cheap crappy KVM to work.

Mike Fal

@billinkc Yeah, having withdrawals

Zane

Good lord. I know I'm the go to guy for fixing shit here but I've told this woman a solid 5 times now that I don't have access to the source system and you will have to talk to a different guy.

I've even told her the name of the contact....

billinkc

6:10 PM

If you were really a team player, you'd contact that other person and then work through all the issues that come. Sheesh

Zane

Well I'm not so I'm going to go eat breakfast food.

mmarie

@MikeFal I don't have anything to PowerShell so i can't do SQLJudo's challenge. Can I BIML something instead?

Mike Fal

@mmarie Heh, so that's actually his challenge from LAST year. Ken's doing them on his own and blogging about them.

mmarie

oh

then I will just BIML something anyway

Mike Fal

You should. Maybe Powershell something to BIML something? :)

mmarie

6:13 PM

i need to BIMLScript first

but i need to get good at BIML before automating it

Mike Fal

BIML all the things?

mmarie

yes, once i figure out how

Aaron Bertrand

6:41 PM

!

@AaronBertrand It took two full days to install SQL Server Management studio 2014 express (after failing to install 2008, 2012). And it solved the problem. — LifeH2O 13 mins ago

billinkc

They were inputting the bits one at a time

swasheck

7:18 PM

mashable.com/2015/03/01/canadians-spock-bills

Aaron Bertrand

^^ awesome

swasheck

sorry. i can't believe i did that

Aaron Bertrand

Feb 27 at 16:39, by Paul White

You're mistaking me for someone who cares enough.

:-)

bluefeet

I'm so over replication

swasheck

no replication sqlsat presos?

bluefeet

7:25 PM

nope

Aaron Bertrand

I could have quoted a similar statement from me about 15 years ago, but the Heap didn't exist then

bluefeet

we have to make our scripts re-runnable - so if exists around everything. trying to figure out if a subscription exists on the subscriber is a pain in the arse

swasheck

replication just absolutely sucks

until you get it up and running. then you can't touch it

billinkc

My, my, my MUSIC! Hits me so hard

makes me say, OH MY LAWD!

Mike Fal

Thank you, for blessing me with a mind to rhyme and two hype feet.

bluefeet

7:39 PM

BTW, I would slap the person who design this table

Mike Fal

Dear sir. You fail at third normal form.

swasheck

8:13 PM

@MikeFal my god eventbrite sucks balls

bluefeet

8:46 PM

wow, just wow

0

Q: Using case in SQL server

Im trying to use CASE statement in SQL Server. Im trying to update the ProfitUSD column where in the values fetched from the Profit column is multiplied by the conversion rate from the Rate column to get the Profit in USD(ProfitUSD column). Basically , Im just trying to convert the profit into US...

sql sql-server case server sql-server-2014

Lamak

yup

swasheck

more fun with normalization

billinkc

They hand typed all of that

bluefeet

~~idiot~~ some other word

2

Phil

1

Q: SQL error: Unknown column in 'where clause'

I have following very simple PHP MySQL Query: $sql = 'SELECT * FROM bundesland WHERE bkuerzel = ' . $_GET['q']; $result = mysql_query($sql, $con); When I execute the query with a click on a item with the value "Wien" on a dropdown menu I get "SQL error:Unknown column 'Wien' in 'where clause' i...

mysql php

billinkc

8:49 PM

To quote the Penguins of Madagascar: Sweet chimichanga

@Phil VTC MySQL & PHP

Phil

Should we remove SQL injection questions that aren't sorted?

billinkc

Unless you have a means of removing the users

Phil

I have one question for you: WHY? — Phil 17 secs ago

I must be grumpy today

JNK

Boss today brought up moving to Postgres because of SQL Server licensing costs

I'm considering writing up a breakdown of how long it will take us to refactor all our existing code

Kris Gruttemeyer

@JNK Yes, but what's the cost of shooting yourself in the foot?

JNK

8:59 PM

@KrisGruttemeyer they make a fair point which is SQL Server licensing is pretty high for our little company

and it's blocking us from upgrading some things

Kris Gruttemeyer

Absolutely, but refactoring all that code will probably take some time, like you said

billinkc

@JNK Just imagine what shenanigans The DBA (tm) will pull if you moved to PG

JNK

@billinkc This is part of a like 5 year plan potentially

Lamak

@KrisGruttemeyer and also training the current dbas

JNK

not gonna happen any time soon

and it's more of a "we need to talk about this so we can have a good reason not to" I believe

Like he's being asked to look into alternatives

Apparently SOMEONE has told him that a lot of the stuff we do just has to be manual if we don't go 2012 enterprise

And I told him I think almost everything that HAS to be manual can be powershelled

like The DBA™ likes using the gui to set up log shipping but I don't think that's the only way to do it...

Mike Fal

9:25 PM

@JNK God no. You can use the baked in stored procedures to do most of the work. We use the log shipping exe to do our log backups here and I have coded around those stored procedures to automate the job creation.

Aaron Bertrand

@JNK egads, it's so much easier to NOT use the GUI or any of the built-in stuff at all.

Some people love them some GUI, though. Prevents them from having to rub brain cells together.

swasheck

@JNK DROP DBA WITH ROLLBACK IMMEDIATE;

billinkc

9:41 PM

@swasheck Where can I click in the GUI to get that?

swasheck

@@AaronBertrand @MikeFal ... could index rebuilds result in any sort correlation to a drop in the number of pages in the buffer pool?

@billinkc it's a hidden feature

sudo ssms.exe

billinkc

MMA is banned by our webfilter so no judo ssms I'm afraid

JNK

@swasheck when you rebuild the index won't the old index pages in the buffer be invalidated?

swasheck

@JNK i dont know, actually

it's why i'm asking :)

JNK

@MikeFal yeah I know it's possible to do it all in advance

Aaron Bertrand

9:46 PM

@swasheck yes

swasheck

@AaronBertrand thanks. for the reason that @JNK mentioned?

Mike Fal

@AaronBertrand I always tell people that the GUI will eff them in the end.

Aaron Bertrand

@swasheck right - you may see an increase temporarily if you are doing it online, but in either case you should see a drop.

swasheck

awesome. thanks!

Aaron Bertrand

Hmm, hold on. With a heap that doesn't happen.

swasheck

9:52 PM

meaning if you rebuild the heap?

or a NCI

Aaron Bertrand

rebuild heap

swasheck

interesting. i dont think that we have a significant number of heaps (though TK might say that we should we have large fact tables)

billinkc

tk421, why aren't you a heap?

Aaron Bertrand

I don't think they all get invalidated at once. But if you rebuild presumably you will have fewer pages because the data gets reorganized to fit. Will depend on frag / fill factor.

"ShowMeTheBuffers" is based off the second query here: mssqltips.com/sqlservertip/2393/…

swasheck

interesting

thanks

Aaron Bertrand

9:59 PM

There are probably some super double-secret trace flags that will print out all of the page-level activity during the rebuild. But I don't think those details are crucial anyway. The net result won't change. :-)

JNK

I wonder if it's b/c ALTER TABLE REBUILD is blocking

@AaronBertrand What's a trace flag?

Aaron Bertrand

@JNK same happens with ONLINE = ON

swasheck

WHAT DOES THE TRACEFLAG SAY?

Aaron Bertrand

That's the case where I suspect there are more pages temporarily (e.g. if you check the buffer pool from a different session), but still fewer in the end.

Mike Fal

@swasheck dingdingdingdingdingdingding

swasheck

10:02 PM

for the last 4 hours

Mike Fal

@swasheck Wow, I've never had that problem.

Transcript for

The Heap™ – Consultancy ©®