January 18, 2012 at 10:36 pm
Hi,
" String or binary data would be truncated"
I am getting the above error in my SP. While Investigating the problem on the testing server I boiled down to the Column which is causing this error by executing the steps in SP step by step. But whats bewildering is that the both source and destination columns have same size(i.e varchar(40)). But still I get the error. Below is the statement in the SP.
BEGIN TRANSACTION
INSERT INTO dbo.HVC_CustomerContactHistory(ContactDate, BTN, RepName, SpokeTo)
SELECT C.ContactDate, C.BTN, C.RepName, C.SpokeTo FROM dbo.vHVC_CustomerContactHistory C WITH (READUNCOMMITTED)
INNER JOIN #USP_ArchiveTranTables_A C2
ON C.ContactID = C2.ContactID
ROLLBACK TRANSACTION
dbo.HVC_CustomerContactHistory is a table in the Prod Server in some DB
dbo.vHVC_CustomerContactHistory is a view on the same server and DB
#USP_ArchiveTranTables_A is a temp table in the SP(It Only one column and that is a INT value So no Need to Worry Abt It)
After The Investigation I Knew The Column That Is Causing The Error Is "SpokeTo".
I Checked The Column in dbo.HVC_CustomerContactHistory table it is (varchar(40),not null)
And I Checked For the Same colum in the dbo.vHVC_CustomerContactHistory view. It Still is (varchar(40),not null) But I Still get The Error "String or binary data would be truncated".
I Hope U Guys Understood The Situation. Forgive me If I am not Clear.
Thank You,
Naz
January 19, 2012 at 12:11 am
Have you tried commenting out that column and rerunning the Insert? What are the column definitions for the remaining columns?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 12:14 am
Yes, It Works.
But I have found out something else.
I have found out that the base table for the view "dbo.vHVC_CustomerContactHistory". Which is Named "HVCE_3.dbo.HVC_CustomerContactHistory" Which Resides in another DB "HVCE_3". There is difference varchar datatype sizes in two columns.
Column 1 called "SpokeTo" has datatype varchar(100) in "HVCE_3.dbo.HVC_CustomerContactHistory" table(Which is the base table for the view)...... Where as in the View ""dbo.vHVC_CustomerContactHistory(Which Is on another DB)" the same column "SpokeTo" has datatype varchar(40).
I have one more column for which datatype varchar has different sizes.
Could this be giving me the above error.
??
January 19, 2012 at 12:17 am
That would be the cause. I would find out which one is the correct size and make all of them the same size.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 12:26 am
So I Think The Better Option Would Be To Change The Sizes In The View And The Target Table In Which The Data Is Being Inserted Into ....Right?
As The Data In The Base Table for The View comes from some imp source.
What wud be the Final Advice?
January 19, 2012 at 12:27 am
If the size needs to be Varchar(100) then change them to varchar(100). This depends on the max data size between the different sources as well as the business requirements.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 12:33 am
Ok Thank You for the help.
I have to Discuss this Issue with my team lead. If it works I will get back again and Thank u once again.
TC BYe
January 19, 2012 at 12:35 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 8:10 am
Hi I have a new problem. In the above the view "dbo.vHVC_CustomerContactHistory" the base table for the view is "HVCE_3.dbo.HVC_CustomerContactHistory".
As mentioned above. What is puzziling is that when I expand the view(In Object Explorer) and look at the columns the size of a prticular column called "SpokeTo" has "varchar(40)". But when I look at the base table the same column(SpokeTo) has varchar(100).
How Is It Possible? How can base table(HVCE_3.dbo.HVC_CustomerContactHistory) of a view have a different size compared to view generated from that table?
I saw the defenition of View. It is something like below.
CREATE VIEW [dbo].[vHVC_CustomerContactHistory]
AS
SELECT * FROM HVCE_3.dbo.HVC_CustomerContactHistory WITH (READUNCOMMITTED)
Any Ideas what I need to solve this issue?
January 19, 2012 at 8:14 am
It looks like the view is not schema bound and the underlying table was probably updated after the view was created. When doing that, the view needs refreshed.
Run either sp_refreshview or the newer sp_refreshsqlmodule
Also, you can try modifying the view so it utilizes "With SchemaBinding" to help prevent this issue in the future.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 8:27 am
Thank You Dude. I tired refreshing the view. It Dint Work. I will try the other two options u gave and get back to you.
This was really helpful. I really appreciate the help u guys.
Thnks guys
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply