DarinAlred
asked on
Define: Could Not Be Bound
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'
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'
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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)
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)
>>BTW: was does "could not be bound" mean?<<
I guess my comment was ignored. Oh well ...
I guess my comment was ignored. Oh well ...
acperkins addressed that in his post...
...unless you are asking what "bind" means in the context of retrieving data from a database...
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.
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.
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?
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?
ASKER
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.
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.
ASKER
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
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
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
UPDATE TNode
SET Sequence = Cast ( 0 as int)
(i.e. drop the alias, "T.", from the column reference).