Problem with XP_CMDSHELL ''DIR .....''

Question

Post reply

Problem with XP_CMDSHELL ''DIR .....''

dUros

SSCommitted

Points: 1506
More actions
November 2, 2006 at 6:04 am

#114908

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

Viewing 15 posts - 1 through 15 (of 16 total)

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

Will1922 SSCertifiable Points: 7189 More actions · Answer 1

Will1922

SSCertifiable

Points: 7189

November 2, 2006 at 8:02 am

#669485

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

dUros SSCommitted Points: 1506 More actions · Answer 2

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

Will1922 SSCertifiable Points: 7189 More actions · Answer 3

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

dUros SSCommitted Points: 1506 More actions · Answer 4

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

dUros SSCommitted Points: 1506 More actions · Answer 5

dUros

SSCommitted

Points: 1506

November 2, 2006 at 12:32 pm

#669595

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

Sergiy SSC Guru Points: 110192 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

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)
Intro to Tally Tables and Functions

dUros SSCommitted Points: 1506 More actions · Answer 8

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

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)
Intro to Tally Tables and Functions

Will1922 SSCertifiable Points: 7189 More actions · Answer 11

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

dUros SSCommitted Points: 1506 More actions · Answer 12

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