Vscode-sqltools: Can't connect to MSSQL Server 8.x

[glha]

Describe the bug
Cannot connect to a MSSQL Server version 8.x. Versions 11.x and 13.x works just as expected, even though the configurations look the same.

To Reproduce
Steps to reproduce the behavior:

1. Add Server to CONNECTIONS
2. Click on created connection to login into server
3. After some time, the error is displayed and the Log is as follows
4. See error log
   --------==--------------==-------------

INFO: Executing SQLTools.selectConnection
ERROR: Error:  { Error: Connection lost - read ECONNRESET
    at c:\Users\//removed//\.vscode\extensions\mtxr.sqltools-0.21.0\extension.js:1:22923
    at c:\Users\//removed//\.vscode\extensions\mtxr.sqltools-0.21.0\extension.js:1:23330
    at Immediate.messageQueue.size.setImmediate (c:\Users\//removed//\.vscode\extensions\mtxr.sqltools-0.21.0\extension.js:1:23874)
    at runCallback (timers.js:694:18)
    at tryOnImmediate (timers.js:665:5)
    at processImmediate (timers.js:647:5) code: -32001, data: { dialect: 'MSSQL', dialectOptions: {} } }

--------==--------------==-------------
Expected behavior
A seamless connections, as it happens with other servers versions.

Desktop (please complete the following information):

• SQLTools Version [e.g. v0.21.0]
• VSCode Version: [e.g 1.39.2]
• Dialect Used/Version: MSSQL versions 8.x, 11.x, 13.x
• OS: Windows10

Additional context
Important to add that all connections, with the same configurations listed below (ports, servers, users), works with other clients, such as the official SSMS or third party software, like HeidiSQL.

SQLTools entries for each server:

Server version 8.x

{
  "database": "db8",
  "dialect": "MSSQL",
  "id": "db8|MSSQL|server8|db8",
  "isActive": false,
  "isConnected": false,
  "name": "db8",
  "password": "user8pass",
  "port": 1433,
  "server": "server8",
  "username": "user8"
}

Server version 11.x

{
  "database": "db11",
  "dialect": "MSSQL",
  "id": "db11|MSSQL|server11|db11",
  "isActive": false,
  "isConnected": false,
  "name": "db11",
  "password": "user11pass",
  "port": 1433,
  "server": "server11",
  "username": "user11"
}

Server 13.x

{
  "database": "db13",
  "dialect": "MSSQL",
  "id": "db13|MSSQL|server13|db13",
  "isActive": false,
  "isConnected": false,
  "name": "db13",
  "password": "user13pass",
  "port": 1433,
  "server": "server13",
  "username": "user13"
}

[mtxr]

Hi @glha.

I haven't test with MSSQL v8 for while. But I'll do it till Sunday and get it working again, ok?

[mtxr]

@glha the sql server v8 image I was using to test is not available anymore, so I can't test.

If you can go to another tool and run the queries in this file https://github.com/mtxr/vscode-sqltools/blob/master/packages/core/dialect/mssql/queries.ts (changing placeholders like :table), you should be able to see some errors. If you can send me this errors, I could try to updated the queries.

But to be honest, if you have sometime, would be better for you to work on this queries yourself and sending a pull request. I won't be able to test it with this V8.x version

[glha]

Oh, I see.
Most of it comes from CONCAT, that isn't a thing in v8.x. You can pretty much replace it with ISNULL and a plus sign, so this:
concat( f.routine_schema, '.', f.routine_name ) as signature
become this:
( ISNULL(f.routine_schema, '') + ISNULL('.', '') + ISNULL(f.routine_name, '') ) as signature
The first two queries are displayed, once changed like that. You can use them in newer server versions too, so you can compare the results.

The issue I don't know how to approach is this piece, from the third query:
f.routine_definition AS source FROM information_schema.routines AS f
The result is greater than 8 KBytes for some procedures, so v8.x can't deal with it as a row. Is there anything we can do about this? Split the source? Compress to and from a blob, while retrieving the data?

[mtxr]

@glha Awesome. Let's replace than by ISNULL.

f.routine_definition AS source FROM information_schema.routines AS f to be honest, this is not really used, we could remove that for now.

I'm working in new queries for v0.22, and that will fetch pieces of information from other metadata tables, like DataGrip for example. That would probably fix this 8KB issue.

[mtxr]

Do you plan to send a PR? if so, I'll assign this issue to you.

Just let me know.

Thank you for all the help debugging :D

[glha]

Yes, I can fork and send the PR. Just give me a moment.

Edit: Done. I hope none of the changes messes with the extension.

[mtxr]

Great job!

I'll merge it right now.

Thank you @glha