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 Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Output Data - How to specify Schema and Output Table Name for SQL Server Database

mpurdy
8 - Asteroid

I am trying to append data to an SQL Server Database table. I have set the Output Data tool up, established a connection to the database and it has prompted me for the table name (which I have entered) but when I try running my job it errors: "Error: Output Data (13): Error opening query: Microsoft OLE DB Provider for SQL Server: Invalid object name 'Test'.\42S02 = 208". I think it has something to do with the schema (the table is not in the default schema) and so it cannot find it. Can you specify the schema anywhere? Or am I just doing something completely wrong? It has worked for me before but the table was in the "dbo" schema so that is why I am assuming this is causing issues. Thanks in advance.

7 REPLIES 7
jdunkerley79
ACE Emeritus
ACE Emeritus

Have you tried useing [Schema].[TableName] for the table name to push to.

 

I think that should work.

mpurdy
8 - Asteroid

Thanks for the feedback. I thought I had tried that without success but I have gone back and it has worked (maybe I was missing the square brackets?). Sorry for the silly question. Thanks again!

TylerG
Alteryx Alumni (Retired)

Hello, here's what worked and also what did not work. 

 

  1. \SQLEXPRESS|||[Sandbox].[Test1] 
  2. \SQLEXPRESS|||[Sandbox].[dbo].Test2
  3. \SQLEXPRESS|||Sandbox.dbo.Test3

 

#1 Failed. (brackets, w/o DBO)

#2 Passed. (mostly brackets, w/ DBO)

#3 Passed. (no brackets, w/ DBO)

 

Output to SQL Server with Alteryx DesignerOutput to SQL Server with Alteryx Designer

Best,

Tyler Garrett

LinkedIn

apozzy
7 - Meteor

Does anyone know how this can be done dynamically? We need to be able to move our workflows between dev/uat/production environments without anyone having to touch the workflows. So the db/schema/connection info needs to change to point to the correct environment by being updated dynamically. We want to use relative paths to store config files which would contain the connection info for each environment, and have Alteryx read the config and update the output tool with the correct info on the fly.

 

Can this be done? If so, can anyone help?

 

thanks!

Alex

TylerG
Alteryx Alumni (Retired)

Yes, it's possible to dynamically output SQL tables. There are likely several ways to accomplish your request - here's an example.

 

I use a control parameter and an action, to handle the request. Below are a few screenshots - to help you quickly work through your use case. 

2018-05-23 11_19_43-Alteryx Designer x64 - Macro.yxmc.png

 

 

Here's the settings to change a value in the output.

2018-05-23 11_21_03-Alteryx Designer x64 - Macro.yxmc.png

 

1. Update value (drop down)

2. Select the "file - value" (in value or attribute to update:)

3. Check Replace a specific string (on the bottom)

 

We are replacing 1, with numbers.

2018-05-23 11_25_53-Alteryx Designer x64 - New Workflow2_.png

 

Success, generated 3 new tables.

2018-05-23 11_36_42-Microsoft SQL Server Management Studio.png

 

Here's other things we could change in the connection string:

 

aka:DL-LT-003\SQLEXPRESS|||sandbox.dbo.1table_test

  • DL-LT-003
  • SQLEXPRESS
  • Sandbox
  • 1table

Or any granularity... Like we replaced a 1 in the table name, and I generated the above macro in a few minutes.

 

Maybe you do PROD_, DEV_, TEST_, aliases? You can swap and automate utilizing the above method.

 

One more thing... because your input can carry any values, you can dynamically change, based on previous data streams.

 

This is incredibly cool, I've never even thought about this before, nice work! Thanks for bringing the use case to the community.

 

Best,

Tyler Garrett

FreeRangeDingo
11 - Bolide
11 - Bolide

I was trying to work thru an output tool that wouldn't write to SQL, and this thread was helpful.  To add to the information.....

 

In my use case, I was using an aliased Gallery data source.  This is what worked for me -- aka:GalleryAliasName|||DatabaseName.dbo.OutputTableName.

 

No brackets were required.

paulhet12123131
6 - Meteoroid

Hi, 

 

I am having a similar issue. Basically I try to read data from an SQL server table and to put it in an Azure Sql server table. 

 

The flow does the following: 

1: Connects to a SQL Server and reads data

2: Load data into another table , under an Azure SQL Server DW.

 

The issue appears when I try to load the data. For some reason the same select statement from step 1 is taken into account in step 2 , where the Table structure is totally different. 

 

I have attached a print-screen with the data-flow, where the Output Data object properties are visible. 

 

Could you please advice what am I doing wrong ?

Labels