Connect to multiple tables on the same SQL server using data gateway

[Anonymous]

Apologies if this has been asked before, I am trying to connect to multiple tables on the same SQL server.

My report contains two queries, both referencing two different tables on the same server. THese run fine both within SQL and in the report.

I'm trying to set up the report for a scheduled refresh using the data gateway, however I can't get it to connect. This worked fine when I was just referencing the one table but it won't connect to the second table. I've tried adding it in Manage Gateways with no luck.

What am I doing wrong? Thank you.

[smarthp29]

@Anonymous Would you be able to share screenshots? Hide the server and any other confidential information with a black marker.

[tctrout]

You 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.

[Anonymous]

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