Create Data Source from two different data connections

1

Hi Klaus

The simplest way would be to create view in the bps database that joins the data from both sources.
If the second database is not on the same server you have to create a linked server connection in to make it accessible

Hi,

If the databases are on the same sql instance, you can create a Union query by specifying the full name/path to the table (database name) eg [BPS_Content].[dbo].[WFElements]
Same principle if you want to use join.
If these are separate instances, it is required to connect the instances by creating linked servers.

It is important to choose not the default connection as the connection, but the connection created specifically for one of the databases. (But it also depends on what version of webcon you have)
(the full path [BPS_Content].[dbo].[WFElements] is then given to the second base in the union.)

Hi,

You can use 2 methods to create a LinkedServer. These methods are detailed below

1. Steps for creating Linked Server in SSMS for MYSQL:
• Open SSMS and connect to your SQL Server instance.
• In the Object Explorer, expand the "Server Objects" node, right-click on "Linked Servers," and select "New Linked Server."
• In the "New Linked Server" dialog box, provide the following information:
• Linked server: Give it a name (e.g., "MyMySQLLinkedServer").
• Server type: Choose "Other data source" from the drop-down.
• Provider: Choose "Microsoft OLE DB Provider for ODBC Drivers."
• Product name: Enter the name you used for the ODBC Data Source (set up in step 2).
• Data source: Enter the exact name of the ODBC Data Source configured for MySQL.
• Click on the "Security" tab and choose the appropriate authentication method (e.g., "Be made using this security context" and provide MySQL login credentials, or "Not be made" to use the current user's security context).

2.The second way to create a Linked Server is by executing the following script:

EXEC sp_addlinkedserver
@server = 'MyMySQLLinkedServer', -- Linked Server name
@srvproduct = '', -- Leave blank
@provider = 'MSDASQL', -- Provider for MySQL
@datasrc = 'MySQLDataSource', -- Name of the MySQL data source configured in ODBC
@provstr = 'DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=mysql_server_address;PORT=3306;DATABASE=mysql_database_name;USER=mysql_user;PASSWORD=mysql_password;OPTION=3';

-- Execute the query with INNER JOIN between the tables from both databases
SELECT *
FROM WfElements AS lt
INNER JOIN OPENQUERY(MyMySQLLinkedServer, 'SELECT * FROM remote_table') AS rt
ON lt.column_name = rt.column_name;

I have added two images with the steps to create a LinkedServer using SSMS.

Thanks,
Raluca

Thank to all three of you!

I noticed that I have already the servers linked and thus it was with your hints "a piece of cake".