Error while running query --String or binary data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error while running query --String or binary data

Author

Topic

unikoman
Starting Member

32 Posts

Posted - 2008-08-15 : 02:06:14

I'm running this query :

SET NOCOUNT ON
DECLARE @LookupProducts table
(
ProductID uniqueidentifier primary key ,
APIRCode varchar(255),
[Description] varchar(255),
up_price decimal(18, 4) NULL,
up_date datetime NULL
)
-- get the products ID
INSERT INTO @LookupProducts (ProductID, APIRCode, [Description])
SELECT ID , p.APIRCode, Description from dbo.products p where p.APIRCode in
(
'ADV0013AU',
'ADV0014AU',
'ADV0022AU',
'ADV0023AU',
'ADV0024AU',
'ADV0031AU',
'ADV0046AU',
'ADV0091AU',
'ADV0118AU',
'ADV0120AU',
'ADV0122AU',
'ADV0123AU',
'ADV0125AU',
'ADV0411AU',
'ADV0412AU',
'ADV0413AU',
'AJF0003AU',
'AMF0100AU',
'AUS0305AU',
'BAR0606AU',
'BAR0607AU',
'BAR0802AU',
'BTA0001AU',
'BTA0002AU',
'BTA0005AU',
'BTA0007AU',
'BTA0009AU',
'BTA0010AU',
'BTA0011AU',
'BTA0012AU',
'BTA0014AU',
'BTA0016AU',
'BTA0017AU',
'BTA0019AU',
'BTA0021AU',
'BTA0023AU',
'BTA0024AU',
'BTA0025AU',
'BTA0026AU',
'BTA0027AU',
'BTA0041AU',
'BTA0042AU',
'BTA0043AU',
'BTA0044AU',
'BTA0051AU',
'BTA0052AU',
'BTA0065AU',
'BTA0066AU',
'BTA0067AU',
'BTA0076AU',
'BTA0077AU',
'BTA0082AU',
'BTA0088AU',
'BTA0127AU',
'BTA0131AU',
'BTA0138AU',
'BTA0139AU',
'BTA0140AU',
'BTA0143AU',
'BTA0144AU',
'BTA0145AU',
'BTA0146AU',
'BTA0147AU',
'BTA0149AU',
'BTA0150AU',
'BTA0151AU',
'BTA0152AU',
'BTA0153AU',
'BTA0173AU',
'BTA0174AU',
'BTA0175AU',
'BTA0196AU',
'BTA0197AU',
'BTA0198AU',
'BTA0200AU',
'BTA0212AU',
'BTA0213AU',
'BTA0214AU',
'BTA0215AU',
'BTA0216AU',
'BTA0217AU',
'BTA0218AU',
'BTA0219AU',
'BTA0220AU',
'BTA0228AU',
'BTA0229AU',
'BTA0230AU',
'BTA0231AU',
'BTA0234AU',
'BTA0235AU',
'BTA0236AU',
'BTA0237AU',
'BTA0401AU',
'BTA0402AU',
'BTA0403AU',
'BTA0491AU',
'BTA0492AU',
'BTA0493AU',
'BTA0601AU',
'BTA0602AU',
'BTA0603AU',
'BTA0604AU',
'BZW0006AU',
'BZW0010AU',
'CA0001',
'CSA0005AU',
'CSA0008AU',
'CSA0016AU',
'CSA0022AU',
'CSA0024AU',
'CSA0117AU',
'FSF0003AU',
'FSF0006AU',
'FSF0007AU',
'FSF0012AU',
'FSF0013AU',
'FSF0014AU',
'FSF0017AU',
'FSF0018AU',
'FSF0019AU',
'FSF0020AU',
'FSF0021AU',
'FSF0022AU',
'FSF0023AU',
'FSF0024AU',
'FSF0025AU',
'FSF0028AU',
'FSF0032AU',
'FSF0033AU',
'FSF0035AU',
'FSF0040AU',
'FSF0041AU',
'FSF0042AU',
'FSF0043AU',
'FSF0044AU',
'FSF0045AU',
'FSF0046AU',
'FSF0048AU',
'FSF0075AU',
'FSF0077AU',
'FSF0090AU',
'FSF0136AU',
'FSF0144AU',
'FSF0145AU',
'FSF0157AU',
'FSF0158AU',
'FSF0159AU',
'FSF0169AU',
'FSF0180AU',
'FSF0181AU',
'FSF0182AU',
'FSF0184AU',
'FSF0190AU',
'FSF0191AU',
'FSF0193AU',
'FSF0195AU',
'FSF0197AU',
'FSF0213AU',
'FSF0223AU',
'FSF0224AU',
'FSF0239AU',
'FSF0240AU',
'FSF0248AU',
'FSF0251AU',
'FSF0252AU',
'FSF0259AU',
'FSF0261AU',
'FSF0264AU',
'FSF0265AU',
'FSF0267AU',
'FSF0270AU',
'FSF0271AU',
'FSF0273AU',
'FSF0275AU',
'FSF0277AU',
'FSF0282AU',
'FSF0287AU',
'FSF0293AU',
'FSF0297AU',
'FSF0301AU',
'FSF0304AU',
'FSF0311AU',
'FSF0317AU',
'FSF0318AU',
'FSF0352AU',
'FSF0355AU',
'FSF0375AU',
'FSF0376AU',
'FSF0383AU',
'FSF0391AU',
'FSF0400AU',
'FSF0401AU',
'FSF0402AU',
'FSF0403AU',
'FSF0404AU',
'FSF0405AU',
'FSF0406AU',
'FSF0407AU',
'FSF0414AU',
'FSF0415AU',
'FSF0418AU',
'FSF0439AU',
'FSF0443AU',
'FSF0453AU',
'FSF0454AU',
'FSF0462AU',
'FSF0464AU',
'FSF0469AU',
'FSF0479AU',
'FSF0481AU',
'FSF0485AU',
'FSF0486AU',
'FSF0490AU',
'FSF0491AU',
'FSF0492AU',
'FSF0493AU',
'FSF0494AU',
'FSF0495AU',
'FSF0496AU',
'FSF0497AU',
'FSF0500AU',
'FSF0501AU',
'FSF0504AU',
'FSF0505AU',
'FSF0512AU',
'FSF0513AU',
'FSF0515AU',
'FSF0516AU',
'FSF0518AU',
'FSF0520AU',
'FSF0521AU',
'FSF0522AU',
'FSF0523AU',
'FSF0528AU',
'FSF0529AU',
'FSF0530AU',
'FSF0531AU',
'FSF0532AU',
'FSF0533AU',
'FSF0534AU',
'FSF0535AU',
'FSF0536AU',
'FSF0537AU',
'FSF0538AU',
'FSF0539AU',
'FSF0545AU',
'FSF0547AU',
'FSF0548AU',
'FSF0550AU',
'FSF0551AU',
'FSF0553AU',
'FSF0556AU',
'FSF0557AU',
'FSF0558AU',
'FSF0560AU',
'FSF0564AU',
'FSF0566AU',
'FSF0567AU',
'FSF0568AU',
'FSF0569AU',
'FSF0570AU',
'FSF0571AU',
'FSF0574AU',
'FSF0576AU',
'FSF0578AU',
'FSF0580AU',
'FSF0581AU',
'FSF0582AU',
'FSF0583AU',
'FSF0584AU',
'FSF0585AU',
'FSF0586AU',
'FSF0588AU',
'FSF0590AU',
'FSF0596AU',
'FSF0597AU',
'FSF0598AU',
'FSF0599AU',
'FSF0605AU',
'FSF0606AU',
'FSF0607AU',
'FSF0608AU',
'FSF0609AU',
'FSF0610AU',
'FSF0616AU',
'FSF0618AU',
'FSF0620AU',
'FSF0622AU',
'FSF0623AU',
'FSF0624AU',
'FSF0626AU',
'FSF0628AU',
'FSF0629AU',
'FSF0636AU',
'FSF0641AU',
'FSF0642AU',
'FSF0643AU',
'FSF0647AU',
'FSF0648AU',
'FSF0656AU',
'FSF0657AU',
'FSF0678AU',
'FSF0679AU',
'FSF0681AU',
'FSF0682AU',
'FSF0683AU',
'FSF0684AU',
'FSF0685AU',
'FSF0686AU',
'FSF0688AU',
'FSF0755AU',
'FSF0760AU',
'FSF0768AU',
'FSF0770AU',
'FSF0773AU',
'FSF0828AU',
'FSF0829AU',
'FSF0832AU',
'FSF0835AU',
'FSF0837AU',
'FSF0838AU'

)

DECLARE c CURSOR FORWARD_ONLY FOR SELECT ProductID from @LookupProducts group by ProductID
DECLARE @CurProdID uniqueidentifier
OPEN c
FETCH c INTO @CurProdID

DECLARE @FoundPrice decimal(18,4)
DECLARE @FoundDate datetime

-- Start Date
DECLARE @SDate datetime
--set @SDate = dateadd(day,14, getdate())
set @SDate = getdate()

WHILE (@@FETCH_STATUS=0) BEGIN
-- Fetch Unit Prices
EXEC dbo.GetUnitPrice @CurProdID, @SDate, @FoundPrice output, @FoundDate output
update @LookupProducts SET up_price = isnull(@FoundPrice,0), up_date = @FoundDate where ProductID = @CurProdID

set @FoundPrice = null
set @FoundDate = null

FETCH c INTO @CurProdID
END

CLOSE c
DEALLOCATE c

--SELECT APIRCode, Description,up_price, convert(varchar(12), up_date, 113) as 'up_date' FROM @LookupProducts

DECLARE @ClientsHolding table
(
ID uniqueidentifier primary key ,
EntityGroupName varchar(255),
ClientSurname varchar(255),
ClientGivenName varchar(255),
ClientDOB datetime,
SpouseSurname varchar(255),
SpouseGivenName varchar(255),
SpouseDOB datetime,
Addr1 varchar(255),
Addr2 varchar(255),
Suburb varchar(255),
PostCode varchar(255),
Adviser varchar(255),
ValueOfHolding money,
ILCN varchar(255),
Note varchar(5000)
)

insert into @ClientsHolding (
[ID],
EntityGroupName,
ClientSurname,
ClientGivenName ,
ClientDOB ,
SpouseSurname ,
SpouseGivenName ,
SpouseDOB ,
Addr1 ,
Addr2 ,
Suburb ,
PostCode ,
Adviser ,
ILCN
)
SELECT --Top 500
e.id,
e.EntityGroupName,
ec.SurName ,
ec.GivenName ,
ec.DOB ,
es.SurName ,
es.GivenName ,
es.DOB,
ec.Addr1,
ec.Addr2,
ec.Suburb,
ec.PostCode,
u.UserName,
Isnull(ec.CustomerID,'N/A')

from EntityGroups e,
ent_clients ec,
ent_spouses es,
users u
where
e.id= ec.EntityGroupID
and isnull(e.deleted,0)=0
and e.UserID = u.ID
and e.id *= es.EntityGroupID

order by 1;

DECLARE c CURSOR FORWARD_ONLY FOR SELECT ID from @ClientsHolding group by ID
DECLARE @CurEntityID uniqueidentifier
OPEN c
FETCH c INTO @CurEntityID

DECLARE @value money
set @value = 0
WHILE (@@FETCH_STATUS=0) BEGIN

DECLARE c1 CURSOR FORWARD_ONLY FOR SELECT ProductID , APIRCode, up_price from @LookupProducts
DECLARE @APIRCode varchar(255)
DECLARE @up_price money
OPEN c1
FETCH c1 INTO @CurProdID, @APIRCode, @up_price

DECLARE @ProductQuantity decimal(18,4)
DECLARE @CurAssetID uniqueidentifier

WHILE (@@FETCH_STATUS=0) BEGIN

-- TransSumConfirmed up to ValuationDate
SELECT @CurAssetID = AS_ID from P_Assets where AS_EN in (select EntityID from ent_clients where EntityGroupID= @CurEntityID union select EntityID from ent_spouses where EntityGroupID= @CurEntityID)
and AS_PR = @CurProdID
--print isnull(convert(varchar(100),@CurAssetID), 'n/a') + ' CurEntityID = ' + convert(varchar(100), @CurEntityID) + ' CurProdID =' + convert(varchar(100), @CurProdID)
select @ProductQuantity = sum(case when t.tx_ts in (1,3) then tx_quantity else 0 end)
from dbo.p_trans t join dbo.p_transtypes tt on t.tx_tp = tt.tp_id where tx_as = @CurAssetID
Update @ClientsHolding set Note = isnull(Note,'') + @APIRCode + ' Qty = ' + isnull(convert(varchar(50), @ProductQuantity),'0') + ' UP = $' + isnull(convert(varchar(50), @up_price),'0') + char(10) + char(13)
where ID= @CurEntityID
set @value = isnull(@value,0) + isnull(@ProductQuantity * @up_price, 0)
set @ProductQuantity = 0
set @CurAssetID = null
FETCH c1 INTO @CurProdID, @APIRCode, @up_price
END

CLOSE c1
DEALLOCATE c1

Update @ClientsHolding set ValueOfHolding = @value where ID= @CurEntityID
set @value = 0
FETCH c INTO @CurEntityID
END
CLOSE c
DEALLOCATE c

SELECT * FROM @ClientsHolding where ValueOfHolding > 0;

I get this error :

Server: Msg 8152, Level 16, State 9, Line 461
String or binary data would be truncated.
The statement has been terminated.

How can I fix this ?/ modify query to get around this issue ?

Thanks in advance:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 02:16:50

Look at insert statements to see if the columns in destination table has enough length to hold values that are populated. compare their lengths with source fields and spot any differences. the error is because you're trying to assign a value to a character field which it cant accomodate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources