SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help me find the offending data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

766 Posts

Posted - 11/19/2013 :  14:47:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 11/19/2013 :  14:57:47  Show Profile  Reply with Quote
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?
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 11/19/2013 :  15:30:48  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 11/19/2013 :  17:09:08  Show Profile  Reply with Quote
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 11/21/2013 :  10:40:29  Show Profile  Reply with Quote
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!

Edited by - Hommer on 11/21/2013 10:41:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/22/2013 :  07:11:14  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000