Solved

Connecting to multple databases on the same SQL Server

  • 20 April 2023
  • 6 replies
  • 78 views

Userlevel 3
Badge +1

Hi,

Is it possible to connect to several databases that are on the same SQL server with one data source connection?

We have tried to connect to multiple databases on the same SQL Server using the provider “TimeXtender SQL Data Source 17.0.1.0”.

We tried leaving the Database parameter blank, putting in *, and writing a comma-separated list of databases, but neither way works. 

Has anyone had any success doing this with this connector or another?

We’re using version 6221.1.

Thanks!

icon

Best answer by bas.hopstaken 21 April 2023, 08:51

View original

6 replies

Userlevel 3
Badge +4

Hi @pontus.berglund ,

As far as I know this it not possible. 

Userlevel 3
Badge +1

Hi @bas.hopstaken,

I was assuming that could be the case.

@Christian Hauggaard, should I make a post about this in the ideas section, or could you move this post there? Let me know what you think is best.

Userlevel 5
Badge +7

If you are using a single SQL instance to host all databases you could consider adding views in one database that combines all together. Are you wanting to combine them because they are the same structure split across different databases? Then you do the combination after the ODX in your DWH instance. That is what mapping sets are for.

If the structures of the databases are different, then I would wonder why you would want to treat them as one entity.

Badge

Hi @pontus.berglund,

Can you clarify why you would want to use multiple databases within the same connection?

Thanks,

//Alex

 

Userlevel 3
Badge +1

Hi @rory.smith and @AvanWijnen,

We do indeed have a SQL Server data source with around 10 databases with the same structure, tables, views etc. I think it would be “cleaner” to have the same data source for all of these. Now we have 10 similar data sources, which was albeit not much work to create thanks to the copy function in the portal.
As you suggested we will map the common tables together in the DW instance.

Thanks!

Badge

That's exactly what the copy function is for @pontus.berglund 😉.
I also want to highlight the possibility to map multiple tables at once (see video).

 

Reply