0

unable to connect MS SQL server in incorta

Hi Team,

 

Please help we are trying to add new data source in our incorta system. when we try to connect getting "Driver:SQLServerDriver:9 returned null " error and it is working fine in SQL developer with same connection details.

INC_005002001:Failed to Connect to [test] due to [Driver:SQLServerDriver:9 returned null for URL:jdbc:mysql://host:49170/test] with properties [[database, sqlserver], [user, jams], [connectionPool, 30], [connection, jdbc:mysql://host:49170/test], [driver, com.microsoft.sqlserver.jdbc.SQLServerDriver]]

 Thanks,

Delli M

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Delli are you sure you have configured your data source correctly? I see you are using the Microsoft SQL Server driver but your JDBC URL contains mysql. Here is an example of a SQL Server JDBC URL I've used recently. Remember that when using port number with SQL Server that you do not specify instance name:  jdbc:sqlserver://sample_server.incorta.com:4809;databaseName=sample_database;

    Reply Like
  • Thx Dustin Basil  you are Correct :) now i can able to connect. but when define the query with CTE function in incorta end it it throwing error which is running fine in SQL server not in incorta.

    Error :

    incorrect syntax near the keyword 'WITH'.] with properties [[database, sqlserver

    SQL:

    WITH path(FolderId, FolderName, parentfolderid, LEVEL, treepath) AS
    ( SELECT FolderId AS FolderId, FolderName, parentfolderid, 0 AS LEVEL,CAST(FolderName AS VARCHAR(1024)) AS treepath FROM folder WHERE parentfolderid IS NULL
    UNION ALL
    SELECT d.FolderId AS FolderId, d.FolderName, d.parentfolderid,path.LEVEL + 1 AS LEVEL,CAST(path.treepath + ' -> ' +
    CAST(d.FolderName AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath FROM folder d
    INNER JOIN path ON path.FolderId = d.parentfolderid)

     

    Thanks,

    Delli M

    Reply Like
    • Delli I have never used table expressions in an Incorta table query but the first thing I'd try is to prefix your WITH keyword with a semicolon. For example:

      ;WITH path(....

      Reply Like
  • Dustin Basil Try ;WITH but still no luck :(

    Reply Like
  • Can you please click on "Done" instead of Execute on Edit query window. and load table from schema? CTE should work. 

    Reply Like
    • Siva Kowsika I have the same problem with "Execute" when using stored procedures with MS SQL.  They work fine when clicking "Done", they just don't preview / test.  Must be a single result set returned though.

      Reply Like
    • Siva  fine now :)

      Reply Like
  • I will ask QA team to review further. During the preview, we'd like to limit result to 250 rows. So, we have a wrapper select top 250 * () t on top of the input query. 

    Im not sure, we do support sql CTE, store proc officially yet. Remember, sql store proc can return multiple result sets to the client. 

    Reply Like
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 8Replies
  • 51Views
  • 4 Following