Error when trying to extract a table from a SQL connection using the Connect In-DB tool

[eric_rigolon]

Hello everyone,

I'm currently having issues trying to acess a Table when i connect to a Source using the Connect In-DB tool.

I Have the connection (Read/Write Driver: SQL Server OLE DB) estabilished and i can use the Query Builder to view all the Tables through the Visual Query Builder, but when i try to run the flow using anything, even a browse tool directly after the Connect In-DB tool, i get the following error message:

Browse In-DB (2) Error: Microsoft OLE DB Provider for SQL Server: Incorrect syntax near the keyword 'WITH'.\42000 = 156¶ in Query: WITH "Tool1_b979" AS (Select * From FICHA_FICHA_ITEM) SELECT TOP 100 * FROM "Tool1_b979"

When i connect to the same Source using the Microsoft SQL Management Studio, I can see all the tables and use it just fine.

Can anyone help please?

[bsharbo]

Can you post a copy of your workflow (you can obviously change the database connection + password).  

From the looks of your error the query you are trying to pull from isn't correctly built and that is causing the error, but adding the workflow would allow me to help check for you

[eric_rigolon]

Hello bsharbo, I can't post a print screen right now cause i'm inside my company and can't upload files due to blocks from the IT security sector.

I'm doing a simple Select * from "Table name" 

When i get home I'll upload the printscreen!

[bsharbo]

Sounds good. Secondary question, do you actually need to use the connect in DB tools?  

From your description so far you are simply to query from a databse table, which you can do with a standard input tool... I imagine you just simplified your question and there is another reason you need the In-DB tools, however I wanted to confirm that (Since you cant upload the workflow yet)

[GeoffColes]

Hi - were you able to finally resolve this issue? We had a similar error using the input tool and are currently investigating an issue with the OLEDB driver. A workaround we discovered is to remove all tabs from the query. This seems to enable the input tool to process the query in alteryx fine. 

[FTuvene]

Having a similar problem myself, when opening up the connection a message pops-up stating "invalid SELECT statement" but there are no errors when test validating the query within the connection. When the query is executed it ends up with an error.

However, when I take the exact same SQL code and paste in into our database application it executes without any issues. Not sure what Alteryx is doing to the query so it results in an error.

I also want to point out that the "invalid SELECT statement" message also pops-up on other connections that work fine.

[SeanAdams]

Hi all,

Can we work together to debug this - that way we can either get you back on the road, or submit a highly specific defect report that the team can turn-around quickly (with an example that can be replicated every time)?

- Firstly: put a "dynamic Output in-DB" just after the string of controls that assembles the query

- Make sure that on this tool you select "Query" so that you can get the query string

- This query string then comes out with a long series of queries preceeded by a With Statement - pop this in to SQL Enterprise Manager or your favourite query tool and see how it looks. You'll need to pretty-format it to get it to make sense (see below)

- If this query works in Enterprise Manager, then the problem may reside in the final execution process - if this query does not work in Enterprise manager, then there may be a problem with the query assembly.

Note: SQL server (and other databases) tend to be fairly prickly about funny characters in table names - not sure if this is the case here.

Depending on how this goes, I have MS SQL server installed on my home machine so we can try to simplify this down to the specific thing that's breaking this. For example - we try different tables; or different columns or different transformations. All we'll need is the table structure (create table xxx ) and the workflow that you're running, and I can work with you to dig into this.

Example of generated SQL:
WITH "Tool1_4eb8" AS (SELECT * FROM Sales), "Tool2True_cf2b" AS (SELECT * FROM "Tool1_4eb8" WHERE "SALESDATE" > '01 apr 2017'), "Tool4_d853" AS (SELECT "StoreCode", "salesTime", "SalesTRANSACTIONID", "SalesCURRENCY", "SalesDATE" FROM "Tool2True_cf2b") SELECT TOP 50 * FROM "Tool4_d853"

When you structure this better, it is much easier to understand. You can see the WITH block up top that sets up the assets; and the query below that returns the data

//This sets up a bunch of temporary structures that are used as building blocks
WITH
"Tool1_4eb8" AS
( SELECT
*
FROM
Sales),
"Tool2True_cf2b" AS
( SELECT
*
FROM
"Tool1_4eb8"
WHERE
"SALESDATE" > '01 apr 2017'),
"Tool4_d853" AS
( SELECT
"StoreCode",
"salesTime",
"SalesTRANSACTIONID",
"SalesCURRENCY",
"SalesDATE"
FROM
"Tool2True_cf2b")

//This is the part that returns the data to the output
SELECT
TOP 50 *
FROM
"Tool4_d853"

[GeoffColes]

Hi, Has anyone got an update on this issue. It's happening a lot with multiple queries run against sql server databases. The queries work fine from other applications. It's only alteryx that complains with this error. It's quite frustrating.

[CaptainWill]

I'm having the same issue.  When I try using multiple "Connect In-DB" tools to load and join multiple tables, It shows an error.  The queries and joins work fine when i use the "Input Data" tool so I know it is not a problem with the SQL code.  Its a lot of records so I would prefer to use the In-DB tool.

[JessicaS]

HI @CaptainWill,

Can you paste your query here?  Could you also provide a screenshot of how it looks pasted into the connect indb tool?

In particular, I'm curious if you have the query formatted with new lines and white space.

Thanks!