I have a database that holds all of clients information on one server and on another server, a database for each client. I am trying to connect a table that exists in individual clients databases in a query within the database on the server that holds all clients information. The problem is I need the database name to be dynamic on the join depending on which client information is being pulled. is this possible?
`enter code here`from
Timeslips ts
inner join Invview IV on IV.InvoiceID=ts.InvoiceID
Inner join Customers C on C.CustomerID=TS.CustomerID
LEFT join ReceiptControl r on r.InvoiceID=IV.InvoiceID
LEFT join commissions c on c.ReceiptControlKey=r.ReceiptControlKey
LEFT join #temp T on T.invoiceID=TS.InvoiceID AND T.paymentdate=c.Date AND
T.damount=r.dAmount
LEFT join [172.20.2.8].(**what needs to be dynamic**).dbo.order_commission OC on OC.orderid Collate SQL_Latin1_General_CP1_CI_AS=ts.OrderID
LEFT join [172.20.2.8].(name needs to be dynamic).dbo.order_commission_type OCt on OCt.commissiontype Collate SQL_Latin1_General_CP1_CI_AS=OC.commissiontype
1条答案
按热度按时间r7xajy2e1#
The only way I could think to do this outside of dynamic sql would be to build a View that composed data from the tables for ALL clients via UNIONs, with an additional client code as the first field. Then you could filter on that field for the join... but it'd be hella slow, even if you only included the few minimum possible columns, and you'd have to update the view every time you gain or lose a client.