Date format difference in xp_cmdshell

  • Because it's a bit of a fuss using SSIS to create Maintenance Plans for each new SQL Server, we decided to make our own jobs. We're using xp_cmdshell to find old backup files to delete. Now a problem has arrisen.

    Some SQL Servers are giving a different date format in the xp_cmdshell output. There are differences in version and collation between SQL Servers, but there is no consistency between these differences and the difference in date formatting.

    We have taken two of the SQL Servers for further investigation, but simply cannot figure out where the date format is chosen.

    A 'select getdate()' executed via a SQL Server job gives the same format on both:

    2009-02-19 12:35:29.183

    A 'date /t' in a CMD box gives the same format on both (Dutch):

    do 19-02-2009

    A 'date /t' in a SQL Server job of with xp_cmdshell yields a difference:

    do 19-02-2009

    Thu 02/19/2009

    The SQL Server job runs as 'SQL Server Agent account' on both.

    Both have the same default language (US English).

    The SQL Server giving the Dutch result has no logins with Dutch as a default language.

    We really would like to find out where the format is chosen, so we can either change this, or interactively check for it and work around it.

    Any ideas?

  • you could use xp_delete_file and forget about all of that 😉


    * Noel

  • I didn't know about xp_delete_file. It might be a solution for us, but I see it doesn't work for txt files (not surprising, as cleaning up txt files in SSIS Maintenance Plans doesn't work either), which we also need to do. I'm also worried about many reports on the web about xp_delete_file being unreliable in results, being unsupported and not giving any decent output (i.e. not reporting if it removed anything, let alone what).

    So, if possible, I'd like some more control over our jobs. Thanks for the tip though!

    Anyone else have any ideas?

  • Hope you find help at:http://www.sqlservercentral.com/Forums/Topic649456-146-2.aspx#bm652385

    MJ

  • ddonck (2/20/2009)


    I didn't know about xp_delete_file. It might be a solution for us, but I see it doesn't work for txt files (not surprising, as cleaning up txt files in SSIS Maintenance Plans doesn't work either), which we also need to do. I'm also worried about many reports on the web about xp_delete_file being unreliable in results, being unsupported and not giving any decent output (i.e. not reporting if it removed anything, let alone what).

    So, if possible, I'd like some more control over our jobs. Thanks for the tip though!

    Anyone else have any ideas?

    SSIS uses xp_delete_file !

    It should work you must patch your server to at least SP2 because of bugs released with several RTM and SP1 versions.

    Unreliable with "network" files yes with local files no.

    Unsupported ... hehe I have lost the count on how many "supported" features in SQL Server have been changed, broken and finally discontinued.


    * Noel

  • All thanks for the advice.

    Noeld, I realise the SSIS packages use xp_delete_file, but our old SSIS Maintenance Plans didn't clean up old text files either. We are at SP2, but maybe not yet at to correct update after that.

    You are right about MS having a nasty habbit of changing all kinds of stuff. Food for thought.

    Well, we're still debating whether or not we're going to use xp_delete. But should we choose not to use it, I've managed to figure out a way to work around it. Since both date formats have different length weekday (English = Thu = 3, Dutch = do = 2), the year starts at a different position. So by using charindex to find the position of the year, I can determine which format is in use and automatically create my code as needed. Though I must admid this makes for some very complex coding.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply