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

[mpurdy]

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.

[jdunkerley79 · Accepted solution]

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

I think that should work.

[mpurdy]

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]

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 Designer

Best,

Tyler Garrett

LinkedIn

[apozzy]

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]

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. 

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

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.

Success, generated 3 new tables.

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]

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]

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 ?