Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

Microsoft SQL server flow breaks once published

charter747
7 - Meteor

Hello. I have an Alteryx flow that works on my laptop but breaks once published. I'm thinking the ODBC driver on Alteryx server is different than the driver on my laptop? How can I find out the various ODBC drivers that are on our Alteryx Server. We're using Alteryx server version 2021.2.1.35394

 

Also, any other ideas on what may be causing my problems.

 

Thanks in advance

6 REPLIES 6
Aguisande
15 - Aurora
15 - Aurora

Hi @charter747 

Can you provide the error shown when the WF breaks?

Maybe the ODBC connection has a different name too.

charter747
7 - Meteor

The admin for our Alteryx Server confirmed that the server has the "ODBC Driver 17 for SQL Server" driver installed in 32-bit and 64-bit

 

This is the complete connection string that Alteryx has in the output tool:

 

 

aka:BI_server|||BI.[\FAA Michael].alteryx_sp_test

 

 

This is how I query the table I'm trying to output to in Toad so I know the table exists and I have permissions to write to it:

 

select *
from BI.[FAA\Michael].alteryx_sp_test;

 

This is the error:

 

Info: Output Data (44): Alias translated to odbc:DRIVER={ODBC Driver 17 for SQL Server};UID=BIShare;PWD=__EncPwd1__;DATABASE=BI;SERVER=iamcdcdvdb380.ad.faa.gov|||BI.[\FAA Michael].alteryx_sp_test
Info: Output Data (44): ODBC Driver version: 03.80
Error: Output Data (44): Error running TRUNCATE table "BI.[\FAA Michael].alteryx_sp_test": [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find the object "alteryx_sp_test" because it does not exist or you do not have permissions.

Aguisande
15 - Aurora
15 - Aurora

Are you using the same credentials on both Designer & Server? 

I don't think that's driver related, looks more like a permission's issue

Aguisande_0-1648151166095.png

 

charter747
7 - Meteor

I've decided to step away from the ODBC connection method and use the Quick Connect option. I'm thinking ODBC will not work since the name of the datasource on the server cannot be defined. The Quick Connect only asks for server name and credentials. It should work more easily using Quick Connect.

 

But now the problem is I cannot get the flow to work on my laptop in Alteryx. I'm getting the error message above. Why is it not finding that table?

Aguisande
15 - Aurora
15 - Aurora

Is the table present when you connect via Management Studio with the same credentials you're using in the WF?

If so, and you can see and access the table, try adding dbo. before alteryx_sp_test ("BI.[\FAA Michael].dbo.alteryx_sp_test")

For some reason, when I connect to my local SQL server I need to add that

charter747
7 - Meteor

Yes, the table is visible within Toad. I tried adding dbo. and that still did not work. I've decided to attempt a DNS-less connection as described here:  Solved: DSN-less connection string issue - Alteryx Community

 

This is how the table shows up in Toad. The \FAA is my username for windows authentication. I don't know why it shows up like that; I think it has something to do with the way the DBA set-up my account.

 

charter747_0-1648243050636.png

 

This is the connection string inserted into the Alteryx output tool:

odbc:DRIVER={ODBC Driver 17 for SQL Server};UID=uid;PWD=pwd;DATABASE=BI;SERVER=server|||[BI].[FAA\Michael].[alteryx_sp_test]

 

When I run the Alteryx flow now on my laptop, I get 

Error: Output Data (48): An unknown error occurred in II_PushRecord.