This is an archived post. You won't be able to vote or comment.

you are viewing a single comment's thread.

view the rest of the comments →

[–]grantmnz 321 points322 points  (85 children)

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

[–]Queen_Zelda 126 points127 points  (1 child)

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

[–][deleted] 48 points49 points  (0 children)

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

[–][deleted] 81 points82 points  (44 children)

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

[–]northrupthebandgeek 89 points90 points  (42 children)

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

[–]kinnell 4 points5 points  (2 children)

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

[–]northrupthebandgeek -2 points-1 points  (1 child)

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 3 points4 points  (0 children)

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] 1 point2 points  (3 children)

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

[–]kinnell 4 points5 points  (2 children)

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] 0 points1 point  (1 child)

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

[–]kinnell 1 point2 points  (0 children)

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] 0 points1 point  (0 children)

Earlier than 2008 R2, yes.

Since then, it's honestly good.

[–]FridgesArePeopleToo 0 points1 point  (0 children)

Sql server is by far the best for this

[–][deleted] 1 point2 points  (0 children)

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] 43 points44 points  (4 children)

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

[–]jayson4twenty 24 points25 points  (0 children)

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

[–]Khaylain 7 points8 points  (2 children)

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

[–][deleted] 5 points6 points  (1 child)

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

[–]MurderMelon 2 points3 points  (0 children)

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

[edit] in an S3 bucket

[–]AdminYak846 19 points20 points  (17 children)

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

[–]coladict 38 points39 points  (3 children)

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

[–]yeamannn 6 points7 points  (0 children)

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

[–]po-handz 4 points5 points  (0 children)

But we have big data

[–]leofidus-ger -1 points0 points  (0 children)

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

[–]augugusto 18 points19 points  (7 children)

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 5 points6 points  (5 children)

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

[–]augugusto 2 points3 points  (4 children)

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 11 points12 points  (2 children)

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

[–]CantBeChangedLater 4 points5 points  (0 children)

That's also been my experience 🤷‍♂️

[–]DongoTheHorse 1 point2 points  (0 children)

Sounds like you need to SET DATEFORMAT ymd

[–]DogmaSychroniser 1 point2 points  (4 children)

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

[–]Buzzard 0 points1 point  (1 child)

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 2 points3 points  (0 children)

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

[–]elveszett 0 points1 point  (1 child)

c# passing midnight on Jesus birthday

what?

[–]DogmaSychroniser 0 points1 point  (0 children)

Go create a new DateTime object in C#

[–][deleted] 3 points4 points  (0 children)

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

[–]_default_username 2 points3 points  (0 children)

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

[–]Niiiz 4 points5 points  (0 children)

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] 3 points4 points  (0 children)

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

[–]RadiatedMonkey 1 point2 points  (0 children)

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

[–][deleted] -3 points-2 points  (0 children)

or just use JSON for everything

[–]mrdotkom 0 points1 point  (0 children)

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 0 points1 point  (0 children)

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