graphql-engine: SQL Server Incorrect syntax Exception

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!

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 22 (8 by maintainers)

Most upvoted comments

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.

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.

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.

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;