September 13, 2010 at 11:03 am
when i run the update script below i gett his error i cannot fix it.
the first time i ran it it ran fine, then it never ran again. just this error.
Code:
update RM00301
set SPRSNSLN = left('Batista, Yuliana Mer',20),
ADDRESS1= 'Jewellery Salespeople',
ADDRESS2= '8/10/10',
CITY = '174',
[STATE] = '20',
ZIP = '17828.9',
COUNTRY = 'COMM'
where SLPRSNID = 817
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Split ' to data type int.
There is a trigger on the table
Trigger Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[zDT_RM00301U] ON [dbo].[RM00301] AFTER UPDATE
AS
set nocount on
BEGIN
UPDATE dbo.RM00301
SET DEX_ROW_TS = GETUTCDATE()
FROM dbo.RM00301, inserted
WHERE RM00301.SLPRSNID = inserted.SLPRSNID
END
set nocount off
September 13, 2010 at 11:58 am
Please provide sample data and DDL. This will help people better find the issue.
As it stands - it looks like you have some bad data.
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
September 13, 2010 at 12:08 pm
I would agree with Jason. Typically this occurs when you have in implicit conversion taking place and there is data in the table or code that is cannot be an int.
So if you store purchase orders as varchar and you have
123455
123456
123457
123457A
The last item would fail on an implicit conversion.
Note: Edited code to clean it up
September 13, 2010 at 12:20 pm
Yes, the table structure would be helpful. To easily see the structure, use
sp_help RM00301
See the block that begins with the column Coliumn_Name and copy the first 7 columns.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
September 13, 2010 at 12:30 pm
npeters 86796 (9/13/2010)
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[zDT_RM00301U] ON [dbo].[RM00301] AFTER UPDATE
AS
set nocount on
BEGIN
UPDATE dbo.RM00301
SET DEX_ROW_TS = GETUTCDATE()
FROM dbo.RM00301, inserted
WHERE RM00301.SLPRSNID = inserted.SLPRSNID
END
set nocount off
As to where that 'split ' is coming from, I'm not sure, but it might be worth checking 00301 SLPRSNID for duplicates and see if you're picking up more then one record at a time.
Side Note:
Hey guys, that's an after update at the same table it's triggered off of with no restrictions. This risks infinite recursion, no? Always thought you'd need either to use INSTEAD OF or a column check for a change in this, or make sure you had specific DB settings (server? I don't have '08 available here) always set so triggers couldn't recurse.
I don't want to derail this completely, since this is an odd issue, but can someone fire me off a PM on this if it's more complicated then a one-two line answer?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 13, 2010 at 12:34 pm
I believe i got the answer.
I do not know why this works but by placing the SLPRSNID = '104' instead of 104 works !!
Thanks !!
September 13, 2010 at 12:39 pm
Datatype and implicit conversion.
It looks like your ID column is not an Integer and is a string of some type.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply