Problem with XP_CMDSHELL ''DIR .....''
-
If I execute command EXEC master..XP_CMDSHELL 'DIR ....\*.*' in query analyzer, file date in result string is in wrong format mm/dd/yyyy
. When I execute DIR command in CMD window I get file date in correct format dd.mm.yyyy 
, like settings in win. regional settings.This problem apper in MSDE 2000. Anybody know solution.
Regards
Uros
-
Can you post the exact code that you are having trouble with? I have done a lot of work with xp_cmdshell and should be able to help you out.
Live to Throw
Throw to Live
Will Summers -
Statement is part of code where determining which backup file is for delete. All code works fine on 99% of PC's, but now I install MSDE 2000 and my program to new PC with WinXP Pro and code stop working becouse file date is wrong. I get 10/30/2006 (30 october), but in my regional settings date format is set to dd.mm.yyyy (30.10.2006). I get error becouse month with number 30 does not exist. The date delimiter is wrong too - '/' instead '.'.
I don't know where problem is. Maybe in OS but why in CMD window DIR work fine and return date in dd.mm.yyyy format. When execute DIR with XP_CMDSHELL return string is wrong becouse date is in mm/dd/yyyy format.
Settings in Regional settings is set just like must be (in Slovenian), all program works fine and all date and time thinks in SQL server work fine. Collation in SQL server is Slovenian CI CP1250.
So, I decide that problem is hide in SQL MSDE 2000.
part of my code:
....... some SQL statements .....
-- @wImenik is parameter C:\some_dirname\*.*
CREATE TABLE #DOS_RS
(DOS_RS VARCHAR(4000)
SET @CMD = 'DIR "'+@wImenik+'*.*"'
INSERT INTO #DOS_RS
EXEC MASTER..XP_CMDSHELL @CMD
....... work on #DOS_RS .....
All code is wery long and I think is not necessary here becouse code work OK if date format in dd.mm.yyyy.
Uros
-
One thing that you could try is to add the /n to the DIR command. That will display the date as the first column in the results.
If the row has a period in it, see if a CONVERT(DATETIME,string,104) will work on it. The 104 will look for a date in the format dd.mm.yy.
If the string doesn't have a period in it, then just use the old way to get the date.
I can't write the exact code for you cause it seems the Slovenian output is different from USA.
Let me know if this helps. I know how frustrating working with DIR through xp_cmdshell can be.
Live to Throw
Throw to Live
Will Summers -
No, nothing help.
I need universal solutions, becouse I newer know what format user use on PC. Problem is in string. If I write a function which control the date string I newer know on string '12.10.2006' is this date in december or october, in both way is OK, no error, but which date is right, if day is bigger then 12 error appear.
CONVERT return an error: Server: Msg 242, Level 16, State 3, Line 1, The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I probably must write function and first check if date '30.10.2006' is OK, if no error then I know the date format is dd.mm.yyyy then go further ......
I will write this function, but I believe to easyest solutions. And strange return date format still exist, why DIR in XP_CMDSHELL return this format, this is a question. Where they get 'mm/dd/yyyy' and date delimiter '/'. I think, this is a default value (I don't know from where) and on same conditionis this default is used instead regional settings or SQL colalation settings. Perhaps this is a bug?
Slovenian output is different from USA and we have problems with date format and some letters (š,c,ž,...) from begining ...
Uros
-
No, I can't write function becouse in SQL server date format is OK, so I newer know what format DIR function return, is '10.12.2006' october or december.
Uros
-
When you execute command EXEC master..XP_CMDSHELL 'DIR ....\*.*' in query analyzer you run DIR from CMD shell opened from Server, when you execute DIR command in CMD window you must have opened it from your local computer. I guess they are different machines with different local settings.
Go to Server (probably using Remote Desctop), run CMD and execute DIR. You must see the same result as from QA.
To change format of date returned by master..XP_CMDSHELL 'DIR ....\*.*' you need to change Windows Locale settings on the Server where your SQL Server is started.
If you want to control the settings or at least to know which are the current ones google "xp_regread". It's extended SP which reads Windows registry and may help you to figure out which date format is used by Windows and interprete the strings returned by DIR accordingly.
_____________
Code for TallyGenerator -
Perhaps if you told us the rest of the story as to why you are trying to do a DIR, we might even be able to come up with a solution that doesn't require xp_CmdShell or the "SA" privs it requires...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sergiy, I agree with you but I use MSDE 2000 on local machine is no server there, just one machine with MSDE. When I see problem with date the first think I do, is looking Windows locale settings, it is OK, all settings are Slovenian. Then I try execute DIR in cmd, works OK (dd.mm.yyyy), then I try to execute all commands in QA EXEC XP_CMDSHELL 'DIR C:\somedir\*.*', get wong date format (mm/dd/yyyy).
Why I use XP_CMDSHELL DIR .....?
I nead to know file date to delete the old backup files. I have a stored procedure to generate backup. I use this SP inside the Job. My program generate the job and SQL scheduler start my job.....
XP_CMDSHELL is only way to get the file date, as I know.
Uros
-
You could script yourselves out of this problem. You can format the date anyway you like. Then instead of writing
dir c:\temp\*.*
you write
cscript mydir.vbs "c:\temp"
Option Explicit
Dim objFso
Dim objItem
Dim colFiles
Dim datD
Set objFso = CreateObject("Scripting.FileSystemObject")
Set colFiles = objFso.GetFolder(WScript.Arguments(0)).Files
For Each objItem In colFiles
datD = objItem.DateCreated ' you also have DateLastAccessed and DateLastModified
WScript.Echo Mid(CStr(Day(datD)+100), 2) & "." & Mid(CStr(Month(datD)+100), 2) & "." & CStr(Year(datD)) & " " & objItem.Name
Next
-
That would do it...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Are you deleting files that are older than a certain date? In my backup jobs I just delete ANY non-zipped backups that are out there, then do my backup. I also zip the backup after it is created and copy it off to another machine.
This way I have three copies of the backup file, one on a different machine. Saves a ton of space and adds another layer of safety. I think you could just skip your problem all together if you just zipped your backup to a name with the date & time in it and then just delete any nonzipped backup before you start. Much more simple than trying to find the date from the DIR command.
Live to Throw
Throw to Live
Will Summers -
Michael, very nice solutions. I will try it.
Will, my backup jobs delete files older then X days. My backup enable tree types of backup, once per week on specified day and specified time, once per day on specified time and many time on day on specified hours (every hour from 08:00 to 16:00). Backup files is write to specified folder, from there user alone copy bak files to other disks or machines - if they want. In many cases backup folder is on phisical different disk and all backup runing full automatic. So i must to take care of the old backup files in my code. User alone determine which type of backup they run on wich day and hour, how many old (in days) files will be deleted and where the files will be saved. This types of backups normaly use for small databases on MSDE, for big databases on servers I use backup from SQL server.
I think the solutions with script will solve my problem becouse Michael script is insensitive to date format.
Uros
-
Script work fine.
My problem is solved.
But question why XP_CMDSHELL "DIR..." return date in wrong format still exist!?!?
Thanks all for replys
Uros
-
You might be getting regional settings of the account under which the SQL Server service runs. Have you checked that?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply