Forum Discussion

shanjaf's avatar
shanjaf
New Member
10 years ago

how to schedule refresh for the dataset using on-prem sql database with option 'Import'

Hi All, how to schedule refresh for the dataset using on-prem sql database with option 'Import' instead of Direct query. Can some one guide me how to schedule when we use 'Import' data.
Need Help
THEG72's avatar
THEG72
Helper V
8 years ago

Hey Anonymous shanjaf 

 

Just checking if this advice in this post is still current.

 

I have an On Prem SQL data source and using the IMPORT option to gather data through a stored procedure as an SQL statement in the SQL Server Database dialogue box

 

Here is my statement

exec('[spReceivableSummaryReport] @ShipmentId = 294')

 

The data returns fine however i am having an issue with the refresh once i pubish to service.

 

I have an enterprise gateway installed and working and have added the SQL data source.

 

I setup the new connection in the Manage Gateway section of he service and it tests fine for the SQL source but when i go to the dataset to configure the refresh it states "you dont have any gateway installed or configured for the data sources in this dataset. Please install a new data gateway or configure the data source for an existing data gateway."

 

The Gateway connection Data Source credentials and Parameter are all greyed out ? 

 

Is there any common issues you have come accross that could shed some light on the issue? or is calling a stored procedure and trying to refresh in the service require a special setup?

 

I am running version 14.16.6670.1 April 2018 of the gateway software.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

THEG72 It should work especially if you were the one that set up the Gateway. Did you remember to add the user that is publishing the report to the dataset you created on the gateway? Also, ensure that the connection Server/database name that you are using in the PBIX file matches exactly to what you set up as a datasource in the gateway.

  • THEG72's avatar
    THEG72
    Helper V
    8 years ago

    Hi Anonymous


    Thanks for your advice it was spot on. One of my queries used local host instead of the server name like the other queries...given i didnt have localsource as a server name in the managed gateway section it greyed out the Refresh options.

     

    Can you have localhost as a server name when setting up a gateway? Is it better practice to put the name in or not if localhost works?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      THEG72 Avoid the "Localhost", it will just cause problems (like this use case). Even if it would work, it would require someone else to know what server it is if it did work.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        HI Anonymous

         

        You do know this is the "Desktop" forum, don't you?