SQL Server Incorrect syntax Exception

[dkalaria]

Hello,
I really appreciate the graphql-engine project and i am really excited to try it out. I am trying to run the project on docker for MS Sql Server. I have a remote database server and I was able to connect to the server using the connection string:
Driver={ODBC Driver 17 for SQL Server}; Server=remoteurl,1433;Database=tempdb;Uid=sa;pwd=password and I am using
hasura/graphql-engine:v2.0.0-alpha.11 version.

When I try to query the object after tracking it, I get following error:

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "tag": "unsuccessful_return_code",
          "contents": [
            "odbc_SQLExecDirectW",
            -1,
            "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'.o"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "sql server exception"
    }
  ]
}

I am using the following docker-compose file:

version: '3.6'
services:
  postgres:
    image: postgres:12
    restart: always
    volumes:
    - db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: postgrespassword
  graphql-engine:
    image: hasura/graphql-engine:v2.0.0-alpha.11
    ports:
    - "8080:8080"
    depends_on:
    - "postgres"
    restart: always
    environment:
      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## enable the console served by server
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
      ## enable debugging mode. It is recommended to disable this in production
      HASURA_GRAPHQL_DEV_MODE: "true"
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
      ## uncomment next line to set an admin secret
      # HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey
volumes:
  db_data:

Is there any configuration that I am missing? I expect since the graphql-engine is running inside docker I don't have to install it on my local. Do I need to add any config for SQL server in the docker-compose yml file? i am using connect database option to add database source to a remote SQL server instance.

Appreciate it!

[0x777]

@dkalaria Looks like we are generating a SQL query that is not valid on SQL Server. Can you share the GraphQL query for which you see this error?

[dkalaria]

@0x777 Here is the query i am trying to run. Its the bare minimum

query MyQuery {
  Application {
    UserID
    ApplicationID
    CreatedDate
    Type
  }
}

[0x777]

@dkalaria Do you have any permissions defined?

[dkalaria]

@0x777 I am able to connect to the server and also track the tables. Just not able to query it. What kind of permission would restrict the execution of a query?

[nicuveo]

@dkalaria do you have any row select permissions on that table by any chance? Since those permissions filter the rows of the table, they translate into a WHERE condition in the generated query, this might be where we generate an invalid SQL query string.

Could you also try using the Analyze button in the console? It will give you the generated SQL query, and we can use that to pinpoint the bug.

Thanks in advance!

[dkalaria]

Sure, On analyze button, I see Analyze query error pop up and console error:
POST http://localhost:8080/v1/graphql/explain 500 (Internal Server Error)

[nicuveo]

Argl, sorry about that, that was to be expected. 😞
I'll see if I can fix Analyze so that it still outputs the generated SQL in case of an error.

In the meantime, would you mind exporting your metadata (go to /console/settings/metadata-actions and click "Export metadata"), so we can try to figure out what's causing the issue?

[dkalaria]

@nicuveo No worries.
Here's the metadata content.

{
  "resource_version": 13,
  "metadata": {
    "version": 3,
    "sources": [
      {
        "name": "default",
        "kind": "postgres",
        "tables": [],
        "configuration": {
          "connection_info": {
            "use_prepared_statements": true,
            "database_url": {
              "from_env": "HASURA_GRAPHQL_DATABASE_URL"
            },
            "isolation_level": "red-commitated",
            "pool_settings": {
              "connection_lifetime": 600,
              "retries": 1,
              "idle_timeout": 180,
              "max_connections": 50
            }
          }
        }
      },
      {
        "name": "QA",
        "kind": "mssql",
        "tables": [
          {
            "table": {
              "schema": "dbo",
              "name": "app"
            }
          }
        ],
        "configuration": {
          "connection_info": {
            "connection_string": "Driver={ODBC Driver 17 for SQL Server}; Server=<remote-url>,1433;Database=tempdb;Uid=usr;pwd=pwd",
            "pool_settings": {
              "idle_timeout": 5,
              "max_connections": 50
            }
          }
        }
      }
    ]
  }
}

[jmillerv]

Having a similar issue with alpha.11 here's my error and my query looks similar to the OP's.

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "tag": "unsuccessful_return_code",
          "contents": [
            "odbc_SQLExecDirectW",
            -1,
            "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'."
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "sql server exception"
    }
  ]
}

here's my metadata

{
  "resource_version": 8,
  "metadata": {
    "version": 3,
    "sources": [
      {
        "name": "QA",
        "kind": "mssql",
        "tables": [
          {
            "table": {
              "schema": "dbo",
              "name": "myAppdb"
            }
          }
        ],
        "configuration": {
          "connection_info": {
            "connection_string": "Driver={ODBC Driver 17 for SQL Server};Server=<remote-url>;Database=<dbname>;Uid=usr;Pwd=pwd;",
            "pool_settings": {
              "idle_timeout": 5,
              "max_connections": 50
            }
          }
        }
      }
    ]
  }
}

[wc-matteo]

very similar error when trying to make a simple query from the hasura api explorer:

query MyQuery {
  test1 {
    foooooo
  }
}

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "tag": "unsuccessful_return_code",
          "contents": [
            "odbc_SQLExecDirectW",
            -1,
            "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "sql server exception"
    }
  ]
}

if I go in the raw sql page and try to make the same query:

select foooooo from test1

it works as expected

---

hasura v2.0.0-alpha.11
mssql 2016

[0x777]

@dkalaria Can you share the schema of the Application table? Also the SQL server version?

@jmillerv If you can share the GraphQL query, the schema of the tables that are involved in this GraphQL query and the SQL server version, it would be very helpful

[jmillerv]

@0x777 will do. It might be Tuesday at the earliest that I'm able to share it as I'm away from my work computer for the next few days.

[0x777]

Hey folks, can you let us know the version of sql server you are on by running this query:

SELECT @@VERSION;

We suspect that the older versions of SQL Server 2016 do not support OPENJSON WITH which causes the above error. However SQL Server version 2016 that can be deployed on Azure does not exhibit this behaviour, i.e, everything works as expected. So the version information will be very helpful to us. If your SQL Server 2016 has updates, please update it and let us know if the issue still persists.

We've reverted the sql generation changes introduced in alpha.11, so that even with an older version of SQL Server 2016, queries should work. However subscriptions still require OPENJSON WITH support. Please try this build out and let us know if everything works as expected:

hasurabuild/graphql-engine:mono-branch-main-720c0e0c7

[jmillerv]

@0x777 here's the MySQL version I'm working with currently. I'll go try that build right now.

Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64) 
	Aug 20 2020 22:33:27 
	Copyright (C) 2017 Microsoft Corporation
	Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

[jmillerv]

Update: so far the hasurabuild/graphql-engine:mono-branch-main-720c0e0c7 build doesn't return the Incorrect Syntax Exception.

[jmillerv]

@0x777 Quick question. The build you commented with appears to have solved the above problem and I have things working. What's the recommendation for continued development? Should I just roll with this build you provided or will there be changes inside of alpha.12 to reflect this issue?

Thanks for your time and help!

[wc-matteo]

Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64)
	Jun 15 2019 19:20:12
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: )

I can confirm that using OPENJSON ( @json ) WITH gives the same error seen in hasura

[jmillerv]

I just tested OPENJSON (@JSON) in mine and I didn't get the hasura error, but I did have to alter the database compatibility to get OPENJSON to work. That being said, I was getting an invalid object error and not Incorrect Syntax Exception.

ALTER DATABASE db SET COMPATIBILITY_LEVEL = 140;

[0x777]

Hey folks, with beta.1, we've changed the query generation logic to not use OPENJSON WITH, so queries should work fine. However, subscriptions require OPENJSON, so you may see runtime errors with 'sql server exception'. If this happens, check the COMPATIBILITY_LEVEL of your database as follows (OPENJSON requires COMPATIBILITY_LEVEL of at least 130):

SELECT name, compatibility_level FROM sys.databases;

If it is lower than 130, you'll need to change the compatibility_level of your database to get OPENJSON and hence subscriptions working. Please let us know if subscriptions fail to work even after setting COMPATIBILITY_LEVEL to 130 or more.

[0x777]

I'm closing this issue as this should be resolved with beta.1. However please feel free to open a new issue or reopen this if you still face this error.

[jmillerv]

I'm closing this issue as this should be resolved with beta.1. However please feel free to open a new issue or reopen this if you still face this error.

Meant to update this and say that everything has been working on my end since beta.1

Thanks!

[obrunno]

I just tested OPENJSON (@JSON) in mine and I didn't get the hasura error, but I did have to alter the database compatibility to get OPENJSON to work. That being said, I was getting an invalid object error and not Incorrect Syntax Exception.

ALTER DATABASE db SET COMPATIBILITY_LEVEL = 140;

Tnks man. Its solved my problem with subscription errors.