'Run a query that uses both a Dataverse and a on-prem SQL table via SSMS

I want to run a query that needs to use a table from our Dataverse environment but joined to a local MSSQL table.

I can access both database in the same SSMS, but using them together is still a bridge to far for me it seems.

I've connected to the Dataverse environment in SSMS and then entered this query:

SELECT cr6e4_customeruser, count(cr6e4_assettag) as AssetCount
  FROM [dbo].[cr6e4_itassets]
  INNER JOIN [CDWSVR01].DataFactory.[dbo].[ActiveDirectoryUsersEnabled] AdUsers ON [cr6e4_itassets].cr6e4_customeruser = adusers.Email
  GROUP BY cr6e4_customeruser
  ORDER BY cr6e4_customeruser

But that gives this error message:

Msg 40000, Level 16, State 1, Line 1 Invalid server name “CDWSVR01”. Line:4, Position:14 RequestId: TDS;----------------------------;33 Time: 2022-01-26T13:10:10.5003734Z

Is this even possible? And if so, what am I missing?

sql-serverssmsdataverse


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions