none
Trying to run T-SQL Script from SQL Server Agent job and not sucessfully running and getting this error " Message Executed as user: NT AUTHORITY\NETWORK SERVICE. Error converting data type varchar to numeric. "

    Question

  • I am trying to Run following script in SQL Server Agent:

    UPDATE SecondaryIncome 
    SET SecondaryIncome.RateLockInvestor = [Inv_name], SecondaryIncome.RateLockPrice = [ConfPrice], SecondaryIncome.RateLockInvPrice = [Inv_Price], SecondaryIncome.RateLockCommitNum = [Inv_Commitment], SecondaryIncome.RateLockInfoPosted = GETDATE()
    FROM SecondaryIncome INNER JOIN RateLock ON SecondaryIncome.LoanNum = RateLock.ApplicantId
    WHERE (((SecondaryIncome.RateLockInvestor) Is Null) AND ((SecondaryIncome.PurchasedDate) Is Not Null));

    And Getting Following Error: Executed as user: NT AUTHORITY\SYSTEM. Error converting data type varchar to numeric. [SQLSTATE 42000] (Error 8114).  The step failed.


    • Edited by Rocket ST Friday, November 08, 2013 11:54 PM
    Friday, November 08, 2013 11:53 PM

Answers

All replies

  • Did you compare the columns from tables SecondaryIncome and RateLock to see if they have same data type? Seems there is one Varchar Column being assigned to a numeric column.


    Regards, Ritta Singh

    Saturday, November 09, 2013 12:54 AM
  • It seems that the columns in the table are defined as VARCHAR and have alphanumeric value(s) that can not be implicitly converted to any numeric data type.

    I'm assuming that the following columns should have numeric but may have wrong data. I might have added few extra columns or missed some so please verify yourself too.

    The script checks if the column has characters except numbers -

    -- SecondaryIncome columns
    select ratelockprice from secondaryincome where PATINDEX('%[^0-9]%', ratelockprice) > 0
    select ratelockinvprice from secondaryincome where PATINDEX('%[^0-9]%', ratelockinvprice) > 0
    select ratelockcommitnum from secondaryincome where PATINDEX('%[^0-9]%', ratelockcommitnum) > 0
    select loannum from secondaryincome where PATINDEX('%[^0-9]%', loannum) > 0
    
    -- RateLock columns
    select [confprice] from ratelock where PATINDEX('%[^0-9]%', [confprice]) > 0
    select [inv_price] from ratelock where PATINDEX('%[^0-9]%', [inv_price]) > 0
    select [inv_commitment] from ratelock where PATINDEX('%[^0-9]%', [inv_commitment]) > 0
    select [applicantid] from ratelock where PATINDEX('%[^0-9]%', [applicantid]) > 0


    - Aalam | (Blog)

    Saturday, November 09, 2013 1:42 AM
  • Two suggestions:

    1. Use MERGE instead of UPDATE
    2. Use explicit convert instead of implicit

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, November 09, 2013 4:10 AM