Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update statistics job is failing Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 9:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 961, Visits: 4,989
I've set up a Maintenance Plan (yeah, yeah, code it up myself and create the Agent job, I know... ) to run an Update Statistics once a week on a server here.

Well, the past couple times its run, it's coming back with an error, and I'm not sure how to resolve it:
Executing the query "UPDATE STATISTICS [dbo].[TABLENAME]
WITH FULLSCAN
" failed with the following error: "Error converting data type varchar to numeric.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

No, the table in question isn't really named "TABLENAME." The table in question does have quite a few computed columns, but there's nothing in BOL to indicate that these should be a problem. Yes, this table has quite a few columns (106 of which 39 are computed,) but it was (I believe) inherited from a mainframe system.

I know I could exclude this DB from the update job, but I'd rather not. Further, it has worked in the recent past (about 3 weeks back,) and the dev responsible for it hasn't made any schema changes in months.

Any suggestions?

Thanks,
Jason
Post #1502206
Posted Monday, October 7, 2013 9:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
DBCC CheckDB WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

If you do a SELECT * FROM TableName do you get the same error?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1502217
Posted Monday, October 7, 2013 9:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 961, Visits: 4,989
While I do have an Agent job that runs DBCC CheckDB once a week as well (at a time that doesn't impact any other jobs,) I'll manually run it as you suggest this weekend (I have to wait that long, there's Agent jobs that fire every 30 minutes to load data throughout the week. They don't run on the weekends...)

As for the select *, it's about 3/4 done (8.4 million rows in the table) with no problems so far...

And it just finished with the same error. Sounds like the DBCC CheckDB job isn't catching this problem. I'll run your code this weekend, and see what it spits up.
Post #1502222
Posted Monday, October 7, 2013 9:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Update stats on that table manually, see if you get the same error. If so, update stats one by one, see which one gives you the error. Look at what columns that stats object is defined on.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1502227
Posted Monday, October 7, 2013 9:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 961, Visits: 4,989
That's going to be fun...


56 auto-created stats to check, plus one on the primary key. Well, no one said this job would be easy!

Hmm, time to see if I can do this by RBARing through an output of sys.stats, so I can automate this some...


Thanks much Gail!
Post #1502234
Posted Monday, October 7, 2013 10:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 961, Visits: 4,989
OK, found a better way then the cursor, and already tried it.

Used this:
select
name
, STATS_DATE(object_id, stats_id) as LastUpdated
from sys.stats
where object_id = 735341684
order by LastUpdated asc;

to see which stats hadn't been updated this weekend, then manually tried to update each of those. The one that failed is an auto-created stats on one of the computed columns.

The datatype of the column is Numeric, and the computed text is: (CONVERT([numeric],[ColumnName],(0))/(100.0))
Now, I checked ColumnName, and I think I've found the problem (time to give the dev a headache! ) ColumnName is a varchar(10). So I'm betting, someone, somewhere, entered something like 1234ABC.123 in there.

Time to do a select ColumnName from... and see what comes up.

And, found the problem child...
Somehow a value of '16923951 0' got entered...
Chatting with the Dev about making the computed column better able to deal with things like this...
Post #1502267
Posted Tuesday, October 8, 2013 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 2,837, Visits: 3,954
jasona.work (10/7/2013)
Somehow a value of '16923951 0' got entered...
while you insert value ( '16923951 0' ) into it.
it will throw error like "String , binary data would be truncated"


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1502523
Posted Tuesday, October 8, 2013 5:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Bhuvnesh (10/8/2013)
jasona.work (10/7/2013)
Somehow a value of '16923951 0' got entered...
while you insert value ( '16923951 0' ) into it.
it will throw error like "String , binary data would be truncated"


That's a 10-character string, it'll insert without error into a varchar(10).

It won't convert to numeric, but that will only be done when someone queries the calculated column, not at insert time, so the insert will succeed but subsequent selects will fail.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1502563
Posted Tuesday, October 8, 2013 7:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 961, Visits: 4,989
Once more, thanks Gail!

The Dev put together a function to check for non-numeric characters, and if found, not create the computed value. In theory, the application which uses this DB is eventually going away, but who knows when that might really be...
Post #1502605
Posted Tuesday, October 8, 2013 7:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
A better solution might be to change the column to numeric. Or is that not possible?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1502612
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse