CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-agent
reverterez
reverterez

Reputation: 3

SQL Server stored procedure works but failes while running job

I have a problem with my stored procedure in SQL Server 2017 Developer. It gets file's modification date using xp_cmdshell, returns varchar into temp table and trying to convert this to date. The stored procedure is working when I execute it in SSMS manually, but fails when I put it into a job step.

Error from Job History:

Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241)

I have marked the fragment of code that fails (when I tried to execute SP in job without this, it executed successfully).

When I execute only the "problem" code, it's working fine and returns such data:

Code:

select top(1) cast(left(mdate,20) as date) data_ 
from t_lomag_temp_table

Results:

2018-03-14

Code for stored procedure:

declare @lomag_bak_file_source nvarchar(400) = /*source folder*/
declare @restore_status int
declare @bak_list as table (id int identity
                          , plik varchar(200)
                          , data_bak date)
declare @baza_nazwa varchar(200)
declare @xp_cmdshell_dir varchar(1000)
declare @counter int
declare @loop_limit int
declare @bak_files_status int

set @restore_status = 0
set @bak_files_status = 0
set @counter = 1
;

truncate table t_lomag_temp_table
;

insert into @bak_list
    select concat(dl.baza,'_db.bak'), null
    from t_lomag_database_list dl
;

select @loop_limit = max(id) from @bak_list

while @counter <= @loop_limit
begin
    select @baza_nazwa = bl.plik 
    from @bak_list bl 
    where bl.id = @counter

    set @xp_cmdshell_dir = concat('dir ',@lomag_bak_file_source,@baza_nazwa)

    insert t_lomag_temp_table 
        exec master.dbo.xp_cmdshell @xp_cmdshell_dir

    set rowcount 5 

    delete from t_lomag_temp_table 

    set rowcount 0

    /* JOB PROBLEM CODE varchar to date*/
    update @bak_list 
    set data_bak = x.data_ 
    from (select top(1) cast(left(mdate, 20) as date) data_
          from t_lomag_temp_table) x
    where @baza_nazwa = plik

    set @counter = @counter + 1
end
;

begin transaction

delete from logistyka.dbo.t_lomag_restore_dates

commit transaction
;

begin transaction
    insert into logistyka.dbo.t_lomag_restore_dates
        select 
            plik, data_bak, 1, 0
        from 
            @bak_list
    ;

    select @bak_files_status = min(rd.date_status_fl)
    from logistyka.dbo.t_lomag_restore_dates rd
;

commit transaction
;

Upvotes: 0

Views: 1244

Answers (2)

Jim Horn
Jim Horn

Reputation: 889

Just for kicks and giggles run this and see if any values that cannot be converted to a date (and are throwing the error) are returned. If so, then figure out how to handle these values gracefully, either by running an UPDATE statement to make them date-convertable, or adding a WHERE clause to exclude them from your query.

SELECT mdate FROM t_lomag_temp_table WHERE ISDATE(left(mdate,20))= 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You have a bad date. You can find it using try_convert() or try_cast():

select mdate
from t_lomag_temp_table
where try_convert(date, left(mdate, 20)) is null;

Your code succeeds because it is -- presumably -- easy to find valid dates in the table. The stored procedure fails because it looks at more of the values.

Upvotes: 1

Related Questions