question

52673392 avatar image
0 Votes"
52673392 asked 52673392 answered

Problems with node js and mssql

I have managed to load data into a mssql database OK.
On reading it out the results length is undefined.
I suspect the data coming out isnt JSON so node doesnt know what to do with it.
I can see the data if I json.stringify it but it needs to be in JSON format for my program to use it.

Also if i "DROP TABLE xxxx IF EXISTS" the if exists causes a syntax error.
mssql seems to have a lot of functionality missing.
Have I missed something somewhere ?

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@nigelwright-2487,

Please update your question with the following info:

  • SQL Server version, (SELECT @@VERSION:)

  • Your database compatibilty level. You can get it via T-SQL below. Just change the DB name.


      USE AdventureWorks2012;  
        GO  
        SELECT compatibility_level  
        FROM sys.databases WHERE name = 'AdventureWorks2012';  
        GO  
    
0 Votes 0 ·
52673392 avatar image
0 Votes"
52673392 answered YitzhakKhabinsky-0887 commented

I was running sql server 2012 so hopelessly out of date.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@nigelwright-2487,

You would need SQL Server 2016 or later, to support your JSON needs.

Here is a good link on SQL Server versions: sql-server-2016-falling-out-of-mainstream-support


0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

Have I missed something somewhere ?

Yes, at least2 thinks.

First, read documentations, like DROP TABLE (Transact-SQL), the right syntax is DROP TABLE IF EXISTS dbo.Tablename
Second, you didn't provide useful informations or code. How do you access SQL Server/which which library/data provider, how does the table design look like, and so on.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

52673392 avatar image
0 Votes"
52673392 answered

SSMS is throwing out if exists so it cant be in my sql server.
Same goes for "FOR JSON AUTO"


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

SSMS is throwing out if exists so it cant be in my sql server.

Same goes for "FOR JSON AUTO

If SSMS is throwing things out, it is because it looks at what version of SQL Server you have, so it will give you red squiggles for unsupported syntax.

Both FOR JSON and DROP ... IF EXISTS requires SQL 2016. Apparently, you have an older version. Use "SELECT @@version" to find out.

(Compatibility level may also matter. But not for these two examples; they work on lower compat levels as well on SQL 2016. I just tested.)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

52673392 avatar image
0 Votes"
52673392 answered

An upgrade on my server to SQL server 2017 fixed my problems.

mssql is different to mysql in its output from database.
mysql outputs straight JSON.
In mssql you have to extract it from "recordset" in json.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.