Help me find the offending 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

General SQL Server Forums

New to SQL Server Programming

Help me find the offending data

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-11-19 : 14:47:07

Hi,

I have imported a set of data via ssis into this staging table assets_t. Now I want to merge it into the destination table assets.

This next select gave me the data in the results tab, as well as the error under the messages tab in ssms.

select o.id, t.id
,o.[assettag], t.[assettag]
,o.[serial],t.[serial]
from assets o join assets_t t on o.id = t.id
where o.[assettag]<> t.[assettag]
or
o.[serial]<>t.[serial]

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value '", theyare"' to data type int.

Then when I tried this next update, all I got is the same error.
update o
set o.[assettag]= t.[assettag]
,o.[serial]=t.[serial]
from assets o join assets_t t on o.id = t.id
where o.[assettag]<> t.[assettag]
or
o.[serial]<>t.[serial]

I couldn't find this offending data string.

This is sql08r2. thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-19 : 14:57:47

The error message kind of says it all. My guess is that one of the columns you are joining on is an INT in one table and a string (VARCHAR,CHAR, etc.) in the other.

Can you verify that the data types match in both tables?
Can you supply the DDL of the two tables?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-11-19 : 15:30:48

I can tell the different data type columns, and I want to do a convert when I update.

But I want to fix the data if I can find it.

I guess I need a select that will give me the column names from a known table with a given value.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-19 : 17:09:08

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-11-21 : 10:40:29

quote:
Originally posted by Lamprey

The error message kind of says it all. My guess is that one of the columns you are joining on is an INT in one table and a string (VARCHAR,CHAR, etc.) in the other.

Can you verify that the data types match in both tables?
Can you supply the DDL of the two tables?

Thank you for your reply!

I had found the bad data on the above issue.

Here is an example, which will demonstrate my point and the reason I did not post the DDL at first place.

The data types dose not match in both tables.

CREATE TABLE [dbo].[Assets](...
[inservdt] [datetime] NULL,...)
CREATE TABLE [dbo].[Assets_Mac](...
[PO Date] [varchar](50) NULL,...)

Here is my insert

Insert into Assets ([inservdt])
Select Convert(datetime, [PO Date], 101) from Assets_Mac

Then I got
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

So that is what I meant that I am after the bad data. And in this case, I found it by

select [po date] from assets_MAC order by [po date] desc
then fixed the data
update assets_MAC set [po date] ='' where [po date] ='N/A'

This is part of my job, and sometimes, I came here for "magic".

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 07:11:14

in that case you could just find offending data using


select [po date] from assets_MAC 
where isdate([po date])=0
order by [po date] desc

also changing it to N/A will not solve the issue if your attempt is to dump it to datetime field
for that you've to either make it NULL or set it to a defauult date value (like 19000101)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources