Any way to get sql server error message?

[merlinm]

Currently, when executing queries that fail, the error message returned is a generic one, e.g.

ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

Is there any way to get the specific context of the error? this would signficantly aid in debugging and development.

[GeoffMontee]

Yeah, set msg_handler to notice. See here:

https://github.com/tds-fdw/tds_fdw/blob/master/ForeignServerCreation.md

[merlinm]

ok, I see that. Well that is helpful. e.g. the output is: NOTICE: DB-Library notice: Msg #: 207, Msg state: 1, Msg: Invalid column name 'idd'., Server: RCDYLSMSTSQL001, Process: , Line: 1, Level: 16 ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

Having said that, for SQL level errors, I would humbly suggest that this behavior is sub-optimal; ideally we'd want to have the SQL error message be embedded in the error TEXT. There's a lot of reasons for that. For example, when trapping errors in pl/pgsql having the error present in SQLERRM allows for better diagnostics of whatever issue. The library is great btw, I'm jumping in heavily (hence the feedback).

[merlinm]

I don't know if my suggestion is practical. I did take a look a the code and It's not so complex. It may be possible to save off the last 'notice' generated error and try and save it off so that it can be rendered with the raised error.

[GeoffMontee]

Yeah, it might be possible to save notices, and then print them whenever an error is thrown. The problem is that FreeTDS generates a lot of messages, and it is not always easy to determine which ones are relevant, and which ones are noise.

I generally just set msg_handler to notice when I'm debugging, and then set it back to default when I want it to be quiet.