Forum Discussion
Get data from SQL Server, is database really optional?
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.