Forum Discussion
Connecting to two different Azure SQ databases
1] If I need to get data from mulitple databases using DirectQuery, what are my options?
Have the connections on the database back end. Write a view that pulls the information from the other database.
2] Since I cannot mix DirectQuery and getting data from Excel, is there any other way to get data from other files when using DirectQuery mode? Not to my knowledge, you would need a way to create a database object Procedure/Function/View that could reference the external object. I was just thinking of using OPENROWSET(), but doesn't work in Azure SQL.
1] If I need to get data from mulitple databases using DirectQuery, what are my options?
Have the connections on the database back end. Write a view that pulls the information from the other database.
2] Since I cannot mix DirectQuery and getting data from Excel, is there any other way to get data from other files when using DirectQuery mode? Not to my knowledge, you would need a way to create a database object Procedure/Function/View that could reference the external object. I was just thinking of using OPENROWSET(), but doesn't work in Azure SQL.
Hi Anonymous,
Thank you for your suggestions.
Though not ideal, I think as you sugested I may be able to achieve some of the functionality of mixing database data and file data using Transact-SQL Rowset functions in the databse backend. I'd probably need to read a lot to understand how it can be done but atleast your answer gave me some direction.
I was also thinking of Azure Factory to load file data.
But it would have made it very appealing for small businesses if data could be loaded from multiple databases and files into a single PBIX file/project in DirectQuery mode.
Thanks,
Anand
