I am having an issue with two reports that use different tables/queries from the same database on the same SQL server. I do not have the same issue with my other SQL Server. When I try and refresh the data in the published report I get the following error message:
I previously got this message but the table was Status. I renamed the PBI query Status to Status2. I then duplicated another query, copied the SQL query from Status2 and pasted it in to the new Query1. I then renamed Query1 to Status. I then saved and published the report. When I tried to refresh the dataset it gave me the error above. I checked the report and when I look at Data Sources there is only the one.
Can anyone provide more information on why this may be happening or why the fix I tried worked? I have a of queries in the report and changing them like I did for status means redoing all of my visualisations, measures and calculated columns which I am trying to avoid.
I have looked through the forums and as per suggestions elsewhere, rebooted the gateway server, removed the gateway all together and created a new one and checked the permissions are identical for both SQL servers as well.