-2

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"

3
  • 4
    Would you care to at least format your question so it's readable? It'll help people who might be willing to help you. Commented Nov 13 at 20:08
  • Why do you need OPENROWSET anyway, why not just use four-part naming with a linked server? Commented Nov 15 at 18:28
  • Was out of necessity. Remote entity would not allow linked servers. Thanks Commented Nov 16 at 19:03

1 Answer 1

3

it's different in dynamic

You've got two sections of code that have single quotes in them. When you're working with dynamic SQL, you need to use two single quotes to maintain the contiguous string.

('CALL CUSTOMER', 'COLLECTED A PAYMENT', 'CALLED')

('CHK IN MAIL', 'CUSTOMER IRATE/HANG UP', 'MADE CC PMT', 'PROMISE TO PAY', 'REFUSE TO PAY', 'SENDING $')

Should be

(''CALL CUSTOMER'', ''COLLECTED A PAYMENT'', ''CALLED'')

(''CHK IN MAIL'', ''CUSTOMER IRATE/HANG UP'', ''MADE CC PMT'', ''PROMISE TO PAY'', ''REFUSE TO PAY'', ''SENDING $'')

Perhaps with some code formatting this would have been more obvious to you.

1
  • Duh. I knew a second set of eyes would see it. Thanks a zillion. Commented Nov 14 at 15:29

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.