?
Solved

Define: Could Not Be Bound

Posted on 2007-09-28
17
Medium Priority
?
988 Views
Last Modified: 2010-05-19
I am using SQL Express 2005 on a SQL Database where the compatibility level is set to SQL Server 2000 (80).  I am trying to change a value that usually gets autopopulated once, then never gets altered.  The value is a sequence value of reports that controls the order the reports display in a third party proprietary software.  Our office uses SQL all the time for queries and altering tables, yet I can't seem to find any documentation on "The multi-part identifier could not be bound" as a generic definition so I can solve the problem and avoid them in the future.  Here is the code I have been trying to use. In a nutshell I do want to be able to fix the code, and learn the term so I don't have to run into this issue again.

BEGIN TRANSACTION ReportChange

UPDATE TNode

SET T.Sequence = Cast ( 0 as int)

SELECT   C.co, t.label, t.description, t.sequence

FROM CInfo c

JOIN TNode t on c.guidfield = t.contextId

AND t.nodeTypeId IN

      (SELECT tn.nodeTypeId

            FROM TNodeType tn

                  WHERE tn.label = 'Report')

AND t.parentnodeId in

      (SELECT t2.nodeId

            FROM TNode t2

                  WHERE t2.label = 'Every Payroll'

                  AND t2.contextId is NULL)

AND c.co = '5320'
0
Comment
Question by:DarinAlred
  • 6
  • 4
  • 3
  • +2
17 Comments
 
LVL 10

Expert Comment

by:lahousden
ID: 19980104
Your first update should read

UPDATE TNode
SET Sequence = Cast ( 0 as int)

(i.e. drop the alias, "T.", from the column reference).
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 560 total points
ID: 19980106
the update is wrong:

UPDATE TNode
SET [Sequence] = Cast ( 0 as int)

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 80 total points
ID: 19980218
But to answer your question ...       :)

The reason you cannot bind the resultset is because you are missing SET NOCOUNT ON at the top.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:DarinAlred
ID: 19980543
angelIII:
Thank you.

When I made the change I got a new error:
Msg 8152, Level 16, State 2, Procedure TNode_Auto_Update, Line 9
String or binary data would be truncated.
The statement has been terminated.

Can you help on this question? Or should I start a new one?

-- BTW: was does "could not be bound" mean?
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 19980548
The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.

FROM http://doc.ddart.net/mssql/sql70/ua-uz_3.htm

A table alias specified in a FROM clause cannot be used as a qualifier in SET column_name. For example, this is not valid:

UPDATE titles
        SET t.ytd_sales = t.ytd_sales + s.qty
        FROM titles t, sales s
        WHERE t.title_id = s.title_id
        AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

 

To make the example work, remove the t. alias from the column name.

UPDATE titles
        SET ytd_sales = t.ytd_sales + s.qty
        FROM titles t, sales s
        WHERE t.title_id = s.title_id
        AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 19980631
>>BTW: was does "could not be bound" mean?<<
I guess my comment was ignored.  Oh well ...
0
 
LVL 10

Expert Comment

by:lahousden
ID: 19980639
acperkins addressed that in his post...
0
 
LVL 10

Expert Comment

by:lahousden
ID: 19980655
...unless you are asking what "bind" means in the context of retrieving data from a database...
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 19980674
Could not be bound refers to the fact that you were trying to use an alias in the set statement -

SET T.Sequence = Cast ( 0 as int)

SQL does not understand using an alias there (see my earlier comment). It could not bind the t. alias to the column.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 19980714
As for

When I made the change I got a new error:
Msg 8152, Level 16, State 2, Procedure TNode_Auto_Update, Line 9
String or binary data would be truncated.
The statement has been terminated.

Is this the complete stored procedure?
0
 

Author Comment

by:DarinAlred
ID: 19980736
I am sorry I was too vague in my last BTW question.  I did see the cause of the "could not be bound" i.e.  that I  am "missing SET NOCOUNT ON at the top" thank you - acperkins.  However, I am seeking the "text book" definition, of the message.  Like the  definition on this Microsoft.com page,
http://support.microsoft.com/kb/119554.  I know it is probably a much simpler question than most of you are used to answering; but I like to read the definitions for problems (when they exist) to better understand what is being displayed to me.  
0
 

Author Comment

by:DarinAlred
ID: 19980778
ptjcb:

No this is not the complete store procedure.  In fact I have no stored procedure named TNode_Auto_Update, all I could find was a trigger called TNode_Auto_Update that is on the table called TNode.

This is the script for TNode_Auto_Update. ::

USE [MillenniumAARON]
GO
/****** Object:  Trigger [dbo].[TNode_Auto_Update]    Script Date: 09/28/2007 13:36:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Trigger Automatically Generated By MPI_MakeTrig_Update
-- DO NOT MODIFY THIS TRIGGER!  YOUR CHANGES WILL BE LOST!
ALTER TRIGGER [dbo].[TNode_Auto_Update] ON [dbo].[TNode] FOR UPDATE AS

    -- If no rows were added get out now
    IF @@ROWCOUNT = 0 RETURN

    -- Update the lastChange and lastChangeUser fields
    UPDATE TNode
    SET TNode.lastChange = GETDATE(), lastChangeUser = APP_NAME()
    FROM TNode, inserted WHERE TNode.ROWGUIDCOL = inserted.ROWGUIDCOL

    -- Log the change to the appropriate LogChanges table
    DECLARE @LogTableID INTEGER
    SELECT @LogTableID = i1 FROM dbflags WITH (REPEATABLEREAD) WHERE label = 'LogChanges'
    IF @LogTableID = 1
    BEGIN

    if update(parentNodeID)
    insert into LogChangesS1(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'parentNodeID', deleted.parentNodeID, inserted.parentNodeID, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(nodeTypeID)
    insert into LogChangesS1(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'nodeTypeID', deleted.nodeTypeID, inserted.nodeTypeID, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(label)
    insert into LogChangesS1(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'label', deleted.label, inserted.label, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(description)
    insert into LogChangesS1(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'description', deleted.description, inserted.description, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(sequence)
    insert into LogChangesS1(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'sequence', deleted.sequence, inserted.sequence, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(contextID)
    insert into LogChangesS1(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'contextID', deleted.contextID, inserted.contextID, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    END
    ELSE
    BEGIN
    if update(parentNodeID)
    insert into LogChangesS2(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'parentNodeID', deleted.parentNodeID, inserted.parentNodeID, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(nodeTypeID)
    insert into LogChangesS2(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'nodeTypeID', deleted.nodeTypeID, inserted.nodeTypeID, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(label)
    insert into LogChangesS2(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'label', deleted.label, inserted.label, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(description)
    insert into LogChangesS2(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'description', deleted.description, inserted.description, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(sequence)
    insert into LogChangesS2(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'sequence', deleted.sequence, inserted.sequence, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    if update(contextID)
    insert into LogChangesS2(tablename, guidfield, fieldname, oldvalue, newvalue, changetype)
    select 'TNode', inserted.ROWGUIDCOL, 'contextID', deleted.contextID, inserted.contextID, 'U'
    from inserted, deleted where inserted.ROWGUIDCOL = deleted.ROWGUIDCOL

    END

0
 

Author Comment

by:DarinAlred
ID: 19980795
Another interesting point is that the  T.sequence number is an integer; so why would it truncated if I have casted it as a integer?
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 160 total points
ID: 19980842
When retrieving data from an RDBMS engine, the scripting or program technology layer needs to define "placeholders" in its data space to place retrieved data into.  This is called "binding".  In any SQL statement where data is going to be returned to the script or program unit, all variables need to be bound - they need a home to go to.

Now, in your batch you have not specified "SET NOCOUNT ON" (as acperkins observes) - this means that the default of returning a DONE_IN_PROC message to the client for each statement executes is enabled, and the scripting or program technology is scrambling around trying to find homes for every variable that is going to come back from the RDBMS engine.  
Now, because you used an extraneous alias in your update the RDBMS engine didn't identify T.Sequence as being a column in any table you referenced in the UPDATE statement and so it passed the term "T.Sequence" back to the technology layer for it to bind as a variable - and this is where the technology layer says "I don't know how to bind that variable..."
0
 

Author Comment

by:DarinAlred
ID: 19980914
lahousden:
Thank you very much. That is what I was looking for as a definition. I admit I know the concepts but not all the terminology.

Protocol Question:  Do I need to assign points now and start a new question for the new error I got? Or should I wait for a response?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 560 total points
ID: 19981539
>Protocol Question:  Do I need to assign points now and start a new question for the new error I got? Or should I wait for a response?
technically, you should indeed close, as the new error has nothing to do with the original one, IMHO.
as you got the solution AND the full explanation

now, line 9 of your trigger seems to be this:
UPDATE TNode
    SET TNode.lastChange = GETDATE(), lastChangeUser = APP_NAME()
    FROM TNode, inserted WHERE TNode.ROWGUIDCOL = inserted.ROWGUIDCOL

which would then make the error being on probably the column lastChangeUser to be too small for the value of APP_NAME()...
you have 2 options:
* either, make the column lastChangeUser larger
* or, use a substring() function around APP_NAME() to get a string max length corresponding to the field's length

if that is all, no need to open a new question...
0
 

Author Comment

by:DarinAlred
ID: 19982013
Thank you for all your help.  I tried to split the points fairly.  I may be opening a new question about this second error since the proprietary software does not  allow changes to the trigger so I will probably need to go back to the drawing board on what actually can be done to solve my need to manipulate the sequence numbers in the database.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question