Get data from SQL Server, is database really optional?

[teasley]

I have a query that includes multiple databases within the same SQL server. My query uses the database prefix for each table name. In PBI desktop, when connecting to SQL server, the interface states database is optional. But when trying to save, the error is "Please enter a value". When I specify a database name in the connection it works in PBI desktop but not when published to PBI service.

 

[Anonymous]

Hi @teasley ,

It sounds like you may need to specify the database name in your connection string in Power BI Service. To do this, you can go to the "Edit Queries" window in Power BI Desktop, click on "Data Source Settings" in the "Home" tab, and then click on "Change Source" for your SQL Server data source. In the "SQL Server database" field, enter the name of the database you want to connect to. Then, when you publish your report to Power BI Service, make sure to update the credentials and connection string to include the database name as well. You can do this by going to the "Data Sources" tab in the "Settings" menu of your report in Power BI Service.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[teasley]

Thank you for your reply but it does not solve the problem. The database is already specified in the connection string, I would like to remove the database from the connection string and let the query specify the database. The interface states that the database is optional, but it doesn't let me save without a database value.

My query (sample below) uses tables from two different databases on the same server.

In the query, I prefix each table name with the database name.

It seems that because the connection string contains the database name, I cannot query tables in a different database than the database specified in the connection string. When I try to remove the database from the connection string I get an error.

The query below will update from the desktop, but not from the PBI service.

SELECT T1.OPEN_FROM_DATE
,T2.BI_FISCAL_YEAR
FROM FMSPRD.dbo.PS_FIN_OPEN_PERIOD T1
INNER JOIN FMSETLPRD.dbo.BI_TIME_DIM T2 ON T1.OPEN_FROM_DATE = T2.BI_DATE
WHERE PSFT_PRODUCT = 'GL'
AND BUSINESS_UNIT = 'FHCRC'
AND TRANSACTION_TYPE = 'DEF'
AND LEDGER_GROUP = 'ACTUALS'

[teasley]

I see the problem now, when using an SQL query a database name is required. I must suggest this as a new feature request, the ability to specify a databse name in the query rather than the connection string so multiple databases can be queried.