Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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
share|improve this answer
    
they are. that's because this view was originally created using ssms's query designer and i just haven't bothered fixing it. –  DForck42 Aug 1 '11 at 16:50
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.