Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unique XP_Cmdshell issue. Expand / Collapse
Author
Message
Posted Wednesday, November 27, 2013 3:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 49, Visits: 395
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.

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.
Post #1518220
Posted Thursday, November 28, 2013 2:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 4,913, Visits: 8,629
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1518293
Posted Thursday, November 28, 2013 9:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 49, Visits: 395
Tried running the SQL Under Local system account as well no luck
Post #1518436
Posted Monday, December 02, 2013 6:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 986, Visits: 1,029
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
Post #1518874
Posted Tuesday, December 03, 2013 11:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:52 PM
Points: 16, Visits: 539
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.
Post #1519343
Posted Tuesday, December 03, 2013 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 49, Visits: 395
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.
Post #1519349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse