Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
teasley
Frequent Visitor

Get data from SQL Server, is database really optional?

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.

 

bffca576-56e5-4d0c-bf24-7846a1cf7df6.png

 

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

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.

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'

 

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors