SQL Query that includes databases from multiple servers

Currently this is in ASP Classic:

My query as it stands at the moment:

SELECT     
    i.CONCOM
,   COALESCE(SUM(CASE WHEN C.CATEGORY_ID = '30' THEN 0 ELSE t.LOGMINS END), 0) AS TotalWithoutNew
,   COALESCE(SUM(t.LOGMINS), 0) AS TotalAllId 
FROM         
    INQUIRY AS i 
    INNER JOIN 
        TIMELOG AS t 
        ON t.INQUIRY_ID = i.INQUIRY_ID 
    INNER JOIN 
        PROD AS P 
        ON i.PROD_ID = P.PROD_ID 
    INNER JOIN 
        CATEGORY AS C 
        ON P.CATEGORY_ID = C.CATEGORY_ID 
WHERE 
    " & sqlwhereclause & " 
GROUP BY 
    i.CONCOM 
ORDER BY 
    totalwithoutnew desc"

The database I need to connect to, the string that works to connect

DatabaseConnectionDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessDBLocation & "data.mdb;Jet OLEDB:Database Password=redacted;"

There is a table called tbl_customer in the access database that has two fields company and priority. I need to do a join on the i.concom and company so it pu开发者_运维技巧lls the priority from the second database.

I am not going to lie this is well beyond me and I'd appreciate any help :)

I'd suggest a couple of different approaches on this.

Gold

Replicate the tbl_Customer data into the database that has everything else. I like this this the best because hitting this Access database with any frequency is bound to conflict with whatever front end process that writes the customer data into the database. The challenge here is to define an appropriate refresh rate (weekly, daily, hourly) such that your app is kept up to date but you aren't impacting Access.

Implementation would either be a scheduled pull, like an SSIS package or a push from Access via something like a macro associated to a save event in the customer table.

Silver

This solution would put the cached values into an ASP dataset object. When you execute the query, which I'd convert to a stored procedure, I'd pass the dataset in to the proc via a user defined table type and then you can join against that table in your query.

Bronze

The last option I'd throw out would be to query Access every time for the data and then load it into a derived table that you pass into your query. It ain't pretty and you'll have to roll your own sanity checking to escape tick marks, ', when you build the query, something like this

DECLARE
    @query varchar(max)
,   @dyanmic_where varchar(max)
SELECT @query = N'
SELECT     
    i.CONCOM
,   COALESCE(SUM(CASE WHEN C.CATEGORY_ID = ''30'' THEN 0 ELSE t.LOGMINS END), 0) AS TotalWithoutNew
,   COALESCE(SUM(t.LOGMINS), 0) AS TotalAllId 
FROM         
    INQUIRY AS i 
    INNER JOIN 
        TIMELOG AS t 
        ON t.INQUIRY_ID = i.INQUIRY_ID 
    INNER JOIN 
        PROD AS P 
        ON i.PROD_ID = P.PROD_ID 
    INNER JOIN 
        CATEGORY AS C 
        ON P.CATEGORY_ID = C.CATEGORY_ID 
    INNER JOIN
        (
            -- this gets built dynamically based on retrieved values
            SELECT 1, ''hello kitty'', 0
            UNION ALL 2, '''', 3
        ) D (concom, company, priority)
        ON D.concom = i.CONCOM
WHERE 
    '+ @dyanmic_where + N'
GROUP BY 
    i.CONCOM 
ORDER BY 
    totalwithoutnew desc
'

EXECUTE(@query)

If any of those tickle your fancy, let me know and I'll be happy to provide more details