VBA Rubberducking

Duga

00:01

RELOAD!

[banane-io/PDB] 1 commit. 49 additions. 5 deletions.

[bruglesco/memory-card] 2 commits. 22 additions. 14 deletions.

[Cardshifter/Cardshifter] 9 commits. 86 additions. 60 deletions.

[Cardshifter/cardshifter.github.io] 2 issue comments.

[Cardshifter/HTML-Client] 2 commits. 57 additions. 1 deletion.

IvenBach

Home Time

Woot. Just got email from IT that they set up my requested SQL instance. Time to fail at DB!

IvenBach

05:38

@MathieuGuindon Going over examples for Assert.SequenceEquals got me thinking. For when boundaries aren't identical theres an Error 9 Subscript out of range message. A bit less user friendly than the boundary message or dimension message.

Also saw if both boundaries don't match it only shows the lower bound as being different.

Nice touch on having it state which dimension its on. :+2: for that alone.

IvenBach

06:08

Check your email Mug. You have a goose egg waiting for you.

Night pond.

Duga

06:33

[rubberduck-vba/Rubberduck] retailcoder created pull request #4000: Resources to merge retailcoder/Rubberduck/resources into rubberduck-vba/Rubberduck/next

> introduces Rubberduck.Resources project, begins to split up RubberduckUI.resx into namespaces, replaces relative paths with pack URI's in xaml markup.

[rubberduck-vba/Rubberduck] build for commit 6804894a on unknown branch: AppVeyor was unable to build non-mergeable pull request

BUILD FAILURE!

Duga

06:56

[rubberduck-vba/Rubberduck] retailcoder rejected pull request #4000: Resources

[rubberduck-vba/Rubberduck] retailcoder created pull request #4001: Introducing Rubberduck.Resources to merge retailcoder/Rubberduck/next into rubberduck-vba/Rubberduck/next

> RubberduckUI isn't fully cleaned up, but it's a start.

IvenBach

@MathieuGuindon it’s 3am mug... you should be sleeping.

So should I...

Duga

[rubberduck-vba/Rubberduck] build for commit d99129b6 on unknown branch: AppVeyor build failed

BUILD FAILURE!

[rubberduck-vba/Rubberduck] retailcoder synchronized pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] build for commit 6b174bb0 on unknown branch: AppVeyor build failed

BUILD FAILURE!

Duga

07:30

[rubberduck-vba/Rubberduck] retailcoder synchronized pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] build for commit 3d8da3c6 on unknown branch: AppVeyor build failed

BUILD FAILURE!

[rubberduck-vba/Rubberduck] retailcoder synchronized pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] build for commit 78944b48 on unknown branch: AppVeyor build failed

BUILD FAILURE!

[rubberduck-vba/Rubberduck] retailcoder commented on pull request #4001: Introducing Rubberduck.Resources

> The inspection resources are broken AGAIN. This is maddening.

[rubberduck-vba/Rubberduck] build for commit e1dba32a on unknown branch: AppVeyor build failed

BUILD FAILURE!

[rubberduck-vba/Rubberduck] retailcoder synchronized pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] retailcoder added label localization to pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] retailcoder added label technical-debt to pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] retailcoder added label PR-Status: WIP to pull request #4001: Introducing Rubberduck.Resources

[rubberduck-vba/Rubberduck] build for commit 25d9f5d6 on unknown branch: AppVeyor build failed

BUILD FAILURE!

Duga

10:03

[rubberduck-vba/Rubberduck] marcinczerny commented on issue #3986: COM Exception while runing unit tests

> @bclothier
I have attached a file with logs.

@WaynePhillipsEA
There are no options related to VBA/Security as far as I am concerned. I have tried to add the iFix project to Trusted Locations in Office, but that's not working either.
own is supported
[RubberduckLog.txt](https://github.com/rubberduck-vba/Rubberduck/files/1994846/RubberduckLog.txt)

Duga

10:27

[rubberduck-vba/Rubberduck] Vogel612 opened issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

> Stacktrace obtained from #3986:

```
2018-05-07 20:32:32.7893;WARN-2.2.6698.27995;Rubberduck.Inspections.Rubberduck.Inspections.Inspector+<FindIssuesAsync>d__7;System.ArgumentException: Element o tym samym kluczu został już dodany.
w System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
w System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
w Rubberduck.Inspections.Concrete.OnErrorStatementListener.ExitModuleBodyElement(ModuleBodyEl

[rubberduck-vba/Rubberduck] Vogel612 added label bug to issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

[rubberduck-vba/Rubberduck] Vogel612 added label feature-inspections to issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

[rubberduck-vba/Rubberduck] Vogel612 added label difficulty-02-ducky to issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

[rubberduck-vba/Rubberduck] Vogel612 edited issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

Duga

11:09

[rubberduck-vba/Rubberduck] bclothier commented on issue #3986: COM Exception while runing unit tests

> Just to verify - Is your testing doing any automation with any of Office components?

Duga

12:00

[rubberduck-vba/Rubberduck] marcinczerny commented on issue #3986: COM Exception while runing unit tests

> No, I was trying to do some dummy tests like Assert.IsTrue True, etc, just to check the rubberduck.

Duga

12:35

[rubberduck-vba/Rubberduck] tavenger5 starred us

2

Duga

12:47

[rubberduck-vba/Rubberduck] MDoerner commented on issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

> The problem here is that the listener populates two dictionaries with additional information to be passed to the inspection results. These do not get cleared in `ClearContexts`. Consequently, a rerun of the inspection will fail on adding a context the second time.

To be honest, I have not been able to determine the actual use of the two dictionaries. The first one actually does not save any information one could not get from the context via an existing extension method.

M.Doerner

@Duga Maybe ClearContexts should be renamed to ResetListener.

Dain Ironfoot III

13:01

The Lie-to-Children paradigm is pervasive in education. In elementary school you learn that the sky is blue. In high school you learn that the sky appears blue because the atmosphere is blue. At university, you learn that the atmosphere filters out non-blue light to a greater extent than blue light. In grad school, you are led to wonder what the defintion of "blue" was anyway, the historical context of defining the sky as blue, and what the likely social, economic, and political ramifications of a non-blue sky would be. During your PhD, you claim that the sky is mauve and defend your model. — Robert Columbia 15 hours ago

@LightnessRacesinOrbit: Java programs are never frictionless, but in their natural state with mass approaching infinity, they do approach becoming perfectly spherical. — R.. 2 days ago

M.Doerner

13:17

Just for reference, the sky appears to be blue because blue light is scattered much more in the atmosphere than most other visible light.

Inarion

@DainIronfootIII Is teaching a simplified (and thus more understandable) model equivalent to lying, though? (But I did actually enjoy that quote nonetheless.) :)

Dain Ironfoot III

@Inarion I don't think so.

Some people do.

Inarion

Glad you don't. :) (I think it would make for a horrible education always teaching the most advanced/detailed/known-to-be-most-accurate model first. No chance for a kid to gradually learn complex correlations...)

Mathieu Guindon

13:39

I'm so going to regret last night today

Going to retrieve the busted .resx files from an earlier commit and fix the PR tomorrow

this

> If there's something strange in you neighborhood
Who you gonna call? (resourcebusters)
If there's something weird
And it don't look good
Who you gonna call? (resourcebusters)
I ain't afraid of no resource
I ain't afraid of no resource
If you're seeing things running through your head
Who you gonna call? (resourcebusters)
An invisible man
Sleeping in your bed
Who you gonna call? (resourcebusters)
I ain't afraid of no resource
I ain't afraid of no resource
Who you gonna call? (resourcebusters)
If you're all alone

Mathieu Guindon

Man you went out of your way on that one lol

IvenBach

15:34

I got SSMS connected. No clue how to do anything... #WhatsThisButtonDo?

this

you do have your own db, right?

IvenBach

Dev, yep.

I'm not stupid enough to try "learning" on a live in production DB.

this

you need to make sure you stay inside that db -- -SSMS work with instances and thus you can connect to more than just that db

IvenBach

How would you stray outside of the instance?

this

one dumb way would be to script a script from production db then try to run it on dev.

By default script includes USE <database> at the top of the script

and even if you've changed the SSMS dropdown to connect to the dev db, running that script trumps that and you end up making changes to the prod db

For that reason, I change the SSMS options to not include the USE <databse> when scripting and always manually check the database dropdown before I hit F5.

Dain Ironfoot III

15:38

Same here.

IvenBach

Where can I change that setting then?

this

Tools -> Options

should be under Object Designers | Scripting or something like that.

Dain Ironfoot III

Except, it gets #fun when you have N databases with the same schema across 3 environments when the DB names are exactly the same in each environment.

Mathieu Guindon

@this assuming permissions ;-)

this

@DainIronfootIII and that's where SQLCMD scripts come in.

IvenBach

15:39

this

@MathieuGuindon I'd hope the IT is smort enough to avoid giving Iven that much permissions but.... ?!?

@IvenBach use the search, Luke.

"scripting" should get you there

Mathieu Guindon

@IvenBach whatever you do, before you hit F5, pay attention to what this dropdown is saying:

this

^ this.

Mathieu Guindon

also the status bar

this

This also may be helpful: accessexperts.com/blog/2011/08/05/…

IvenBach

15:43

Which bar is that? Only Standard is shown presently.

Mathieu Guindon

that would be SQL Editor

you don't really need any other

IvenBach

@this I hear that guy's really helpful.

this

it's not showing by default? How strange.

Mathieu Guindon

@IvenBach you got the Object Explorer toolwindow showing? that's your new best friend.

this

@IvenBach sometimes.

IvenBach

15:44

@MathieuGuindon Yes.

Mathieu Guindon

good

so under Databases you'll find the DB you want to play with

IvenBach

I'm very slow and cautious when trying something new. Overly so at times.

Mathieu Guindon

expand the Databases tree, locate your DB, expand its node

only the Tables, Views, and Programmability nodes matter for now

this

and under Programmability, I'd probably stick to just Stored Procedures for now and not worry about other subfolders until you're totally comfortable with the 3 core objects (table/view/sprocs).

Mathieu Guindon

if you're feeling adventurous you can go under Functions/Scalar-valued Functions and Functions/Table-valued Functions

although, you'll want to master stored procedures first

question: do you see a SQL Server Agent node under the instance root?

(should be at the very bottom)

IvenBach

15:52

There's only the default DB that are defaults.

this

^^ Note that there are several caveats that comes with using functions, especially scalar functions. You need to reorient yourself into thinking in a set-based manner, rather than iterative manner so it's best to avoid the scalar functions. Table-valued function is likely a better next step after the sprocs, IMO.

IvenBach

SProcs is a general term for Stored Procedures?

Duga

[rubberduck-vba/Rubberduck] carlossierra311 opened issue #4003: Annotation 'ModuleInitialize' is illegal in this context

> I am getting the Rubberduck Opportunity described in the title of the issue. I've tried several things to solve it, but haven't been able so far. Things I've noticed/tried:

1. It's only present for the second to fifth test modules. The first one doesn't have this "opportunity".
2. Fixing it (adding the '@Ignore IllegalAnnotation) doesn't make it go away either, as can be seen on the screenshot.
3. Removing the tag breaks the execution of the test cases in the test module.

![modulein

IvenBach

@MathieuGuindon Yep. (Agent XP's disabled).

this

@IvenBach yes

@IvenBach sounds like you have a SQL Express or at least no permissions.

IvenBach

15:55

I installed the free version.

this

yeah, express. That's why no agent. That's expected.

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on issue #4003: Annotation 'ModuleInitialize' is illegal in this context

> If you replace the '@Ignore IllegalAnnotation line with a NEWLINE (i.e. leaving a blank line between the declarations section and the Sub statement, does the inspection result go away?

IvenBach

16:37

@MathieuGuindon Now I grok what you're saying. It'll be a while before I have anything in production. Thanks for the heads up.

Duga

16:58

[rubberduck-vba/Rubberduck] carlossierra311 commented on issue #4003: Annotation 'ModuleInitialize' is illegal in this context

> Although it isn't very clear in the screenshot, there is a line between the declarations section and the sub statement already. Anyway, I just removed the '@Ignore IllegalAnnotation with a blank line, but this didn't make the result go away. I added more white space before the sub statement, but that didn't fix it either.

[rubberduck-vba/Rubberduck] retailcoder commented on issue #4003: Annotation 'ModuleInitialize' is illegal in this context

> Ah, sorry I missed it. In any case this is worrying - it seems to imply that the test engine isn't going to recognize the ModuleInitialize method. If you put a breakpoint and run the tests, is the breakpoint hit?

IvenBach

@this BEGIN TRANSACTION can be kinda-sorta-but-not-entirely-true be thought of as a "staging area" for SQL, like what is done for git?

Duga

[rubberduck-vba/Rubberduck] carlossierra311 commented on issue #4003: Annotation 'ModuleInitialize' is illegal in this context

> But I must clear something up: all tests are running ok (even with the opportunity present). If I remove the annotation, then they break. But as shown on the screenshot above, I am being able to run all my test just fine. It's just my OCD acting up that makes me want to get rid of those "opportunities" :)

Mathieu Guindon

@IvenBach ok no. no.

this

@IvenBach a better analogy woudl be working with a text editor like Word -- BEGIN TRANSACTION disables the autosaving. If you don't disable the autosave, you end up saving what you didn't want to save

Mathieu Guindon

imagine you have a table with bank accounts and balances. you need to withdraw from one account and credit another

you can't withdraw from one account without crediting the other, and you can't credit the destination account without the withdrawal on the source account

it's a case of "all these operations must happen as an atomic unit, or not at all"

that is what a transaction is

Duga

17:05

[rubberduck-vba/Rubberduck] Vogel612 added label bug to issue #4003: Annotation 'ModuleInitialize' is illegal in this context

[rubberduck-vba/Rubberduck] Vogel612 added label difficulty-02-ducky to issue #4003: Annotation 'ModuleInitialize' is illegal in this context

[rubberduck-vba/Rubberduck] Vogel612 added label feature-unit-testing to issue #4003: Annotation 'ModuleInitialize' is illegal in this context

[rubberduck-vba/Rubberduck] Vogel612 added label feature-inspections to issue #4003: Annotation 'ModuleInitialize' is illegal in this context

[rubberduck-vba/Rubberduck] Vogel612 added label inspection-false-positive to issue #4003: Annotation 'ModuleInitialize' is illegal in this context

[rubberduck-vba/Rubberduck] Vogel612 added label feature-inspection-quickfixes to issue #4003: Annotation 'ModuleInitialize' is illegal in this context

Mathieu Guindon

i.e. if for whatever reason the withdrawal aborts, then the transaction is rolled back. ditto if the deposit fails, regardless of the reason.

this

it would be super bad for the bank if a debit happened without a credit or credit without debit. Even if the bank didn't get sued, they'd likely go out of business because money is fast going out.

Mathieu Guindon

until the transaction is committed, nothing has actually happened

this

^ that's why I referred to the analogy of autosaving -- you need to pretend that by default, any changes you make in your database are automatically saved immediately and are done so at the statement level.

Meaning, if you had two UPDATE statements in your SQL script, and 2nd one failed, you're stuck with the effects of the first UPDATE statement and in database world you totally don't want that.

Mathieu Guindon

you don't need to pretend anything, really: unless you're in a transaction, statements are committed as they run :)

@this and that's always a lot of fun!

(isn't it?)

this

17:11

In Word, it's nice to have your document autosaved because you're editing it all the time. In database, there is no "editing state", in a manner of speaking. And you definitely don't want your "drafts" going into the database.

Oh yes. Very fun.

Mathieu Guindon

and then your stress level goes up because you're working against the production DB, and shit hits the fan when you run your "this will fix it" update statement.... without a where clause

this

^ been there, done that, bought the shirt and gnashed the teeth.

so that's why when applying the changes to the production, you really really want a script that'll do it all atomically so that you can easily bail out when something goes wrong.

Mathieu Guindon

you're not a database guy until you've made that mistake IMO :)

Dain Ironfoot III

Can I make that mistake in your skin and avoid the pain?

Mathieu Guindon

then you won't learn from the pain

IvenBach

17:17

@MathieuGuindon In a hand wavey oversimplification its saying pending with "I want this stuff to be done" and if something comes up as a failure you tell it "All the pending stuff, don't actually try to perform it".

Dain Ironfoot III

@MathieuGuindon Sure I will.

I can learn from others pain pretty easily.

IvenBach

You're smart if you learn from your own mistakes. A genius if you learn from the mistakes of others.

Mathieu Guindon

yes. internally SQL Server maintains a transaction log, that can actually fill up and go over-capacity if you run a transaction with too many operations.

Dain Ironfoot III

@MathieuGuindon Please don't tell me you managed to hit that one yourself.

Mathieu Guindon

@DainIronfootIII no doubt. but the wirings in the brain aren't the same then :)

@DainIronfootIII damn right

Dain Ironfoot III

17:19

That must've been a huge transaction.

Mathieu Guindon

SSIS package...

this

Not necessarily.

Dain Ironfoot III

I thought I've seen some big ones...

this

Simply not backing up database in a timely manner can be enough.

Mathieu Guindon

...with a mistakenly cross-joining data source

this

17:20

Particularly so when using full recovery model.

Mathieu Guindon

^

Dain Ironfoot III

@MathieuGuindon Hey, a doctor doesn't have to kill someone to know not to do something.

Mathieu Guindon

this has bitten me every time

gotta go grab lunch, bbl

IvenBach

Thank goodness Mugs not a doctor.

this

@MathieuGuindon That's a lie. I don't bite people! Besides, you be in Canada, and I be on the other side of murica.

Dain Ironfoot III

17:21

And certain programmers actually have to worry about literally killing people if they make mistakes in prod.

@this Must've picked up rabies.

Mathieu Guindon

@this lol, I meant the full recovery mode thing

M.Doerner

18:06

I also hit the log capacity maximimum once on a test server. I was deleting a lot of test outputs (via a stored procedure that did not partition its work properly). The output to delete turned out to be larger than 2TB.

this

That's a good reason to never use the default of allowing your log files to grow infinitely. Giving it a large enough size and setting up a proper backup schedule avoids the dreaded issue where your log file has turned into the blob and took up the entire drive and you can't make a backup without any data loss.

M.Doerner

Well, 2TB is the largest size you can set a log file to in SQL Server. The transaction roll back log increased until it hit the capacity limit. Then the database tryed to roll back the transaction; fun.

IvenBach

Go is equivalent to saying "Do make a batch for everything between this Go and any other previous Go"?

this

@IvenBach it's not really a real SQL command

it only exists in SSMS's mind

and SQLCMD, too, I guess

either way it means to execute the SQL in that batch.

IvenBach

> SQL Server provides commands that are not Transact-SQL statements, but are recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor. These commands can be used to facilitate the readability and execution of batches and scripts.

batch is whatever is the center of a Go sandwhich?

Go
--However many statements in between
--All of this is considered a single batch
Go

this

18:15

Right, when you consider that some statements like CREATE PROCEDURE or CREATE VIEW (for example) must be the only statement in a batch, GO let you include those with other statements but behind the scene, SSMS will basically sepearate in each GO and execute each.

Yes.

one thing to watch out for --- GO; or GO --blah won't work as intended.

Mathieu Guindon

@IvenBach script 10K inserts one after the other, without a GO anywhere: you'll get an error involving "batch size" or something. now sprinkle GO every 1500 inserts or so, and the script runs fine.

this

it must be simply GO all on its own line. no extra fluff.

Mathieu Guindon

and yeah, GO basically allows you to script DROP/CREATE an entire database in one script.

IvenBach

@MathieuGuindon docs.microsoft.com/en-us/sql/t-sql/statements/… meaning inserting a record (row for those used to Excel)?

Mathieu Guindon

yes

as in INSERT INTO dbo.Table1 VALUES (1,2,3)

this

18:19

Do be extra careful when doing DDL & DML in a single script. Not all errors are fatal and SQL Server has this stupid behavior where it will keep on executing the rest of script even after encountering an error which can wreak everything, and transaction won't help you. That's why you want the SQLCMD with :ON ERROR EXIT to ensure it aborts on the first error.

Mathieu Guindon

the VBA guy inside me wants to alias this to ON ERROR GOTO HELL

IvenBach

The INTO keyword is marked [ INTO ] and therefore optional. ~~Is it a best practice to include it to avoid ambiguity/implicit stuff being done?~~ Is it taboo to not include it.

Mathieu Guindon

oh, the docs are confusing AF

IvenBach

^ I've noticed.

Mathieu Guindon

you're looking at the INSERT syntax

this

18:21

note that some of the optional stuff is simply to maintain compatiblity

Mathieu Guindon

so you can insert into dbo.Table1 (col1, col2) select 42, 'abc' when Table1 exists. or you can select col1=42, col2='abc' into dbo.Table1 to create Table1.

the INSERT syntax basically combines all possible ways to do things, so pretty much everything is optional, and none of it really is - only depends what you want to do

this

I'd prefer to avoid omitting the options. I don't like reading INSERT i(x,y,z) SELECT f.a x, f.b y f.c z FROM foo f; - I"d rather read INSERT INTO dbo.i(x, y, z) SELECT f.a AS x, f.b AS y, f.c AS z FROM dbo.foo AS f;.

Mathieu Guindon

@this wait that's legal?

this

i didn't test but off the cuff it's

Mathieu Guindon

enjoy life, stick to standard syntax

this

18:24

^

IvenBach

There is no intention on my part to try any form of cutesy-crap syntax. I want clear straightforward syntax.

Dain Ironfoot III

Yes, both of @this's examples are legal.

Mathieu Guindon

shudder

this

IKR!

Dain Ironfoot III

Hey, what's wrong with as?

this

18:28

and unfortunately i've seen SQL written in the former.

@DainIronfootIII just that it shouldn't be optional.

Dain Ironfoot III

I don't use it personally, but I don't have anything against it.

Oh, I thought you were advocating against using it.

Mathieu Guindon

@DainIronfootIII consider:

this

no, i'm advocating againtst the "short" version

because IMO it's not legible, especially in more complex queries.

Dain Ironfoot III

@this I don't write complex queries.

Mathieu Guindon

select
    Foo = table1.FooBarBaz
   ,Bar = table2.BarBazFoo
from table1 inner join table2 on table1.Foo = table2.Foo

now throw in some type casts and calculations, aggregates and whatnot

Dain Ironfoot III

18:30

If I have to nest more than one level deep, I'm doing something wrong. And nesting at all is rare.

this

fyi the x = blah is non-standard.

Dain Ironfoot III

Yeah. I do:

Mathieu Guindon

@this but so much easier to maintain

I use as for CTE's... and that's about it

Dain Ironfoot III

select
    tbl1.FooBarBaz Foo,
    tbl2.BarBazFoo Bar
from table1 tbl1
inner join table2 tbl2 on tbl1.FooBarBaz = tbl2.BarBazFoo

this

I use CTEs all the time but I normally don't do the = syntax; only the AS; I think that's a Choose your poison case.

Mathieu Guindon

18:32

@DainIronfootIII I used exactly that until I realized how much easier my life was with ColumnName = expression

also, comma-first :)

Dain Ironfoot III

Comma second.

Because then I can comment out a parameter without rearranging things.

Mathieu Guindon

there's no good way to end this :)

this

No, there isn't, @MathieuGuindon.

Dain Ironfoot III

If I comment out the top parameter with comma-first, I have to rearrange things.

@this Yeah, we really need to report Mat to the FBI.

this

@DainIronfootIII but you have the same problem with the last column the other way around

Dain Ironfoot III

18:34

Especially since he's going to be visiting Disney shortly.

@this True.

But I'm more likely to comment out the first, IME.

Why, I don't know.

this

IME, it's usually the bottom part of select i'm monkeying with

so the comma in front benefits me.

Dain Ironfoot III

Yeah. Different workflows, I guess.

I usually switch it before I check it in.

this

tbh, the sql formatting as a standard is abysmal. C# code looks pretty much same everywhere I go. but with SQL formatting, it's all over the map

Dain Ironfoot III

Q:

where foo = bar and
    fizz = buzz

Or:

Mathieu Guindon

@this as long as the style is consistent across the db, I don't care what style is used

Dain Ironfoot III

18:38

where foo = bar
    and fizz = buzz

Mathieu Guindon

2nd

the idea is that you want to be able to easily comment-out any part of the statement

(or select & run selection)

Dain Ironfoot III

You still can.

See:

Mathieu Guindon

yes, you can. but only one allows placing the caret on the line and hitting the comment-out button and running as-is

Dain Ironfoot III

where foo = bar --and
    --fizz = buzz

SQL comments are always through the end of the line.

I can't do that with the other version.

Mathieu Guindon

where foo = bar /*and
    fizz = buzz*/

Dain Ironfoot III

18:41

SQL supports that?

TIL.

Mathieu Guindon

where foo = bar
--and fizz = buzz

Dain Ironfoot III

@MathieuGuindon What about?

where --foo = bar
    and fizz = buzz

this

not legal

Mathieu Guindon

/* */

this

^

Dain Ironfoot III

18:43

Considering I didn't know SQL had /**/ comments, the other made more sense.

this

same situation as before wRT comma first/last for parameter list

Mathieu Guindon

/**/ is annoying though

this

someone suggested doing /* with a --*/

that way you can comment only the start and it just works

Mathieu Guindon

I tend to always select a column I 100% need first, and always filter a column I 100% need to filter first, so, #N/A

this

yes, best

IvenBach

18:45

Might I get a bit of help with starting an Insert? I'm trying to change a fields type to money from nvarchar(255) but get a warning about `"Saving changes is not permitted.".

I would like to copy the rows but am drowning in documentation.

Mathieu Guindon

cast(field as nvarchar)

this

best to specify the width

Mathieu Guindon

^ another legit use of as

this

cast(field as nvarchar(255))

otherwise you get funky results

Mathieu Guindon

meh. you only get funky results with funky data in a funky schema.

#ShitInShitOut

IvenBach

18:48

I'm going to get funky results then.

this

Bad habits to kick: Declaring varchar without length

Mathieu Guindon

I mean, your money column might be 6-15 digits, including the dot and decimals. you're inserting it into a nvarchar(200), what's the risk of omitting the length?

hmm

hasn't bitten me yet

Dain Ironfoot III

FWIW, I do do my SQL and operators before the line.

this

i've run into it before.

Dain Ironfoot III

I like the way they look lined up.

this

18:51

SQL Server's defaults has a tendency to be.... crappy.

Mathieu Guindon

SELECT 'YES', 'INDEED', 'ABSOLUTELY'

Transcript for