The Heap™ – Consultancy ©®

Aaron Bertrand

01:22

Every time I check in on SO I'm happily reminded why I have given up on it

0

Q: How do you add DBCC CHECKIDENT of any table in an sql SELECT statement?

Hi I want to write an SQL SELECT statement that shows the row count of every user tables in a schema together with their current index. The DB is SQL server 2008 R2. I can write the row count without any problem but I dont know how to add DBCC CHECKIDENT in the result. Please help, thank you!

Aaron Bertrand

01:54

And where is our "close as RTFM" button anyway?

-2

Q: Tallying Results in SQL

i am trying to tally results for a table, the results have replicated product numbers that i need tallied. here is an example of the output: 1203202 1203207 1203205 1203226 1203226 1203226 1203193 1203218 1203213 1203213 1203161 1203161 1203217 1203217 1203217 1203217 1203237 1203206 1203212 120...

sql sql-server

Simon Righarts

04:54

@Aaron, another one of your favorites:

0

Q: A failure in SQL Server backup job

The job was invoked by schedule 16 (WIN7DB01 UserDB Backups.Subplan_1). The last step to run was step 1 (Subplan_1). I could not understand what is the cause of this. Please help me.

sql-server-2008 backup jobs

Wait where did I put my voodoo mind-reading hat.

RichardTheKiwi

05:42

@AaronBertrand Well, you do seem to make a point of "checking in" often! :) We do like your presence here. No offence on that question, I was trying for something light after that comment from the OP.

@ypercube Well, if the query were sane the OP might have an argument for trying to game the optimizer in the right direction. It's not. Side note - I have been known to hint the optimizer lots when above 20 tables and multiple nesting levels are involved.

dezso

07:00

Are questions like this welcome here?

0

Q: Recommendations for PostgreSQL training

I'm looking for recommendations for PostgreSQL training (on-site, off-site, remote) that covers administration, performance optimization, and clustering solutions like Slony and pgpool. Finding courses via web searches hasn't gotten me very far as it seems many of the linked pages are out of dat...

postgresql

Jack Douglas

07:53

-1

A: mdf,ldf file attach problem?

Your problem is with the permissions in your folder or files (mdf/ldf)!

I think this would be better down-voted than removed if anyone is feeling trigger-happy

also, thanks to everyone who is flagging/editing, please keep up the good work :)

-2

Q: Can the transaction log in SQL Server 2008 Express edition be viewed in text?

Is it possible to read the transaction log in text/table format, and if so, how can I do that?

transaction-log sql-server-2008-express

I found the answer interesting here - anyone fancy doing a better edit to get the question into shape?

dezso

@Jack have you hit the review queue? :)

As a former soldier, I considered myself trigger-happy

Jack Douglas

@dezso thanks you :)

wonder who upvoted ?!

@dezso and I shall be careful to be very polite to you from now on ;)

dezso

@JackDouglas Well, I had to give back my tank when I left the military

So I am quite harmless :)

SQL Kiwi

08:17

@JackDouglas I thought about that at the time, but it's hard to know how to improve it without at least a little more direction from the asker.

@JackDouglas Done. I originally felt it might be better as a comment, but it really adds little value and is just wrong anyway. Dunno. Deciding whether to delete is hard.

Jack Douglas

true enough, he's unregistered though so I'd have no qualms about making stuff up if I knew what to make up

@SQLKiwi It's Remus's comment I'd most like to keep :)

generally though I hesitate to delete answers–they really do little harm once they are downvoted. questions are a different matter of course

Have you come across this:

6

Q: Sometimes, an answer is just plain wrong. What to do?

Why "Begin Transaction" before "Insert Query" locks the entire table? The question above contains outright, no two ways about it, plain old factually incorrect answers. I've commented on one and flagged the post that is misinformed but the moderator response has come back as:...

discussion

SQL Kiwi

@JackDouglas Good points. Reading the discussion now, thanks.

@JackDouglas Ok that's helpful. I'm not sure I had an obvious option to comment or down-vote as part of the review (I forget what type of review it was) but I guess I can always click on the link, then review it as 'seems ok' or whatever.

Jack Douglas

@SQLKiwi ahh, the review queue–glad you are using it. I think it is still in 'beta' so no doubt it's missing a few bits

SQL Kiwi

08:33

@JackDouglas Yes, I just discovered it recently and thought I'd give it a crack.

dezso

10:52

@JackDouglas We all come out from Max's overcoat

dezso

12:12

.. and now I edited your posts for good :)

ConcernedOfTunbridgeWells

13:28

@dezso We can get you another one if you want :)

dezso

great

I would get one if Jack went nasty :)

JNK

13:47

If anyone knows a SQL Server Powershell expert I'd appreciate some help

0

Q: SQL Server 2012 SQLPs Module - Changing current location automatically?

I have a large library of existing powershell scripts, primarily to run some complicated logic and interactions between SQL Server and the file system. The SQL interactions typically are performed through the cmdlet Invoke-SQLCmd. Recently the SSMS version on all of my servers was upgraded to...

powershell sql-server-2012

My IT group upgraded our servers over the weekend

now I have a million dollar a day invoicing workflow that's not working

Jack Douglas

14:06

> since those are deprecated in 2012

if they are deprecated why cant you keep on using them?

JNK

They are part of the 2008r2 install

and that is no longer present

so the .dll that those snapins look for doesn't exist

its broken a lot of stuff and I'm kind of pissed about it

I spent ALL DAY yesterday trying to fix these things

then they told me "Oh by the way this really high priority thing you wrote two years ago and haven't touched since is now responsible for all this revenue and needs to be fixed yesterday"

Jack Douglas

ouch

JNK

yeah, I had forgotten about it

it just sort of worked

Jack Douglas

iirc @MarkStorey-Smith knows his powershell. Not sure who else among the regulars–except @jcolebrand of course.

JNK

I thought MSS did but wasn't sure

swasheck

14:14

@JNK assuming everything is physical, right?

JNK

@swasheck I'm not sure what this means

swasheck

@JNK no way to restore a snapshot (if it was virtual)

@JNK sqlblogcasts.com/blogs/martinbell/archive/2011/12/08/…

?

JNK

oh got you

It's all virtual but they are shared term servers

so its complicated to restart them

I'm actually using something similar to that last script to load the module

swasheck

at some point in time you should probably escalate this to incident management --- get your IT team involved to implement a known workaround and then regroup from there

did they completely uninstall everything and then install 2012?

JNK

I think so

IT is involved

they are building me a new server to test stuff on now

and that workaround in your link won't work since I don't have those DLLs in my install

part of my complication is we have a lot of security here

we deal with healthcare data so there are layers and layers and layers

swasheck

14:22

@JNK just realized that after reading through your thread

JNK

and this particular application runs against Medicare data which has its own infrastructure

swasheck

mmm ... healthcare ... yeehaw.

JNK

we have separate servers, switches, physical cabling, etc for this network

just to log in I need to use a special laptop, with my special key card and PIN, then a special login, then another special login once I am logged into the termianl

and use the keycard and pin and password for every step thereafter

its a bitch

this is what happens when congress writes IT security policies

swasheck

so you're saying that to spin up this server for you has a substantial LOE

JNK

yes

swasheck

14:25

@JNK in fairness, sometimes the gov't tries to get things right ... but they get waylaid by money and lobbyists and things end up getting jacked up

once everything's back up and running you should take a day off :)

JNK

its not their fault, but Congress should write IT security policies like I need to be writing tax law

3

I still send CDs for them for petes sake

"No we can't receive data securely over the network, just burn a CD and encrypt the zip file "

swasheck

nice

JNK

its OK, I didn't mean to vent in here. If it was easy then they wouldn't need us and I wouldn't have a job.

swasheck

vent away

JNK

i did

SQLkiwi

14:32

0

A: SQL Server 2012 SQLPs Module - Changing current location automatically?

I've solved this problem by adding a cd as the first line of the script to break out of the provider. Feels hackish though.

I hope it is that simple :)

I also wish I knew the first thing about PS so I could help in some way.

JNK

Well the weird thing is when I load the module the location doens't change right away

and I tried running some scripts and that doesn't change it either

but somewhere in my 1000 line script it gets changed

...and it didn't work :(

SQLkiwi

The line-by-line test looms large :(

JNK

holy cow I think I got it though

swasheck

@JNK it could also be argued that congress should also probably not be writing tax law

SQLkiwi

@JNK Too early for a 'woo hoo'?

Jack Douglas

14:40

I put a Set-Location into my main procedure and it seems to have fixed it! — JNK 2 mins ago

SQLkiwi

I put a Set-Location into my main procedure and it seems to have fixed it! — JNK 3 mins ago

swasheck

@JackDouglas @SQLkiwi I feel like Felix just touched down ... again.

Jack Douglas

@swasheck I missed all that excitement–must be living in a bubble

JNK

@SQLkiwi no I think its good

its processing now

SQLkiwi

@swasheck :c)

JNK

14:41

so WOO HOO

swasheck

@JackDouglas i watched the replay

SQLkiwi

+1 vicarious WOO HOO

Jack Douglas

So the hospitals can start treating people again?

SQLkiwi

I do love a happy ending. I may cry a little.

swasheck

@JackDouglas apparently just the medicare patients

JNK

14:43

i am right now

they are getting treated

but we need to get PAID

we audit Medicare

to make sure they pay the right amount

swasheck

@JNK jack and orange juice?

swasheck

14:56

@JNK ... to make things a bit better

chroniclesofgeorge.com/tickets2.htm

SQLkiwi

Had my first auto-conversion of comment discussion to chat today. Very pleased with that milestone. Now to figure out the exact trigger...

Jack Douglas

@SQLkiwi I always thought it was just when the number of comments hit 10 or whatever–but I haven't had it in ages

JNK

I think it's rate triggered

X comments between 2 users within X minutes

SQLkiwi

@JNK That might be it. There were 9 comments in total (including the invite to move to chat) but 2 comments each for 2 users inside a couple of minutes or so.

ConcernedOfTunbridgeWells

15:28

@dezso If you've ever met @JackDouglas you'd understand that's a fundamentally silly concept. :)

Jack Douglas

@ConcernedOfTunbridgeWells why thank you kind sir

jcolebrand

15:47

I'm going to be very AFK this next week or two

4

SQLkiwi

@JNK Interesting late feedback on your PoSh issue from Chuck:

@SQL_Kiwi I'd have a look at my profile http://bit.ly/TZ3lhD I'd also see if there are any reg checks, then check my reg #PoSHhelp #sqlhelp

Tweeted by chuckboycejr on October 16, 2012 at 3:52 PM in reply to SQL_Kiwi

I said I would pass it on, might be worth looking into if you want a full solution.

And also:

@SQL_Kiwi FWIW, I wouldn't use the sqlps mini-shell. consistent behavior's easier to maintain from plain vanila PoSH IMO #PoSHhelp #sqlhelp

Tweeted by chuckboycejr on October 16, 2012 at 3:56 PM in reply to SQL_Kiwi

@SQL_Kiwi sure thing. hope it can help in someway. Have you looked at #SQLPSX btw? http://bit.ly/bknP0l #sqlhelp #PoSHhelp

Tweeted by chuckboycejr on October 16, 2012 at 4:00 PM in reply to SQL_Kiwi

Last one, I promise :)

dezso

16:19

@ConcernedOfTunbridgeWells That would spare me a quid or two

ConcernedOfTunbridgeWells

@dezso I still think we should get you a tank anyway :)

ypercube

16:31

interesting question

6

Q: In SQL Server, do composite primary keys increase the chance of a deadlock?

I was wondering if, either because of increased row locking, and/or because of increased time spent holding locks, having a composite primary key defined on a table would increase the chance of having a deadlock when a it is being updated my multiple threads at once? Thank you for any help.

sql sql-server

Anynone with an iPhone 5? businessinsider.com/ifa-apples-iphone-tracking-in-ios-6-2012-10

SQLkiwi

@ypercube Composite keys are worse for just about everything, so it wouldn't surprise me. Time to read the question...

Lamak

@SQLkiwi really?, so categorically? (I am actually asking, not giving an opinion)

SQLkiwi

@Lamak For me, and from a performance perspective, yes. There are all sorts of important optimizations (in SQL Server at least) that work best (or at all) with single-column keys, integers for preference, and NOT NULL ones at that :)

So from the point of view of the question, a composite key of the same width is probably no worse from a hash-collision point of view, but slower performance means longer locks so perhaps there is a chance there. Hard to know for sure.

JNK

16:48

@SQLkiwi I appreciate that! I have a lot of entry-level scripters in my group so I like to use the cmdlets instead of managing SQL connections via .NET which is a lot more complicated

SQLkiwi

@JNK Oh I'm glad. He seemed particularly impressed with the SQLPSX project by the way. If you need to do more PoSh stuff in future it might be worth looking at. As I said before, PoSh is a complete mystery to me, but I recognise genuine enthusiasm when I see it :)

JNK

@SQLkiwi PoSh is pretty great. It's been useful in my environment because our security has SQL and file system so segregated, like I can't use xp_cmdshell at all

SQLkiwi

@JNK I think I've just been lucky - on the odd occasion I've needed anything like that I have been allowed to use SQLCLR.

JNK

CLR is also verbotten here

swasheck

17:32

@JNK dont let the man get you down

JNK

we're all the man

I'll quote Tool here:

I've got some advice for you little buddy/before you point your finger you should know that I'm the man/If I'm the <redacted> man then you're the man as well/so you can point that finger <inappropriate>

swasheck

@JNK SWEET!!!

i was more quoting Empire Records ... but meh

JNK

we have the rules for a reason so I'm not complaining about those

swasheck

i get it

dezso

18:45

How can one get a half star on a message here?

SQLkiwi

@dezso That looks like something different - perhaps a 'sticky' star?

dezso

meaning what?

DTest

can you star yourself? maybe a moderator ability

JNK

its a mod star

called a pin

it stays up at the top

DTest

that's fancy

JNK

18:54

the user stars will get cycled out based on age and other stuff getting starred

its handy though to put like a MOD up

Just like moderators, that star is empty inside

6

swasheck

how depressing

DTest

hah

dezso

Oracle folks, is my idea about a table function a wrong one? dba.stackexchange.com/questions/27045/…

dezso

19:23

How to shoot yourself in the foot with an ORM?

1

Q: How to troubleshoot orm queries interfering with other applications

Well, it's hypothetical scenario but what I'm trying to understand is the path to go from a post-morten log (say, an SQL Server Profiler trace) to identify the code on a ORM situation. To make it not too vague, consider an scenario like that: SQL Server 2008 Entity Framework as a ORM So, in t...

sql-server-2008 entity-framework orm troubleshooting

ypercube

I'll repeat myself: "Woohoo, I'm a worm. I'm worse than a worm. I'm an orm"

2

mistrfu

21:14

Good afternoon gentleman I have a question most of you might find trivial.
I am would like to have a table of microscope slides, where each slide should have a number of editable "properties".
Imagine one slide having tumor_type, dye_type, description, date_injected and another slide could have for example patient_name, treated_since, description. I can't store these all in slides table because I would like users to be able to add properties when they wish to. Each property shoul also have a type, since some are integers, some text and some for example date and time.

dezso

@mistrfu Could you please ask a question proper?

I will attract broader attention

mistrfu

As in write a question on SA?

sure

dezso

SA? If you mean DBA.SE, then yes :)

mistrfu

yep, that's what I mean

dezso

OK then :)

mistrfu

21:17

one more thing

I hate posting without searching, could you help me word out a search term for this kind of problem

dezso

Well, the first thing is EAV (Entity-Attribute-Value)

Lamak

@mistrfu EAV modeling?. Entity-Attribute-Value

Entity–attribute–value model

Entity–attribute–value model (EAV) is a data model to describe entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. In mathematics, this model is known as a sparse matrix. EAV is also known as object–attribute–value model, vertical database model and open schema. Structure of an EAV table This data representation is analogous to space-efficient methods of storing a sparse matrix, where only non-empty values are stored. In an EAV data model, eac...

mistrfu

will do thanks!

dezso

it is basically a solve-all-problems thing, but easily can turn into a nightmare

But post your question, I'm sure you will get good advice

Aaron Bertrand

I blogged about EAV, but my opinion is hardly the popular one.

sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/…

mistrfu

21:21

Alright, will do. Thanks for the pattern name guys. I wish there was some sort of dictionary for us devs.

dezso

@AaronBertrand Would have you chosen EAV if you had had only salons as costumers?

However, there are certain cases when you cannot do anything else

Aaron Bertrand

@dezso possibly, yes. Since they may still have had different requirements for attributes they wanted to track.

It helped that each customer was in their own database, which kept bad attribute planning from affecting other customers, and made it even more complex to consider schema variations based on individual customer requirements.

dezso

Do you have a sort of set treshold on when to change to EAV from many columns?

Aaron Bertrand

@dezso I don't think it's a number. It's a "how likely is this set of attributes to change."

@dezso having to change the schema even once or twice to accommodate moving target requirements is an absolute pain in the ass. The downsides to EAV were easy enough to work around.

dezso

@AaronBertrand And what's your opinion on turning heavily used attributes into fixed columns? (OK, from the last message I got a hint)

Aaron Bertrand

21:34

@dezso don't see the benefit. I'd rather create a filtered index on that attribute.

I just find EAV so much more flexible, and there are clever ways to deal with the limitations.

mistrfu

posted, now I get to read @AaronBertrand 's blog post (thanks btw)

dezso

@AaronBertrand Which certainly discards MySQL as an option (with view on @mistrfu's question)

Aaron Bertrand

@dezso only relevant if small subset becomes heavily used, and still no guarantee that will cause a performance problem. I'm sure normal indexes, views etc. can help minimize any perf impacts. And I'm not convinced the non-EAV approach won't have its own pitfalls either. This is why I prefer a proper, mature, commercial RDBMS. They've built features to compensate for bad but necessary design.

dezso

@mistrfu there you will find a very good answer, backed by first-hand experience

Max Vernon

22:04

@AaronBertrand "get them to invest in some new pyramid scheme." SWEET!

mistrfu

22:15

@AaronBertrand nice article, make's me happy how close it is to what noob like me came up with.

Aaron Bertrand

@mistrfu glad it helps. It doesn't mean EAV is magic, though. There are some valid concerns with the approach.

swasheck

yay php

php.net/server-status

Zane

22:31

Anybody besides me in here use Integration Services?

Never mind I got it figured out

Transcript for

The Heap™ – Consultancy ©®