cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jorn
New Member

Service not refreshing/loading a specific field

Hi everyone,

 

I want to create an overview of all views and stored procedures in PowerBI with the query below. This works fine in Power BI desktop, but when you refresh the data in Power BI service, the definition field becomes empty. It's confusing to have different behaviour between desktop and service. Any idea on how to resolve this issue? 

 

Possible root-cause: The "definition" field is very large (within 1 record it will have multiple rows and many characters). But in most things I read it should just be truncated.

 

SELECT definition
FROM sys.sql_modules

 

Many thanks in advance!

1 ACCEPTED SOLUTION

I think @GilbertQ  is on to something regarding permissions.


To see definitions in sys.sql_modules a user needs to be granted VIEW DEFINITION in sql server. In your case you'd want to do that at schema level at least for the account running the gateway.

https://docs.microsoft.com/en-us/sql/relational-databases/security/metadata-visibility-configuration...

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

4 REPLIES 4
GilbertQ
Super User
Super User

Hi @jorn 


Can you ensure that you have clicked on the reset to default button on your report page?

 

GilbertQ_0-1642541694058.png

 

If that still does not work, what happens if you have to upload the PBIX to a new app workspace without refreshing can you see all the information?

 

If you can then make sure that your gateway account connecting to your database has the right permissions to view the sys.modules?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi GilbertQ,
Thanks for your reply!

1. Reset to defaults: It was even greyed out, so nothing to reset

2. Upload without refresh: The data is shown.

2a. Gateway access to sys.modules: All other fields/colums are refreshed except that specific ("definition") field/column so it doesn't seem access related? I will try with my own user in the data gateway to see if that solves the problem.

I think @GilbertQ  is on to something regarding permissions.


To see definitions in sys.sql_modules a user needs to be granted VIEW DEFINITION in sql server. In your case you'd want to do that at schema level at least for the account running the gateway.

https://docs.microsoft.com/en-us/sql/relational-databases/security/metadata-visibility-configuration...

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

That's it indeed!
In my case I went to the properties of the SQL Server user >> Securables >> Permission >> "View any definition".

Thanks bcdobbs and GilbertQ!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors