none
the multi-part identifier 'xxxx' could not be bound

    Question

  • Is it necessary for me to include the two table schemas for the query included below this snippet that shows the error?

    Declare	@RID int,
           @PID int,
           @CID int,
           @TID int,
           @FromDate datetime,
           @ToDate datetime	;
    
    SET @RID = 0
    SET @PID = 0
    SET @CID = 0
    SET @TID = 0
    SET @FromDate = '6/16/2013 8:00'
    SET @ToDate = '6/28/2013 9:30'
    
    SELECT 
    	t.ID,
    	t.CustomerID,
    	t.PayerID,
    	t.ReceiverID,
    	g.Title AS [Response Category],
    	t.ResponseSummary AS [Response Summary],
    	t.ErrorCode AS [Error Code]
    FROM [Eligibility5010Tx].[dbo].[tblTransaction] t WITH (nolock),
         [BUSINESSENTITY].[CRBusinessEntity].[dbo].[tblWebCustomer] c with(nolock)
         INNER JOIN [tblGeneralLookup] g WITH (nolock) ON t.ResponseCategoryID = g.LookupID
    WHERE t.ResponseCategoryID IN (0,4)
      AND g.LookupTypeID = 22 
      AND t.RequestWhen BETWEEN @FromDate AND @ToDate
      AND (@RID = 0 OR t.ReceiverID = @RID)
      AND (@PID = 0 OR t.PayerID = @PID)
      AND (@CID = 0 OR t.CustomerID = @CID)
      AND (@TID = 0 OR t.CustomerID = c.CustomerID)
      AND  c.TradingPartnerID = + convert(varchar(6),@TID)
      ORDER BY g.Title, ResponseSummary


    • Edited by hazz Friday, June 28, 2013 10:40 PM
    Friday, June 28, 2013 10:35 PM

Answers

  • Is that table in another database? Do you get that same error if you execute this procedure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by hazz Monday, July 01, 2013 5:42 PM
    Friday, June 28, 2013 11:43 PM
    Moderator
  • Yes Naomi, that table is in another database.

    The sproc I based this on executes correctly with no errors:

    Declare	@RID int,
           @PID int,
           @CID int,
           @TID int,
           @FromDate datetime,
           @ToDate datetime	;
    
    SET @RID = 0
    SET @PID = 0
    SET @CID = 0
    SET @TID = 0
    SET @FromDate = '6/16/2013 8:00'
    SET @ToDate = '6/28/2013 9:30'
    
    
    SELECT 
    	t.ID,
    	t.CustomerID,
    	t.PayerID,
    	t.ReceiverID,
    	g.Title AS [Response Category],
    	t.ResponseSummary AS [Response Summary],
    	t.ErrorCode AS [Error Code]
    FROM [Eligibility5010Tx].[dbo].[tblTransaction] t WITH (nolock)
      INNER JOIN [tblGeneralLookup] g WITH (nolock) ON t.ResponseCategoryID = g.LookupID
    WHERE t.ResponseCategoryID IN (0,4)
      AND g.LookupTypeID = 22 
      AND t.RequestWhen BETWEEN @FromDate AND @ToDate
      AND (@RID = 0 OR t.ReceiverID = @RID)
      AND (@PID = 0 OR t.PayerID = @PID)
      AND (@CID = 0 OR t.CustomerID = @CID)
    ORDER BY g.Title, ResponseSummary


    • Edited by hazz Saturday, June 29, 2013 12:27 AM clarification
    • Marked as answer by hazz Monday, July 01, 2013 5:45 PM
    Saturday, June 29, 2013 12:21 AM
  • When you just do a SELECT TOP (10) * on the linked server table, does it work?  Do you see the column in question?

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    • Marked as answer by hazz Monday, July 01, 2013 5:44 PM
    Saturday, June 29, 2013 1:46 AM
    Moderator

All replies

  • Why you're using a cross join? Why all these NOLOCK? 

    Does tblTransaction have ResponseCategoryID column?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, June 28, 2013 10:39 PM
    Moderator
  • Thank you for the questions Naomi.

    tblTransaction does indeed have a ResponseCategoryID column.

    When our dba returns, I will ask why all the NOLOCKS. I understand Dirty reads can result.

    I altered this existing, working sproc, included at bottom, adding the following:

    ,BUSINESSENTITY.CRBusinessEntity.dbo.[tblWebCustomer] c with(nolock)
    
    --added the above to the FROM clause
    --and the following to the WHERE clause
    
     AND t.CustomerID = c.CustomerID
     AND c.TradingPartnerID = ' + convert(varchar(6),@TradingPartnerID)

    CREATE PROCEDURE [dbo].[GetReportErrorDetail_sp] 
    	@RID int = 0,
    	@PID int = 0,
    	@CID int = 0,
    	@FromDate datetime,	--start date
    	@ToDate datetime	--end date
    AS
    BEGIN
    
    SET NOCOUNT ON;
    
    SET @ToDate = DATEADD(MINUTE,1,@ToDate )
    
    SELECT 
    	t.ID,
    	t.CustomerID,
    	t.PayerID,
    	t.ReceiverID,
    	g.Title AS [Response Category],
    	t.ResponseSummary AS [Response Summary],
    	t.ErrorCode AS [Error Code]
    FROM [Eligibility5010Tx].[dbo].[tblTransaction] t WITH (nolock)
      INNER JOIN [tblGeneralLookup] g WITH (nolock) ON t.ResponseCategoryID = g.LookupID
    WHERE t.ResponseCategoryID IN (0,4)
      AND g.LookupTypeID = 22 
      AND t.RequestWhen BETWEEN @FromDate AND @ToDate
      AND (@RID = 0 OR t.ReceiverID = @RID)
      AND (@PID = 0 OR t.PayerID = @PID)
      AND (@CID = 0 OR t.CustomerID = @CID)
    ORDER BY g.Title, ResponseSummary



    • Edited by hazz Friday, June 28, 2013 11:12 PM adjustment
    Friday, June 28, 2013 10:53 PM
  • Is that table in another database? Do you get that same error if you execute this procedure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by hazz Monday, July 01, 2013 5:42 PM
    Friday, June 28, 2013 11:43 PM
    Moderator
  • Yes Naomi, that table is in another database.

    The sproc I based this on executes correctly with no errors:

    Declare	@RID int,
           @PID int,
           @CID int,
           @TID int,
           @FromDate datetime,
           @ToDate datetime	;
    
    SET @RID = 0
    SET @PID = 0
    SET @CID = 0
    SET @TID = 0
    SET @FromDate = '6/16/2013 8:00'
    SET @ToDate = '6/28/2013 9:30'
    
    
    SELECT 
    	t.ID,
    	t.CustomerID,
    	t.PayerID,
    	t.ReceiverID,
    	g.Title AS [Response Category],
    	t.ResponseSummary AS [Response Summary],
    	t.ErrorCode AS [Error Code]
    FROM [Eligibility5010Tx].[dbo].[tblTransaction] t WITH (nolock)
      INNER JOIN [tblGeneralLookup] g WITH (nolock) ON t.ResponseCategoryID = g.LookupID
    WHERE t.ResponseCategoryID IN (0,4)
      AND g.LookupTypeID = 22 
      AND t.RequestWhen BETWEEN @FromDate AND @ToDate
      AND (@RID = 0 OR t.ReceiverID = @RID)
      AND (@PID = 0 OR t.PayerID = @PID)
      AND (@CID = 0 OR t.CustomerID = @CID)
    ORDER BY g.Title, ResponseSummary


    • Edited by hazz Saturday, June 29, 2013 12:27 AM clarification
    • Marked as answer by hazz Monday, July 01, 2013 5:45 PM
    Saturday, June 29, 2013 12:21 AM
  • I looked a bit closer - did you also use linked server here? I don't see a reason why would you get this error. Have you tried running the new query directly first, not as a stored procedure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Saturday, June 29, 2013 12:50 AM
    Moderator
  • thank you for your outstanding attentiveness Naomi. I haven't posted for I think 2 years and you are still providing this AMAZING support.

    I will have to ask our dba on Monday and I'll reply back.

    With kind appreciation.

    Greg

    Saturday, June 29, 2013 1:14 AM
  • When you just do a SELECT TOP (10) * on the linked server table, does it work?  Do you see the column in question?

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    • Marked as answer by hazz Monday, July 01, 2013 5:44 PM
    Saturday, June 29, 2013 1:46 AM
    Moderator
  • Right question.  The table was in a different database. I learned something about linked servers.

    I did need to add a join too!

    Thank you,

    SELECT 
           t.ID,
           t.CustomerID,
           t.PayerID,
           t.ReceiverID,
           g.Title AS [Response Category],
           t.ResponseSummary AS [Response Summary],
           t.ErrorCode AS [Error Code]
    FROM Eligibility5010Tx.dbo.[tblTransaction] t WITH (nolock)
         INNER JOIN BUSINESSENTITY.CRBusinessEntity.dbo.[tblWebCustomer] c with(nolock) ON t.CustomerID = c.CustomerID
         INNER JOIN Eligibility5010.dbo.[tblGeneralLookup] g WITH (nolock) ON t.ResponseCategoryID = g.LookupID
    WHERE t.ResponseCategoryID IN (0,4)
      AND g.LookupTypeID = 22 
      AND t.RequestWhen BETWEEN @FromDate AND @ToDate
      AND (@RID = 0 OR t.ReceiverID = @RID)
      AND (@PID = 0 OR t.PayerID = @PID)
      AND (@CID = 0 OR t.CustomerID = @CID)
      AND (@TID = 0 OR t.CustomerID = c.CustomerID)
      AND  c.TradingPartnerID = + convert(varchar(6),@TID)
      ORDER BY g.Title, ResponseSummary

    Monday, July 01, 2013 5:44 PM
  • right question Kalman. I hadn't understood the Server Objects Linked Servers table locations and how to reference them correctly.
    Monday, July 01, 2013 5:46 PM