May 18, 2013 at 10:43 pm
Hi All,
I get the error above when trying to update a table.
UPDATE Table1
SET Field1='XYZ',
WHere Field2='8880348233'
Field2 is Varchar(25)
I know why it happens (convert to int when compared) but I cannot find any workaround.. no CAST or Convert work.
This is working (even though it is a comparison also!)
Select *
from Table1
where Field2='8880348233'
Looks like it doesn't work only for Update.
Any suggestions would be highly appreciated!
May 19, 2013 at 2:31 am
This is so straightforward.
Maybe your Field2 has some invisible characters. Could you post the error message?
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
May 19, 2013 at 3:14 am
Please post the definition of the table and check for triggers.
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
May 19, 2013 at 8:17 am
Hi,
Thanks for your reply.
Just a quick background - this is a dim table that gets updated via ETL process, the ETL process is a few years old, nothing had changed with its logic, source are txt files, targets are DB tables, among them, this one DIM table.
I suspect Field2 (an ID field) that was defined as varchar(25) in the DIM table when this whole process was first defined and built.
I'm trying to find a way to over come this problem without changing the table definition:
CREATE TABLE Table1(
[ID] [varchar](25) NOT NULL,
[Name] [varchar](250) NOT NULL,
[Client] [char](3) NOT NULL,
[AllowUpdates] [char](1) NOT NULL,
[InsUpdDelFlag] [char](1) NOT NULL,
[InsertDate] [datetime] NOT NULL,
[UpdateDate] [datetime] NULL,
[FileName] [varchar](100) NOT NULL,
CONSTRAINT [Table1PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE Table1 WITH CHECK ADD CONSTRAINT [CkTable1AllowUpdates] CHECK (([AllowUpdates]='N' OR [AllowUpdates]='Y'))
GO
ALTER TABLE Table1 CHECK CONSTRAINT [CkTable1AllowUpdates]
GO
I have a trigger on the table.
I can do this:
select *
from Table1
where ID='8880348233'
I get a result set for that but it gives me an error message for this:
select *
from Table1
where convert(numeric(20,0),ID)=8880348233
or
select *
from Table1
where cast(ID as bigint)=8880348233
then I get:
Error converting data type varchar to numeric.
or
Error converting data type varchar to bigint.
any ideas?
Thanks!!
May 19, 2013 at 8:28 am
And the definition of the trigger (which is the prime suspect for the update failing)?
As for why the selects fail when explicitly converted, probably because there's non-numeric values in the columns, not surprising if there are. There's no need to explicitly convert though, because the column is a varchar and the literal is also a varchar.
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
May 19, 2013 at 11:56 am
ALTER Trigger [Table1UpdTrg]
On [Table1]
After Update
As
Declare @msgProfileNameVarchar(100)
Declare @msgRecipientsVarchar(100)
Declare @msgBodyVarchar(255)
Declare @msgSubjectVarchar(100)
Declare @IDInteger
Declare @NameOldVarchar(250)
Declare @NameNewVarchar(250)
Select @msgProfileName='Administrators'
Select @msgRecipients='Admin@xyz.com'
-- Disallow updates to any PK columns:
If Update( ID )
Begin
Rollback Transaction
RaisError('ERROR: Updates to Primary Key Columns Not Allowed',16,1)
Return
End
-- Send Warning if Name is updated:
If Update(Name)
Begin
Declare MyCursor Scroll Cursor For
Select ID, Name
From Inserted
Open MyCursor
Fetch Next From MyCursor
Into @ID, @NameNew
While @@Fetch_Status = 0
Begin
Select @NameOld = Name
From Deleted
Where ID = @ID
If @NameNew <> @NameOld
Begin
Select @msgSubject=@@servername+': WARNING: Updated Name'
Select @msgBody='Name for ID ['+Convert(Varchar(11),@ID)+']'
+' Updated From'
+Char(10)+'['+@NameOld+'] To '
+Char(10)+'['+@NameNew+']'
+' By '+suser_name()+' On '+@@servername
Exec msdb.dbo.sp_send_dbmail
@profile_name=@msgProfileName,
@recipients=@msgRecipients,
@body=@msgBody,
@subject=@msgSubject
End
Fetch Next From MyCursor
Into @ID, @NameNew
End
Close MyCursor
DeAllocate MyCursor
End
--
-- Set UpdateDate to Current Date for updated rows:
--
Update Table1
Set UpdateDate = getdate()
From Inserted a
,Table1 b
Where b.ID = a.ID
=============================================================
ID is definded as Int in the trigger only for updates.. and you are right, I just saw values that include actual charachters..
May 19, 2013 at 12:37 pm
Ow, that trigger is nasty.
Yes, the declaration of ID as int in the trigger is the cause of the update error. Change it to match the table and the updates will no longer error out.
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
May 19, 2013 at 4:04 pm
GilaMonster (5/19/2013)
Ow, that trigger is nasty.
that's an understatement...:-P
mayabee: Cursors and procedural code inside triggers are a really bad idea, consider writing the IDs to another table and have a separate process to pick those up and email them. Email is not an instantaneous process, so a small delay while a scheduled job processes it should not be of concern. (And if it is, there are still ways to handle it without emailing from a trigger)
Oh and don't forget that Update(ID) being true doesn't mean it is being altered (as you have checked for actual updates with the name column I am wondering why you didn't do the same for the PK?)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 19, 2013 at 7:32 pm
Changed it to Varchar(25) and it works.
Thanks for the help!
I couldn't agree more.. nasty trigger and in the near future my plan is to move to a different ETL tool then I'd be able to design the process from scratch.
Thank you so much for your help! 🙂
Have a great week!
May 19, 2013 at 7:33 pm
Hey there, thanks! yes indeed, as I said, planning on moving to a different ETL tool then I'd take the time to design the whole process from scratch and will get the chance to build new tables/triggers.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply