SQL 2005 Data Type Error

Question

SQL 2005 Data Type Error

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
December 3, 2008 at 4:16 am

#202462

I am having an issue inserting into 'tbldeals'.
ERROR
Msg 8152, Level 16, State 4, Procedure procImportAllFiles, Line 36
String or binary data would be truncated.
INSERT CODE
INSERT INTO dbo.tblDeals(dbo.tblDeals.Client,dbo.tblDeals.Bought_Sold,dbo.tblDeals.RIC,
dbo.tblDeals.Trade_Date,dbo.tblDeals.Price,dbo.tblDeals.Counterparty_Code,
dbo.tblDeals.Dealer,dbo.tblDeals.Commission,dbo.tblDeals.Consideration,
dbo.tblDeals.Quantity,dbo.tblDeals.Currency,dbo.tblDeals.SEDOL,dbo.tblDeals.Participant_ID,
dbo.tblDeals.FIDESSA_Ref,dbo.tblDeals.FX_Rate,dbo.tblDeals.Local_Commission,
dbo.tblDeals.Bgn_Cond1,dbo.tblDeals.Bgn_Cond2,dbo.tblDeals.Bgn_Cond3,dbo.tblDeals.Bgn_Cond4,
dbo.tblDeals.Dealing_Capacity,dbo.tblDeals.RisklessPrincipal,dbo.tblDeals.Sector,
dbo.tblDeals.ClientID,dbo.tblDeals.MarketMaking) SELECT distinct dbo.Bargains.Client,
dbo.Bargains.BoughtSold,dbo.Bargains.RIC,dbo.Bargains.Trade_Date,dbo.Bargains.Price,
dbo.Bargains.Counterparty_Code,dbo.Bargains.Dealer,dbo.Bargains.Commission,dbo.Bargains.Consideration,
dbo.Bargains.Quantity,dbo.Bargains.Currency,dbo.Bargains.SEDOL,dbo.Bargains.Participant_ID,
dbo.Bargains.FIDESSA_Ref,dbo.Bargains.FX_Rate,dbo.Bargains.Local_Commission,dbo.Bargains.Bgn_Cond1,
dbo.Bargains.Bgn_Cond2,dbo.Bargains.Bgn_Cond3,dbo.Bargains.Bgn_Cond4,dbo.Bargains.Dealing_Capacity,
dbo.Bargains.Riskless_Principal,dbo.Bargains.MarketSector,
dbo.Bargains.ClientID, isnull(dbo.tblStocks.MarketMaking,0)
FROM dbo.Bargains
left join tblStocks on Bargains.SEDOL=tblStocks.SEDOL
Table DataTypes
dbo.tblClients
Client_short_Name (nvarchar(30),not null)
Client_long_name (nvarchar(100), null)
CountryCode (varchar(5), null)
ClientID (PK,nvarchar(10), notnull)
VwImportAllFiles
Client (varchar(255),null)
ClientID (varchar(20),null)
CountryCode (varchar(50),null)
Conclusion
I understand I need to change the datatype values for the above columns but do I:
1 - Change them in the View? and How?
2 - Change them in the table?
3 - Is there any other way?
Thanks for your time.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hello

I've taken the liberty of simplifying/reformatting your code to make it a little more readable:[font="Courier New"]INSERT INTO dbo.tblDeals(Client,Bought_Sold,RIC,

Trade_Date, Price, Counterparty_Code,

Dealer, Commission, Consideration,

Quantity, Currency, SEDOL, Participant_ID,

FIDESSA_Ref, FX_Rate, Local_Commission,

Bgn_Cond1, Bgn_Cond2, Bgn_Cond3, Bgn_Cond4,

Dealing_Capacity, RisklessPrincipal, Sector,

ClientID, MarketMaking)

SELECT DISTINCT b.Client, b.BoughtSold, b.RIC,

b.Trade_Date, b.Price, b.Counterparty_Code,

b.Dealer, b.Commission, b.Consideration,

b.Quantity, b.Currency, b.SEDOL, b.Participant_ID,

b.FIDESSA_Ref, b.FX_Rate, b.Local_Commission,

b.Bgn_Cond1, b.Bgn_Cond2, b.Bgn_Cond3, b.Bgn_Cond4,

b.Dealing_Capacity, b.Riskless_Principal, b.MarketSector,

b.ClientID, ISNULL(s.MarketMaking,0)

FROM dbo.Bargains b

LEFT JOIN tblStocks s ON b.SEDOL = s.SEDOL

[/font]

This makes it easier to see that neither dbo.tblClients nor the view VwImportAllFiles are involved in this update and the column names specified (Client_short_Name etc) don't actually appear in the column lists of either the extract (from dbo.Bargains and tblStocks) or the table being updated (dbo.tblDeals).

Which statement, when run, causes the error?

Can you post the table structures for the tables involved in this statement?

Cheers

ChrisM

See also http://www.sqlservercentral.com/Forums/Topic610173-1291-1.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 2

I am very sorry, that was the incorrect INSERT statement!!

Here is the correct one, the column types in my frst post relate to this INSERT:

INSERT INTO dbo.tblClients

(Client_Short_Name,

Client_Long_Name,

CountryCode,

ClientID)

SELECT dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.CountryCode, dbo.vwImportAllNewClientIDs.ClientID

FROM dbo.vwImportAllNewClientIDs

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Heh - no worries. Can you please run the following two statements and post the values they return?

SELECT MAX(LEN(RTRIM(i.Client))) AS Client_LEN,

MAX(LEN(RTRIM(i.CountryCode))) AS CountryCode_LEN,

MAX(LEN(RTRIM(i.ClientID))) AS ClientID_LEN

FROM dbo.vwImportAllNewClientIDs i

SELECT MAX(LEN(i.Client)) AS Client_LEN,

MAX(LEN(i.CountryCode)) AS CountryCode_LEN,

MAX(LEN(i.ClientID)) AS ClientID_LEN

FROM dbo.vwImportAllNewClientIDs i

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 4

Seem to have the same result....

32212

Client_Short_Name nvarchar(30)

Client_Long_Name nvarchar(100)

UK bit

ClientID nvarchar(10)

Category nvarchar(25)

CountryCode nvarchar(50)

McLaganCode nvarchar(50)

SLogixAccountID nvarchar(30)

msrepl_tran_version uniqueidentifier

US bit

Client_Code nvarchar(50)

UnbundlingDesc nvarchar(200)

SalesTrader nvarchar(100)

steveb. SSC-Forever Points: 46733 More actions · Answer 5

You are putting CLIENT with a datatype of varchar(255) into nvarchar(30) and nvarchar(100) there is possibilty that the data is too long so you will need to filter this with a WHERE clause based upon the Length of the string

Same with country code you are trying to put a string with a lengnth of 50 into a col with a length of 5

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Correct me if I'm wrong, but hasn't this been asked and answered twice before? (exact same insert statement and exact same error)

http://www.sqlservercentral.com/Forums/FindPost610162.aspx

http://www.sqlservercentral.com/Forums/Topic610173-1291-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

GilaMonster (12/3/2008)
Correct me if I'm wrong, but hasn't this been asked and answered twice before? (exact same insert statement)
http://www.sqlservercentral.com/Forums/FindPost610162.aspx
http://www.sqlservercentral.com/Forums/Topic610173-1291-1.aspx

Yep, thanks for the confirmation, Gail. I'm outta here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 8

You are correct it has been asked before but the issue has progressed!!

I am asking where is it best to change the DataType??

In the View or in the Table?

steveb. SSC-Forever Points: 46733 More actions · Answer 9

pri.amin (12/3/2008)
You are correct it has been asked before but the issue has progressed!!
I am asking where is it best to change the DataType??
In the View or in the Table?

If you don't want to lose any data or have any truncation, then you will have to change it in the table as this has the smaller data-type.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

pri.amin (12/3/2008)
You are correct it has been asked before but the issue has progressed!!

It has? It's exactly the same error with exactly the same insert statement as I helped you with. You told me a week ago that the problem was resolved and that you had a primary key violation.

It's exactly the same error with exactly the same insert statement as Chris helped you with. He had a great deal of recommendations using left and substring to ensure that the values don't overflow.

If there's still a problem, post on the existing thread, don't start a new one and restate the problem from scratch.

I am asking where is it best to change the DataType??
In the View or in the Table?

Views don't have data types. If you're asking should you left/substring in the view or alter the table to make the columns wider, well, that depends on what you want in the table at the end.

Now, in light of all of the previous advice that Chris gave in one of the threads linked above, where is there still a problem here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 11

A Little Help Please

SSCarpal Tunnel

Points: 4764

December 3, 2008 at 6:10 am

#906806

Thank you very much

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 12

Thanks for the update.

Question is why was it working correctly in SQL2000 and now iv moved the DB to 2005 this error occurs?