Forum Discussion
Connect to multiple tables on the same SQL server using data gateway
Responsive ResidentYou specified 2 different tables on the same server, but are the tables located within the same database?
If they are, then I dont have a solution for you.
If they are not, then it sounds like your gateway is configured to the database for the table it is working for but not to the other. You first need to ensure the datagateway lists your other database as a connection, and then correctly map it within the connection.
Hope this helps.
Hi tctrout ,
The two tables are on the same database, unfortunately. Do you know why I'm unable to connect to different tables on the same database? It seems a little odd to me that we're unable to do this! I would just connect to the whole database but I'm using custom SQL queries for both so I have to specify the table. I tried to connect to it within the same SQL query which worked in SQL but still threw an error in PBI.
Thank you very much for your reply!
- tctrout
This is how I would start to troubleshoot it.
1. Are the tables on the same Schema?
NO: Check your organization to see if different security permissions are on the different schemas.
2. Separate troubleshooting from PBIX to Online
A. PBIX: Does the PBIX refresh fine locally?
NO: Get rid of the embedded sql. Connect to tables using the Edit Queries GUI.
B: Online Manually: Does the solution refresh fine when you manually trigger the refresh?
NO: Focus on gateway like orginal reply. If configured correctly, next ensure your username is listed as a User on the gateway. This is necessary regardless if your account is listed as Gateway Admin.
C: Online Scheduled: If A & B work, does the scheduled refresh work?
NO: Similiar to B, but check to see if your organization usese a 'production' single account to do the deploys, schedule refreshes etc. If they do, then ensure this production account is listed as a User