xp_cmdshell problems... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_cmdshell problems...

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-19 : 10:13:28

Ok, I feel like a total idiot but it seems this has got the best of me. The following script works excellent on a number of 2005 servers but when I try to run it on my 2000 server absolutely nothing is returned. Does anyone know why?? ->

CREATE TABLE #cmdTable (
  [Filename] varchar(500)
)
DECLARE @dirCmd varchar(500)
SET @dirCmd = 'dir c:\'

INSERT INTO #cmdTable
EXEC xp_cmdshell @dirCmd

SELECT * FROM #cmdTable

DROP TABLE #cmdTable

- Lumbago

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-19 : 10:23:16

Is there an error ?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-19 : 10:41:15

Nope, nothing whatsoever...it says that the query completed successfully and the #cmdTable is empty.

- Lumbago

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-19 : 10:49:55

has to be a security related difference. Can you try running the same on sql server 2000 with a sysadmin role ?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-19 : 10:56:18

It's totally weird...doesn't matter if I'm logged in with sql server credentials as sysadmin or with windows credentials as a domain admin.

EDIT: Service is running as Local System Account.

- Lumbago

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-19 : 11:04:37

Its weird. Came across this link. Not sure if you would want to do this though.

http://www.mydatabasesupport.com/forums/sqlserver-tools/389988-xp_cmdshell-executes-successfully-sql-2000-but-fails-msde-2000for-sharepoint-database.html

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-20 : 04:57:57

Thank you for the link, however it didn't solve my problems unfortunately. I tried to replace the dll with one from my 2005 server but that produced the same results. The only thing I can think of that can cause these problems is that sql server is installed on a mounted volume that actually resides on a VMWare server. I have no idea why it's set up like this (and I would never have done it this way) but except from this xp_cmdshell issue everything works fine. I also just tried creating a job with a cmdExec task and that also worked fine so I think that's what I'll do. I needed the xp_cmdshell to delete some old backupfiles on a regular basis but I guess I need to create a bat-file for it instead...

Thanx alot for your help though

- Lumbago

dsindo
Starting Member

45 Posts

Posted - 2009-03-26 : 20:35:30

create table #temp (text varchar(1000))

insert into #temp
exec master..xp_cmdshell 'dir c:\'

select * from #temp

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-26 : 22:04:13

I've seen a lot of instances where LocalSystem cannot access the root folder of a drive, but can access folders within it. What results do you get if you run the xp_cmdshell by itself, without inserting it into a table?

dsindo
Starting Member

45 Posts

Posted - 2009-03-27 : 16:25:56

i see the contents on c:\

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-28 : 09:21:14

declare @rc int
CREATE TABLE #cmdTable (
[Filename] varchar(500)
)

DECLARE @dirCmd varchar(500)
SET @dirCmd = 'dir c:\'

INSERT INTO #cmdTable
EXEC @rc = xp_cmdshell @dirCmd

select @rc
SELECT * FROM #cmdTable

DROP TABLE #cmdTable

What do @rc return?

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-30 : 05:31:04

@robvolk: the query exec master..xp_cmdshell 'dir c:\' gives the result "Command(s) completed successfully.", nothing else.

@Peso: @rc = 1, table is empty.

- Lumbago

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-30 : 08:40:13

Can you try exec xp_cmdshell 'echo Hello, World!' and see if that returns any output.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-30 : 08:52:24

And try to use 'dir c:\*.*'

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-30 : 09:30:41

Command(s) completed successfully.

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-30 : 09:32:13

...for both cases. I think I'll just give up. I'm tempted to try a exec xp_cmdshell 'format c:' but I don't think I have the guts...

- Lumbago

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-30 : 10:08:26

I vaguely remember a trace flag that altered the output of certain commands, I don't know if it's documented or not. Take a look in Books Online and try googling "undocumented trace flags".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources