Distributed Query

  • I'm running a store procedure from my SQL 2000 reporting server which calls my linked SQL 2000 production server. It contains a CASE statment:

    Territorycode= (case pb.territorycode

    when 1 then 'North/West Vancr'

    when 2 then 'Lower Mainland'

    when 3 then 'Maple Ridge/Pitt Meadows'

    when 4 then 'Squamish/Whistler'

    when 5 then 'Pemberton/Hope'

    when 6 then 'Fraser Valley'

    when 7 then 'Thompson/Okanagan'

    when 8 then 'Kootenays'

    when 9 then 'Cariboo'

    when 10 then 'Prince George'

    when 11 then 'North Coast'

    when 12 then 'Peace River'

    when 13 then 'South Vancouver Island & Other Islands'

    when 14 then 'Middle Vancouver Island/Sunshine Coast'

    when 15 then 'Northern Vancouver Island'

    when 16 then 'Canada/USA'

    when 27 then 'White Rock/Twassen'

    when 28 then 'Port Coquitlam'

    when 31 then 'Waterfront N/W Vancr'

    when 34 then 'Coquitlan'

    when 35 then 'Cloverdale'

    when 36 then 'Delta'

    when 38 then 'Langley/Aldergrove'

    when 40 then 'Ladner/Delta'

    when 42 then 'Richmond'

    when 45 then 'Port Moody/Coqutilam'

    when 47 then 'Van-East of Cambie/Buraby/New West'

    else 'Not defined in query' end)

    When run on the production server it runs fine, But when run as a distributed query I get the followin error.

    Server: Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'Qry1058'.

    Server: Msg 125, Level 15, State 1, Line 1

    Case expressions may only be nested to level 10.

    Is there a way to increase the nested level for the CASE expression in a distributed query?

    Thanks Greg

  • The error about nested to a level of 10 usually means you did something like this

    EX

    CASE colx

    WHEN 1 THEN CASE coly

    WHEN 1 THEN CASE colz

    .......Nesting down

    WHEN 2 THEN CASE cols

    WHEN 1 THEN CASE colb

    Can you post both code side for the local and the remote as I don't see Qry1058 referred to here. Need more info to help on this.

    Hard to

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • The code is below...Our business user is trying to run this from a server name MIS. It selects the data below from a linked server name POS. There is no Qry1058 just the Store procedure code below, it doesn't join or select anything on the MIS server just selects from the linked POS server.

    select distinct pe.name,policynumber, pb.vic,pb.versionnumber,pc.vehiclemake,pc.vehiclemodel,pc.modelyear,Amountdue,Netrate, commissionamount,transactiondate,pd.transactionid,

    Territorycode= (case pb.territorycode

    when 1 then 'North/West Vancr'

    when 2 then 'Lower Mainland'

    when 3 then 'Maple Ridge/Pitt Meadows'

    when 4 then 'Squamish/Whistler'

    when 5 then 'Pemberton/Hope'

    when 6 then 'Fraser Valley'

    when 7 then 'Thompson/Okanagan'

    when 8 then 'Kootenays'

    when 9 then 'Cariboo'

    when 10 then 'Prince George'

    when 11 then 'North Coast'

    when 12 then 'Peace River'

    when 13 then 'South Vancouver Island & Other Islands'

    when 14 then 'Middle Vancouver Island/Sunshine Coast'

    when 15 then 'Northern Vancouver Island'

    when 16 then 'Canada/USA'

    when 27 then 'White Rock/Twassen'

    when 28 then 'Port Coquitlam'

    when 31 then 'Waterfront N/W Vancr'

    when 34 then 'Coquitlan'

    when 35 then 'Cloverdale'

    when 36 then 'Delta'

    when 38 then 'Langley/Aldergrove'

    when 40 then 'Ladner/Delta'

    when 42 then 'Richmond'

    when 45 then 'Port Moody/Coqutilam'

    when 47 then 'Van-East of Cambie/Buraby/New West'

    else 'Not defined in query' end)

    from pos.cover.dbo.vic_lookup_codes pa

    inner join pos.cover.dbo.rate_group_vic_except pb

    on pa.vic = pb.vic

    and pa.versionnumber = pb.versionnumber

    inner join pos.cover.dbo.Autocover_headers pc

    on pb.vic = pc.vic

    and pb. modelyear = pc. modelyear

    and pb.territorycode = pc.territorycode

    inner join pos.cover.dbo.transactions pd

    on pc.transactionid = pd.transactionid

    inner join pos.cover.dbo.Branches pe

    on pe.branchid = pd.branchid

    inner join pos.cover.dbo.Payments Pf

    on pf.transactionid = pd.transactionid

    where pb.versionnumber = 1

    and pd.transactiondate >= '11-17-2001'

    and pd.transactiondate <= '01-31-2002'

    and statuscode = 1

    group by pe.name,pb.territorycode,policynumber,pb.vic,pb.versionnumber, pc.vehiclemake,pc.vehiclemodel,pc.modelyear,Amountdue,Netrate, commissionamount,transactiondate,pd.transactionid,transactioncode

  • I tested your case issue and find no problem with it. There has to be something on their end causing it. Do a hand query and post all that happens back here maybe there is some clue. Also you state there is no Qry1058 in the code for the distributed query but from this

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'Qry1058'.

    had to be somewhere in the query otherwise would not have spit back.

    Finally I am not sure that group by clause is buying you anything since you are doing a distinct and no sums, maxs, mins or any agregate function of the kind. Did you mean Order By maybe?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • This code was written by our business user, He has ask me(DBA) to trouble shoot the error. I believe his reasoning behind the group by is that if you use Order by without any of the items in the select distinct list you'll receive an error.

    I tried to run the procedure above on a different server and got the same error.

    Server: Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'Qry1058'.

    Server: Msg 125, Level 15, State 1, Line 1

    Case expressions may only be nested to level 10.

    When I double click on the error message it just takes me to the select line?

    I forgot to mention that the database that the code is calling is in 6.5 compatibilty mode. When I run it from query analyer on the POS server It runs fine. When run on any other machine that has this server linked I get the same error.

    Thanks

    Greg

    Edited by - gregtm on 05/02/2002 09:13:14 AM

  • I tried the same query with just 10 levels in the CASE statement and it works fine. When I add one more level to the the CASE statement I get the same error. Do you think this is due to the 6.5 compatibility?

    thanks

    GREG

  • Must be. But I try that and still have no issues. I will test some more tomorrow to see if I can get it to happen. May be something the way your table is built or the view? Not sure. If you can post the DDL of the table and view I can test here under that circumastance.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply