Sql-server – Statement could not be prepared

sql serversql-server-2005sql-server-2008

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.

Best Answer

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

Related Solutions

Sql-server – The multi-part identifier “xxx” could not be bound

Reformatting your code shows the problem. All I have changed is whitespace and added a comment:

HAVING
    (
        SELECT COUNT(*) FROM
        (
            SELECT *
            FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
            WHERE da2.ContractId = DA.ContractId
            AND da2.RoomTypeId = DA.RoomTypeId
            AND da2.Date >= @FromDate AND da2.Date < @ToDate
            AND da2.IsSold = 0
            AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)
        ) T
    ) = @StayDates
AND
    ( 
        (
            @AllowOnRequestBookings = 'False' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0

        )
    )
AND 
    (
        (
            @AllowOnRequestBookings = 'True' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0
        )
    )

As can now easily be seen, da2 is an alias defined in the subquery in the first ANDd term, and so is not available in other terms.

It may be that you want to move the second and third ANDd terms here into the subquery, or it might be more complicated than that.

SQL Server Linked Servers – Limitation of 10 Branches in CASE Expression

Clearly there isn't a nested CASE expression here.

Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM AdventureWorks2012.Production.Product AS p

Local query plan

That query is local (no linked server) and the Compute Scalar defines the following expression:

Nested CASE expression

This is fine when executed locally, because the parser does not see a nested CASE statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).

However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested CASE statement more than 10 levels deep and you get error 8180.

Another oddity. This inline table-valued function produces the same error

The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.

But a similar multi-statement TVF works fine

Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE result - varchar(2) - the expression is remoted with the msTVF and you will get an error.

Ultimately, the error occurs when an over-nested CASE is evaluated by the remote server. If the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p

CASE not remoted

Best Answer

Related Solutions

Sql-server – The multi-part identifier “xxx” could not be bound

SQL Server Linked Servers – Limitation of 10 Branches in CASE Expression

Related Question