February 19, 2009 at 9:35 am
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?
February 19, 2009 at 11:39 am
you could use xp_delete_file and forget about all of that 😉
* Noel
February 20, 2009 at 2:22 am
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?
February 20, 2009 at 3:47 pm
Hope you find help at:http://qa.sqlservercentral.com/Forums/Topic649456-146-2.aspx#bm652385
MJ
February 20, 2009 at 4:14 pm
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
February 24, 2009 at 8:43 am
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