January 18, 2012 at 10:31 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 18, 2012 at 10:43 pm
You said dbo.vHVC_CustomerContactHistory is a view right? Then, did u check the base table of the view from where the column is derived from?
January 18, 2012 at 10:58 pm
Ok I will Check and Get Back
January 19, 2012 at 12:01 am
Hi I have found out 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 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 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.
If So what is the solution? I would like to know which object I have to ALTER. The View Or The Base Table?
I hope U guys Understood It. I am Sorry If I am not clear. Please help me as soon as possible
Than you,
Naz.
January 19, 2012 at 12:21 am
I More Thing I would Like to Add. I am working on 2005. I suppose that will not make any diff. Just in case I am informing because the Forum Name is T-SQL (SS2K8)
January 19, 2012 at 12:25 am
No diff on SQL version.
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
This is a cross-post. Same thread, two forums.
Here is the other one.
http://www.sqlservercentral.com/Forums/Topic1238442-8-1.aspx
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:29 am
What part of the database can you re-design? If it is possible to alter the destination table,i guess it will be better. If you change the base table for the view, you may not know what repercussions it might have on the overall system.
But PLEASE do the some Impact Analysis before you touch anything.!!!!!
January 19, 2012 at 12:30 am
Oh I am sorry. I just wanted to get view from two differnt experts. I am new to the Forum Culture. Thats the reason I doono That I should not do post on two forms. Sry Once again.
Any Way Thank You.
January 19, 2012 at 12:33 am
nawaz1774 (1/19/2012)
Oh I am sorry. I just wanted to get view from two differnt experts. I am new to the Forum Culture. Thats the reason I doono That I should not do post on two forms. Sry Once again.Any Way Thank You.
It just fragments the discussion.. IN the end, both of us are recommending the same thing - you will need to perform some analysis on code and business rules in your organization in order to determine the best course of action.
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:35 am
Yeah Thank You For The Advice. I will Discuss with my Manager regarding this and then only Will I go ahead.
Regards
Naz.
January 19, 2012 at 7:12 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:08 am
Hi I have a new problem. In the above the view is "dbo.vHVC_CustomerContactHistory". The base table for this view "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:09 am
Hi I have a new problem. In the above the view is "dbo.vHVC_CustomerContactHistory". The base table for this view "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:15 am
See my answer here.
http://www.sqlservercentral.com/Forums/Topic1238442-8-1.aspx?Update=1
Please just continue this conversation in one thread - it is really fragmenting the whole thing.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply