November 27, 2013 at 3:33 pm
Hi all,
I am facing unique issue haven't experienced this kind of a issue so far.
1) I am running a DIR command using XP_Cmdshell the good thing is it runs but it gives me the output in DDMMYYYY format for files, actually the Windows 2008 server was running under Canadian region and was using Canadian style date (DD-MM-YYYY) which I changed it to US and also the region, everything is running as expected but not the XP_CMdshell. :angry:
My Sql Version is :- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4279.0 (X64) Mar 26 2013 17:33:13 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
The Command I am running is (EXEC MASTER..XP_CMDSHELL 'DIR B:\Backup\') I am getting below output
27/11/2013 04:08 PM 566,784 Full_master_20131127_168.BAK
27/11/2013 04:08 PM 142,848 Full_model_20131127_168.BAK
27/11/2013 04:08 PM 3,450,368 Full_msdb_20131127_168.BAK
If I run the same command from command prompt window I am getting below output and this is the Output I need..
11/27/2013 04:08 PM 566,784 Full_master_20131127_168.BAK
11/27/2013 04:08 PM 142,848 Full_model_20131127_168.BAK
11/27/2013 04:08 PM 3,450,368 Full_msdb_20131127_168.BAK
Not sure what I am missing I checked the Sql and it is running under default language English. Am I missing any setting? OR do I have to do some other stuff at Sql End after changing the System date style format...
NOTE :- This system is running on VM Ware....And its a image.
November 28, 2013 at 2:16 am
Regional options are per-user, not system-wide.
Change regional options on the server while logged in as the SQL Server service account.
-- Gianluca Sartori
November 28, 2013 at 9:45 pm
Tried running the SQL Under Local system account as well no luck
December 2, 2013 at 6:41 am
When you run xp_cmdshell, it runs under the security context of the SQL Server Service Account (not the account you are logged on as). One exception is, if your account is not a member of sysadmin fixed server role, then it will run under the xp_cmdshell proxy account. For details, read:
http://technet.microsoft.com/en-us/library/ms175046%28v=sql.105%29.aspx
My suggestion is to check the service account. If you can log on, interactively, as the service account, then change the Regional and Language Options. It might be better to configure an xp_cmdshell proxy account, and use that instead.
Andy
December 3, 2013 at 11:52 am
We encountered this as well. We were writing a process to purge old backup files, using xp_cmdshell to bring back a DIR listing just like you have. We ended up just putting the results of that into a temporary table, and then parse it out into another table with the date formatted the way that we want.
December 3, 2013 at 11:57 am
Thanks for all of your reply, as few people replied that try logging in with the service account and then change the date format. That worked, as the date time format is specific to user credentials.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply