DB Query result is different to Power Query result and SQL Server Management Studio

[KosieS]

I need to compare results between 2 Databases with exact same table layout.
Microsoft SQL Server 1 with Database 1 and Table1 and then Microsoft SQL Server 2 with Database 1 and Table 1. Linked to the is a DB Query reader with the same SQL Query. When I run the results of Query for MS SQL Server2 is same as for MS SQL Server 1, but they should be different.

What can I check to make sure thye are referring to different databases?

[ScottF]

Hi @KosieS and welcome to the forum.

Typically you would check the configuration of your Connector nodes to verify this. Can you upload your workflow (or at least a screenshot of it) so others can have a better picture of what you’re trying to do?

[takbb]

Hi @KosieS , I guess you could run a query such as this against both databases, to see that they return different results:

SELECT 
    SERVERPROPERTY('MachineName') AS HostName,
    (
        SELECT 
            local_net_address 
        FROM 
            sys.dm_exec_connections 
        WHERE 
            session_id = @@SPID
    ) AS IPAddress,
    DB_NAME() AS DatabaseName

---

Additionally, on the MS Sql Server Connector node, you can view the DB Session

• in Classic UI / KNIME AP 4.x, right click the connector node and then choose DB Session from the bottom of the popup menu

• in Modern UI / KNIME AP 5.x, highlight the connector node, and then in the data output pane at the bottom of the screen, select DB Connection and then click “Open Legacy port view”, or in later 5.x versions you can right click the node and then choose "open output port and under DB Session select “Open Legacy port view”)

This should show the database jdbc url and current database user.

[KosieS]

@takbb

SELECT 
    SERVERPROPERTY('MachineName') AS HostName,
    (
        SELECT 
            local_net_address 
        FROM 
            sys.dm_exec_connections 
        WHERE 
            session_id = @@SPID
    ) AS IPAddress,
    DB_NAME() AS DatabaseName

Thank you for code I manage to ascertain that the data results is valid and that the databases are indeed different.