Statement could not be prepared

Question

I've got a SQL Server 2008 server set up with a linked server to a 2005 server. On the 08 server i've got a view set up that's pulling data from the '05 server.

This scenario works from my dev '08 server to the '05 server, but not from my report server to the 05 server. the '05 server has SP4 set up and the 08 servers have SP2 installed. Also, the specific database being queried on the '05 server is on version '08.

here's the view's definition:

    select top (100) percent
    qeb.QuoteId,
    qb.TotalAmount,
    qeb.Abc_ExpectedDateofDecision,
    .95 as prcnt,
    dbo.vwPROJ_TOTALS.Actual_Revenue * .95 as projectedvalue,
    qb.Name,
    dbo.vwPROJ_TOTALS.Nitems,
    dbo.vwPROJ_TOTALS.passthrough,
    dbo.vwPROJ_TOTALS.item_totals,
    dbo.vwPROJ_TOTALS.SL_DESC_L,
    dbo.vwPROJ_TOTALS.BU_DESC_L,
    dbo.vwPROJ_TOTALS.Actual_Revenue,
    qb.QuoteNumber,
    dbo.vwQuoteStatus.Value as QuoteStatus,
    qeb.ABC_QuoteStatus,
    dbo.vwQuoteType.Value as QuoteType,
    qeb.Abc_QuoteType,
    cb.FullName as ContactName,
    qeb.ABC_QuoteDeliveryDate as DeliveryDate,
    dbo.vwPROJ_TOTALS.ServLineCode,
    ab.Name as clientname,
    dbo.vwACCOUNT_MANAGERS.Value as AccountManager,
    case aeb.ABC_CompanyDesignation
      when 1 then 'pharmaceutical'
      when 2 then 'Chemical'
    end as Industry,
    qeb.ABC_QuoteDueDate,
    qb.CreatedOn as StartQuote,
    qeb.Abc_IsthisanFTEAgreement,
    qeb.Abc_EarliestClientReadyDateNEW,
    qeb.Abc_InternalProjectStartDate
from
    appdb.ABC_LABS_MSCRM.dbo.ContactBase cb
    inner join appdb.ABC_LABS_MSCRM.dbo.ContactQuotes cq
    inner join appdb.ABC_LABS_MSCRM.dbo.QuoteBase qb
        on cq.QuoteId = qb.QuoteId
        on cb.ContactId = cq.ContactId
    inner join appdb.ABC_LABS_MSCRM.dbo.AccountBase ab
        on qb.AccountId = ab.AccountId
    inner join appdb.ABC_LABS_MSCRM.dbo.AccountExtensionBase aeb
        on ab.AccountId = aeb.AccountId
    left outer join dbo.vwPROJ_TOTALS
        on qb.QuoteId = dbo.vwPROJ_TOTALS.crmQuoteID
    left outer join dbo.vwQuoteStatus
    inner join appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb
        on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus
    inner join dbo.vwQuoteType
        on qeb.Abc_QuoteType = dbo.vwQuoteType.AttributeValue
        on qb.QuoteId = qeb.QuoteId
    left outer join dbo.vwACCOUNT_MANAGERS
        on qeb.Abc_ABCAccountManagerId = dbo.vwACCOUNT_MANAGERS.Abc_AccountManagerId
where
    (qeb.Abc_QuoteType = 3)
    and (ABC_QuoteStatus in (1,2)
    or ABC_QuoteStatus is null
    )

the error i get on the report server:

Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_ABCAccountManagerId" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_EarliestClientReadyDateNEW" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_InternalProjectStartDate" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_IsthisanFTEAgreement" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_ABCAccountManagerId" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_QuoteType" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.ABC_QuoteStatus" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.ABC_QuoteDueDate" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.ABC_QuoteDeliveryDate" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.Abc_ExpectedDateofDecision" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1031.QuoteId" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1028.Value" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1009.ABC_CompanyDesignation" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1007.Name" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1005.CreatedOn" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1005.TotalAmount" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1005.Name" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1005.QuoteNumber" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1005.QuoteId" could not be bound.

i'm not sure what could be different between the two servers. i did a code compare again with redgate's sql compare and everything's the same.

edit: The curious thing though is that if i remove the where clause from the view it works fine.

Answer 1

There are a couple JOIN combinations that seem abnormal to me.

For example:

left outer join dbo.vwQuoteStatus 
     inner join appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

vs.

left outer join dbo.vwQuoteStatus 
                appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

Answer 2

Well, i got it working. i did some more digging and the view dbo.vwQuoteType was causing the issue for some reason. i cut it out of the view and replaced the field dbo.vwQuoteType.Value with some text (since this view is filtered on the QuoteType anyway it'll always be the same). It's now working perfectly fine, but i'm still not sure why the error was being thrown.

QuoteType:

CREATE VIEW [dbo].[vwQuoteType]
AS
SELECT     TOP (100) PERCENT AttributeValue, Value, DisplayOrder, AttributeName, ObjectTypeCode
FROM         appdb.ABC_LABS_MSCRM.dbo.StringMap
WHERE     (ObjectTypeCode = 1084) AND (AttributeName = N'abc_quotetype')
ORDER BY AttributeName

the weird part too is that, on it's own, the quotetype view works fine.