Temporary scheduled refresh fails after database update in Postgres

[zenisekd]

Hi, 

For quite some time I keep experiencing unpleasant temporary refresh fails (on the import model), which mostly occur when my colleagues do some database adjustment (add a new column to a table in the database, etc.). 

These adjustments do not cause any specific errors, which would require fixing in the desktop. It just causes that after the Postgres database (located on AWS server) adjustment, the scheduled refresh in Service fails a couple of times. Then it catches up again and works. 

This drives me crazy because adjustments are common and my customer requires the report to be updated every 6 hours, so I get requests for manual updates commonly. 

The error is caused by various tables (usually the largest ones, saying an error -2147467259, which gives no good explanation on what to do with it. I was unable to identify, how to fix/prevent this. Could anybody tell? Some time ago I tried to talk to the MS support, but it was useless.... I guess that it could be fighting with some caches and that the fix needs to be done on the database side, or somewhere outside the desktop...

Any advice would be greatly appreciated.

The errors I can see in the service are:

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public product_public_category.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: An error happened while reading data from the provider: '[ccon]Connection is not open[/ccon]'. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: BOM detail level.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public sale_order_line.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public sale_order_line.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public account_invoice.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public account_invoice.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: An error happened while reading data from the provider: '[ccon]Connection is not open[/ccon]'. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public product_template.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: public sale_order.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public sale_order_line.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: An error happened while reading data from the provider: '[ccon]Connection is not open[/ccon]'. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: helpdesk tags.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while reading from stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. Message = [ccon]Exception while reading from stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public account_invoice.

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: An error happened while reading data from the provider: '[ccon]Connection is not open[/ccon]'. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: public crm_team.

[Anonymous]

Hi @zenisekd ,

Changes in data source table structure, or schema, such as a new, renamed, or removed column can only be applied in Power BI Desktop, and in the Power BI service they can cause the refresh to fail. For a quick reference on what they impact, refer to the following table.

For more details, you could read related document: Data refresh in Power BI - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zenisekd]

Hi @Anonymous,

I don't think your comment is touching my issue at all. The changes happen on the database side, but they don't influence the structure of my model created in desktop and published. In desktop I am carefully selecting the columns that I work with and remove the other columns (in which might be a change). If they changed the name of a column or perhaps the type of it, it would cause and error, which would be obvious and I would have to go to Desktop and adjust the queries. 

BUT THIS IS NOT THE CASE. The error ( ErrorCode = 2147467259 or "The exception was raised by the IDbCommand interface") , the service is giving me, tells me nothing that I can grab on. All I have to do is wait and hope that the refresh will go through and ask for on-demand refresh. And eventually, it catches up, just like in the current case. 

I am just frustrated that nobody is able to help. We have had this problem for over a year now. 

Will anybody be able to help me?