I've got two queries i need to run via remote server connection. They run fine if I run them by connect to the remote server FIRST, but if i use the openrecordset to run it from the command line, and another server, i get a syntax error ner 'CALL;
Here is the query running directly on the remote server:
SELECT
SUM(CASE WHEN la.status_code_no = 0 AND la.current_payoff_balance > 0 THEN 1 ELSE 0 END) total_num_of_loans,
SUM(la.current_principal_balance) as total_amount_of_portfolio,
SUM(CASE WHEN la.days_past_due > 60 THEN 1 ELSE 0 END) as total_num_of_default_loans
FROM loanacct la (NOLOCK)
GO
WITH Collected as (SELECT nu.userno as CollectorID,
nu.username as CollectorName,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) THEN 1 ELSE 0 END) as NumberOfCallsMadeToday,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) AND ca.action_code IN ('CALL CUSTOMER', 'COLLECTED A PAYMENT', 'CALLED') AND cr.result_code IN ('CHK IN MAIL', 'CUSTOMER IRATE/HANG UP', 'MADE CC PMT', 'PROMISE TO PAY', 'REFUSE TO PAY', 'SENDING $')
THEN 1 ELSE 0 END) as NumberOfConnectedCallsToday,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) THEN amount_received ELSE 0 END) as AmountCollectedToday,
SUM(CASE WHEN CAST(lc.created as DATE) >= DATEADD(dd, -7, CAST(Getdate() as DATE)) THEN amount_received ELSE 0 END) as AmountCollectedInLastWeek,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) AND ISNULL(amount_received, 0) > 0 THEN 1 ELSE 0 END) as CollectedCallToday
FROM loanacct_collections lc (NOLOCK)
INNER JOIN nlsusers nu (NOLOCK) on nu.userno = lc.created_by
INNER JOIN collection_action_codes ca (NOLOCK) on ca.action_code_no = lc.action_code_no
INNER JOIN collection_result_codes cr (NOLOCK) on cr.result_code_no = lc.result_code_no
LEFT JOIN loanacct_collections_ptp lcp (NOLOCK) on lcp.loanacct_collections_row_id = lc.row_id
GROUP BY nu.userno, nu.username
)
SELECT CollectorID,
CollectorName,
AmountCollectedInLastWeek,
CASE WHEN CollectedCallToday > 0 AND NumberOfCallsMadeToday > 0 THEN CollectedCallToday / NumberOfCallsMadeToday ELSE 0 END as DailyColectionRatePerCall,
NumberOfCallsMadeToday,
NumberOfConnectedCallsToday,
AmountCollectedToday
FROM Collected
GO
Here is the same query running via openrecordset:
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=xxxxxxx.com;UID=uuuuuuu;PWD=pppppp;',
'SELECT
SUM(CASE WHEN la.status_code_no = 0 AND la.current_payoff_balance > 0 THEN 1 ELSE 0 END) total_num_of_loans,
SUM(la.current_principal_balance) as total_amount_of_portfolio,
SUM(CASE WHEN la.days_past_due > 60 THEN 1 ELSE 0 END) as total_num_of_default_loans
FROM loanacct la (NOLOCK)')
GO
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=xxxxxxx.com;UID=uuuuuuu;PWD=pppppp;',
'WITH Collected as (SELECT nu.userno as CollectorID,
nu.username as CollectorName,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) THEN 1 ELSE 0 END) as NumberOfCallsMadeToday,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) AND ca.action_code IN ('CALL CUSTOMER', 'COLLECTED A PAYMENT', 'CALLED') AND cr.result_code IN ('CHK IN MAIL', 'CUSTOMER IRATE/HANG UP', 'MADE CC PMT', 'PROMISE TO PAY', 'REFUSE TO PAY', 'SENDING $')
THEN 1 ELSE 0 END) as NumberOfConnectedCallsToday,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) THEN amount_received ELSE 0 END) as AmountCollectedToday,
SUM(CASE WHEN CAST(lc.created as DATE) >= DATEADD(dd, -7, CAST(Getdate() as DATE)) THEN amount_received ELSE 0 END) as AmountCollectedInLastWeek,
SUM(CASE WHEN CAST(lc.created as DATE) = CAST(Getdate() as DATE) AND ISNULL(amount_received, 0) > 0 THEN 1 ELSE 0 END) as CollectedCallToday
FROM loanacct_collections lc (NOLOCK)
INNER JOIN nlsusers nu (NOLOCK) on nu.userno = lc.created_by
INNER JOIN collection_action_codes ca (NOLOCK) on ca.action_code_no = lc.action_code_no
INNER JOIN collection_result_codes cr (NOLOCK) on cr.result_code_no = lc.result_code_no
LEFT JOIN loanacct_collections_ptp lcp (NOLOCK) on lcp.loanacct_collections_row_id = lc.row_id
GROUP BY nu.userno, nu.username
)
SELECT CollectorID,
CollectorName,
AmountCollectedInLastWeek,
CASE WHEN CollectedCallToday > 0 AND NumberOfCallsMadeToday > 0 THEN CollectedCallToday / NumberOfCallsMadeToday ELSE 0 END as DailyColectionRatePerCall,
NumberOfCallsMadeToday,
NumberOfConnectedCallsToday,
AmountCollectedToday
FROM Collected
')
GO
The error occurs on the second query near "CALL"
OPENROWSETanyway, why not just use four-part naming with a linked server?