CTE giving string or truncate error 3

[sds814]

The OrderKey needs to be updated by each TableName. So if TableA was repeated 4 times and TableB was repeated 3 times it would look like:

TableName (nvarchar) OrderKey (int)
TableA 10
TableA 20
TableA 30
TableA 40
TableB 10
TableB 20
TableB 30

This is the code that I have:

WITH FieldOrderKey (TableName, TableNumber) AS
(
	SELECT TableNameLabels, ROW_NUMBER() OVER(PARTITION BY TableNameLabels ORDER BY TableNameLabels) AS [TableNumber] 
	FROM tblzCdCustomFieldLabels
	WHERE TableNameLabels <> ''
	
)

UPDATE tblzcdCustomFieldLabels
SET tblzcdCustomFieldLabels.OrderKey = TableNumber * 10
FROM tblzcdCustomFieldLabels
   INNER JOIN FieldOrderKey ON tblzcdCustomFieldLabels.TableNameLabels = FieldOrderKey.TableName

The error I get is: String or Binary data would be truncated. The error points to: WHERE TableNameLabels <> ''

Thanks for the help.

 

[gmmastros]

I don't see anything that would cause that error.

What is the data type for the OrderKey column?

Select * from information_Schema.Columns Where table_Name = 'tblzcdCustomFieldLabels' and column_name = 'OrderKey'


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
Doug needs a new pair of shoes, please click an ad link.

 

[ESquared]

1. The concept of your query isn't going to work because you aren't using any unique key for rows in tblzcdCustomFieldLabels. Don't skip over the importance of this. What you're trying to do is impossible without a uniquely-row-identifying key in your CTE that is joined to in the UPDATE clause.
2. tblzCdCustomFieldLabels is nvarchar(what)?
3. Are you mixing up the column names TableName and TableNameLabels?

Please provide more detailed sample data for all tables involved, that includes examples for all columns referenced.

 

[sds814]

George - the data type of OrderKey is int

ESquared -
1. You're right. I found that out doing an example yesterday. The query below is revised by joining FieldOrderKey with tblzCdCustomFieldLabels on the primary key, which is the tablename_columnname.
2. tblzcdCustomFieldLabels is a table so it doesn't have a data type. TableNameLabels is nvarchar(100) and OrderKey is int
3. TableName is derived from TableNameLabels so there isn't a mix up between the column names.

here is more sample data:
TableName OrderKey ColumnName
TableA 10 ControlID
TableA 20 ApprovalLevel
TableA 30 Counsel
TableA 40 Co-Counsel
TableB 10 NoteID
TableB 20 Timing
TableB 30 BANSAP

Code:

WITH FieldOrderKey (FieldNameCD, TableNumber) AS
(
	SELECT FieldNameCD, ROW_NUMBER() OVER(PARTITION BY TableNameLabels ORDER BY TableNameLabels) AS [TableNumber] 
	FROM tblzCdCustomFieldLabels
	WHERE TableNameLabels <> ''
	
)

UPDATE tblzcdCustomFieldLabels
SET tblzcdCustomFieldLabels.OrderKey = TableNumber * 10
FROM tblzcdCustomFieldLabels
   INNER JOIN FieldOrderKey ON tblzcdCustomFieldLabels.FieldNameCD = FieldOrderKey.FieldNameCD

SELECT * FROM FieldOrderKey before the update statement gives the correct values. I also commented the WHERE clause to see if the script would run w/o any errors, but I still get the String or Binary data would be truncated.

Thanks for the help.

 

[SQLDenis]

>>and OrderKey is int

what kind?

tinyint
smallint
int
bigint

the problem is here

SET tblzcdCustomFieldLabels.OrderKey = TableNumber * 10

TableNumber * 10 does not fit in the OrderKey column



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sds814]

it's int(4)..The most columns in a table is 189 so the last orderkey would be 1890.

Would I get a string or binary truncate error if the int datatype was too small?

 

[SQLDenis]

You should get an Arithmetic overflow error

create table #temp (tiny tinyint)

insert #temp values(189)

update #temp set tiny = tiny * 10

Server: Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 1890.
The statement has been terminated.

can you run

select * from information_schema.columns
where table_name = 'tblzcdCustomFieldLabels'

and post the results here

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sds814]

TableName Data_Type Char_Max_Length

FieldNameCD nvarchar 50
FieldNameDescription nvarchar 150
OrderKey int NULL
ActiveSW bit NULL
HideSW bit NULL
RequiredSW bit NULL
TableNameLabels nvarchar 100
ColumnNameLabels nvarchar 200

 

[SQLDenis]

any triggers on the table?

exec sp_helptrigger tblzcdCustomFieldLabels

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sds814]

There are triggers on the table.

One trigger is if the FieldNameDescription changes in tblzCdCustomFieldLabels then the fieldDescription in another table should reflect that change. The other triggers are to update the audit tables if any data is modified in tblzCdCustomFieldLabels.

 

[SQLDenis]

i can guarantee you 99.9999% that the problem is in the trigger

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sds814]

Well that sucks..these triggers were created by somebody else.

Should I disable these triggers try out my code and then enable them again. I don't have too much experience with triggers.

 

[SQLDenis]

>>Well that sucks..these triggers were created by somebody else.

then tell someone else to fix their code or disable triggers but what if someone else inserts/updates at the same time and the triggers are disabled?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sds814]

I'm working on a local db...so that shouldn't be a problem

I disabled the triggers, ran the query, and then enabled the triggers again..And it worked

 

[ESquared]

I sense a new thread to troubleshoot your trigger..

And I don't know how I didn't get the column name in that I was asking about. I saw that I put only the table name but then missed adding the column, somehow... but you figured it out.

 

[sds814]

ESquared- Going back to the 1st point that you made where the primary key has to be unique. The example that I tried using the AdventureWorks was this:

CREATE TABLE #tmpDepartment
(
	Name nvarchar(50),
	GroupName nvarchar(50),
	OrderKey int DEFAULT 0
)

INSERT #tmpDepartment (Name, GroupName)
SELECT Name, GroupName
FROM HumanResources.Department;

WITH DeptCount(Name, GroupName, GroupCount) AS
(  
	SELECT Name, GroupName, ROW_NUMBER() OVER(PARTITION BY (GroupName) ORDER BY (GroupName)) AS 'GroupCount'
	FROM #tmpDepartment
)


UPDATE #tmpDepartment
SET #tmpDepartment.OrderKey = DeptCount.GroupCount * 10
FROM #tmpDepartment
  INNER JOIN DeptCount ON DeptCount.GroupName = #tmpDepartment.GroupName 

SELECT * 
FROM #tmpDepartment
ORDER BY GroupName, OrderKey

DROP TABLE #tmpDepartment

As you said, since the GroupName isn't an unique key the OrderKey didn't produce the correct results. If you could please explain how "SQL Server" decided that the "Executive General and Administration" should have orderkey 40, 50, 50, 50, 50. So my question is more about understanding the logic SQL Server executed on this query.

Thanks for your help.

 

[ESquared]

You will understand why when you run the following query (hint: you get 50 rows, which indicates something). All I changed was to comment out two lines and put in a select *.

WITH DeptCount(Name, GroupName, GroupCount) AS
(  
    SELECT Name, GroupName, ROW_NUMBER() OVER(PARTITION BY (GroupName) ORDER BY (GroupName)) AS 'GroupCount'
    FROM #tmpDepartment
)
-- UPDATE #tmpDepartment
-- SET #tmpDepartment.OrderKey = DeptCount.GroupCount * 10
select *
FROM #tmpDepartment
  INNER JOIN DeptCount ON DeptCount.GroupName = #tmpDepartment.GroupName

You didn't join on a uniquely row-identifying key, which would be GroupName and Name.

Here is the essence of the problem with the above join, in the simplest form:

SELECT *
FROM
   #tmpDepartment D1
   INNER JOIN #tmpDepartment D2 ON D1.GroupName = D2.GroupName

And here is a corrected query with the CTE:

WITH DeptCount(Name, GroupName, GroupCount) AS
(  
    SELECT Name, GroupName, ROW_NUMBER() OVER(PARTITION BY (GroupName) ORDER BY (GroupName)) AS 'GroupCount'
    FROM #tmpDepartment
)
UPDATE #tmpDepartment
SET #tmpDepartment.OrderKey = DeptCount.GroupCount * 10
FROM
  #tmpDepartment D
  INNER JOIN DeptCount ON DeptCount.GroupName = D.GroupName [b]AND DeptCount.Name = D.Name[/b]

The only changed part is in bold.