Connect to multiple tables from multiple databases in SQL

[vishnu_2920]

Hi all,

I need to Build an interface to accept an UserID (Username) and able to display all the roles the user have on all the databases

Note:- There are multiple database Servers and there are multiple databases on each server.  Each database i need to use two tables.

How to  connect to all the databases on one MS SQL server using Alteryx.

Thanks

Sudeep.

[geraldo]

@vishnu_2920 

The first step is to verify the connections are created in Alteryx or ODBC.
List the names that were created.
The way we can build something to meet your need is through macro
that will receive data from the connection to the databases and tables that will be needed for the queries
If you provide more information, we can create a wm workflow for this.

An example de Saved Connections InOut

Through this DSN I will be able to make macro substitutions in Input data, dynamic input and Connect In_Db with the ones specially created for them

[Raj]

To connect to all the databases on one MS SQL server using Alteryx, you can follow these steps:

1. Open Alteryx Designer and click on the "Connections" tab in the left panel.

2. Click on the "Database" tab and select "Microsoft SQL Server" as the database type.

3. In the connection settings, enter the server name and port number of the MS SQL server that you want to connect to.

4. Select "Windows Authentication" or "SQL Server Authentication" depending on how you want to authenticate to the server.

5. If using SQL Server Authentication, enter your username and password.

6. Click on the "Test Connection" button to verify that the connection is working.

7. Once the connection is established, you can use the "Input Data" tool to select the databases and tables you want to query.

8. In the "Input Data" tool, select "Database" as the data source and choose the MS SQL Server connection you just created.

9. In the "SQL Statement" field, enter the SQL query to retrieve the user roles on all the databases. This will involve joining the system tables on each database to retrieve the user roles. The exact query will depend on the version of MS SQL Server you are using and the system tables you need to query.

10. Once you have entered the SQL query, click on the "Run" button to execute it. The results will be displayed in the Alteryx output window.

11. You can then use the "Output Data" tool to save the results to a file or database.

[SusanRenown]

I've set up the tables to query and the query runs, but it's very slow. It seems to be returning all the rows of the necessary tables, then running my actual query. Is this correct? Can we optimize this?

Thank you