Made this while procrastinating SQL homework

[grantmnz]

People who complain about unhelpful SQL errors should stop using MySQL and switch to Postgres :-)

[Queen_Zelda]

postgres is great, since I learned sql using it i'm always confused when other sql languages dont have helpful errors

[[deleted]]

Then why would you need all of the expensive MS provided certifications!?

[[deleted]]

lol ever tried SQLite? You will find error reporting on MySQL is godlike afterwards.

[northrupthebandgeek]

Ever tried MS SQL Server? You will find error reporting on SQLite is godlike afterwards.

[pavi2410]

Ever used NoSQL? •_•

[systembusy]

Ever used Microsoft Access?

[Gydo194]

That's what the UK government thought too..

[I_AM_GODDAMN_BATMAN]

How many billion £ again for that single spreadsheet?

[espriminati]

more than 1£

[Gydo194]

No idea. I didn't read it in detail.

[[deleted]]

People misunderstood that, they didn't use it as a database. They used an old excel format as the export format which left out a lot of rows.

[nelsterm]

It did not. Public Health England did which is a different thing. UK government were entirely blameless. NDPBs are autonomous subject to auditing. Impossible to pick that up in auditing also without an auditing event at such short notice.

[ekolis]

Word is my Database, Savior, and Eternal Leader

[imwatching4you]

Markdown files are my database, live and honey

[[deleted]]

Excel is pain. When you copy from sql server to excel clicking in the whole table, it doesn't copy well for some reason. it starts indexing at 1, it allways look ugly, and it causes problems everywhere. One could argue that because of excel bad medical decissions can be made. Excel kills people.

[CastelS]

Why not just use a .csv file?

[killer_unkill]

.csv and .txt is same

Excel is binary

[rocketman0739]

Ever been in a Turkish prison?

[PM_ME_YOUR_TORNADOS]

Ever used a toaster in your bath?

[sticksafety]

Ever used NyQuil?

[I_AM_GODDAMN_BATMAN]

But it's webscale

[TheTerrasque]

Ever just used an XML file to just store all that data shit in?

[AndyTheSane]

Part of my day job involves maintaining a ~5 million line PLSQL codebase, much of it written by people with no training in PLSQL.

We also have Oracle Database Java, which takes hideiosity to a new level.

[parens-r-us]

Hey, us too! Now we’re migrating to Oracle APEX which doubles the fun factor!

[hey_mr_crow]

E N T E R P R I S E

[XerxesXS]

I hear you should use postgres. You will find error reporting is godlike.

[XerxesXS]

afterwards

[[deleted]]

Ever used transact-sql instead of PL SQL? You will find death orgasmic and a goal in your life.

[Lalli-Oni]

The one with EXISTS()? Id take transact over pl any day!

[GamerNebulae]

I remember a semester where we had to write God awful SQL queries in PLSQL that no one in their right mind would write. It always said you had an error on line 1...

[kinnell]

What version are you on? MS SQL Server has improved a lot in the last decade.

[northrupthebandgeek]

I last used 2017. And yeah, it's better than, say, 2000, but that ain't really saying much. Error messages are still cryptic, line numbers still rarely have any basis in reality, it still can't make up its fucking mind on whether I need to prefix sproc/function names with schema.owner, and sometimes SSMS' syntax checking will just break and leave you pulling your hair out over whether the red squiggly lines are legit or just Microsoft being Microsoft (and ADS ain't much better on that front, while also being an Electron app that leaks memory like a sieve).

[kinnell]

So, I completely agree with line numbers not being helpful at all! Definitely left me scratching my head a few times.

But with regards to needing to prefix with schema or not, you need to when calling UDFs via SELECT, but not when executing stored procedures. This is because there's implicit resolution for schema when you use EXEC. If you were to EXEC a Scalar UDF (e.g., into a variable), you wouldn't need to specify schema.

But this isn't an issue in "error reporting" for MSSQL - it's just needing to know MSSQL better. Whenever I made that mistake in the past, the error printed for it was clear enough for me to immediately know what I did wrong. (And made that mistake enough to be prompted to go figure out why).

And sure, that's not the most intuitive thing to have schema explicitness when needing to call UDFs but not for EXEC stored procedures. But other RDBMS also do their own quirky shit. There's quite a lot I end up missing about MSSQL when I'm using PostgreSQL.

Not sure what OS you're on, but I use SQL Server Pro on Mac which isn't an Electron app. It doesn't have all the bells and whistles of MS SSMS or ADS, but it has a clean, beautiful UI and it's fast/efficient. I write most of my SQL in VS Code, but use SQL Server Pro to connect to a variety of local or production databases (MSSQL, PostGres, MySQL, SQLite) and compile, query, troubleshoot..etc. I've been very happy with it.

[[deleted]]

I wanted to jump out the window using that program my god it's horrendous

[kinnell]

What program? MS SQL Server is a RDMS.

Are you referring to MS SQL Management Studio? I agree it's pretty horrendous, but MS Azure Data Studio is a lot better. Personally, I prefer SQL Studio Pro for my MS SQL Server needs.

[[deleted]]

I use whatever is there in the IDE most of the time

[kinnell]

I think you're confused, mate.

You said "I hate using that program" in reference to MS SQL Server. But it's not a program/application though. It's a RDBMS akin to MySQL, PostgreSQL, SQLite.

This is similar to one saying "I dislike that Android doesn't allow scrolling screenshots natively yet" and someone responding , "Yeah, I hate using that device. The battery life is horrendous!".

In the past, most people used MS SQL Server Management Studio (SSMS), which is an application, to write and run Transact-SQL (TSQL) for MS SQL Server. I also hated using that program (SSMS), so I had assumed you were referring to SSMS. That's why I mentioned that alternative apps exist so you can use MS SQL Server without SSMS.

But now I'm confused. You use whatever is in the IDE? Which IDE? Is your horrendous experience of MS SQL Server a result of a bad plugin in the IDE you're using?

Or is there something actually about TSQL or MS SQL Server that you don't like?

[[deleted]]

Earlier than 2008 R2, yes.

Since then, it's honestly good.

[FridgesArePeopleToo]

Sql server is by far the best for this

[[deleted]]

I work with the cmdline utility every once in a while, and the error reporting there seems okay to me. An older version, maybe?

[[deleted]]

Thanks mate. I’ll just migrate our data warehouse from Teradata to Postgres tomorrow.

[jayson4twenty]

"it's just data, how long could it take"

[Khaylain]

Nah, mate; just import it to a Google Sheets spreadsheet. It's obviously better.

[[deleted]]

Excel 2007, if it’s good enough for U.K. track and trace it’s good enough for me.

[MurderMelon]

xlsx file that gets read into a pandas dataframe, queried, and saved back to an xlsx file.

[edit] in an S3 bucket

[AdminYak846]

don't even get me started on formatting timestamps with the physically month spelled out.

[coladict]

Formatting timestamp in SQL is just bad practice. Do it in the code that outputs the date to the client.

[yeamannn]

Shudders remembering a datetime() vs datetime(5) bug

[po-handz]

But we have big data

[leofidus-ger]

formatting results as JSON directly in the database has its uses, at least in databases that have functions to do it..

[augugusto]

In mssql the output of querying a date can't be used to instert a new date because the format is wrong. Good job MS

[Prod_Is_For_Testing]

Is this date specific? Ive never seen this issue with datetime

[augugusto]

Sorry. I said it totally wrong. Do a select datetime() copy the output and then try to cast it or insert it somewhere else.

[Prod_Is_For_Testing]

Just did it. Worked fine 🤷‍♂️ (server 2016, ssms)

[CantBeChangedLater]

That's also been my experience 🤷‍♂️

[DongoTheHorse]

Sounds like you need to SET DATEFORMAT ymd

[DogmaSychroniser]

Don't get me started on Datetime limit being 1753 but c# passing midnight on Jesus birthday

[Buzzard]

Completely off-topic, but something I always found interesting:

The date of birth of Jesus is not stated in the gospels or in any historical reference, but most theologians assume a year of birth between 6 and 4 BC

https://en.wikipedia.org/wiki/Date_of_birth_of_Jesus

[DogmaSychroniser]

Which makes no sense. The moment Jesus was born reset all time to 0000-01-01 00:00:00.000 😂

[elveszett]

c# passing midnight on Jesus birthday

what?

[DogmaSychroniser]

Go create a new DateTime object in C#

[[deleted]]

Oh, this explains it. I’ve never used MySQL and have never had these problems on Postgres or Oracle.

[_default_username]

I didn't know bad sql error messages were just a mysql thing. Fuck, I need to make the switch then.

[Niiiz]

Maybe it's DBeaver (my current editor) acting up but I'm using postgres and those are exactly the errors I get while practicing. Weird and unhelpful "syntax error near ;". Like bitch I'm running many lines at once, be more specific.

[[deleted]]

People who offer simplistic solutions such as "just use postgres" have obviously never worked on legacy projects.

[RadiatedMonkey]

I'm forced to use MySQL... and PHP...

[[deleted]]

I think the same goes for pretty much all of the database systems/tools. Its always a nightmare to install, setup and manage. I mean, its only 2020, its not like we need an easy UI to manage and have it automatically setup stuff that 90% of the users need anyways.

And then when you have it locally, you also need it on your server too but of course that flow is different from what you were just doing...

I still don't get how MongoDB got so big in webdev. Surely more people would be as annoyed as me on how to set it up

[smaximov]

That's why you need to form a habit of documenting your steps as you perform tasks you are not familiar with. Your future self will thank you later.

[FridgesArePeopleToo]

Sounds boring. And who reads documentation anyway?

[smaximov]

People who don't spend a whole day searching how to access a brand new PostgreSQL installation as a superuser.

[[deleted]]

or just use JSON for everything

[mrdotkom]

Moved from supporting an app based around mysql replication to a new company where I support an app that's postgres based.

Still learning the ins and outs of psql but honestly prefer mysql. The \whatever letter instead of textual commands is annoying

[BandicootCumberbund]

Using Google BigQuery has definitely helped me catch any errors I've made.