Problem with xp_cmdshell - 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

Transact-SQL (2000)

Problem with xp_cmdshell

Author

Topic

happyboy
Starting Member

21 Posts

Posted - 2009-04-24 : 02:59:35

Dear all, I have many DTS packet in SQL server 2000, and I wrote script to extract all of them to my disk but when I run ii just display in result list of SQL query analyzer not to my disk.

Please help me. thanks.

here my code

DECLARE @CMD varchar(1000)

DECLARE @TARGETDIR varchar(500)
SET @TARGETDIR = 'C:\'

DECLARE mycursor CURSOR FOR
SELECT distinct
'DTSRUN /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @TARGETDIR + name + '.dts"'
+ ' /!X'
FROM msdb.dbo.sysdtspackages P

OPEN mycursor

FETCH NEXT FROM mycursor INTO @CMD

WHILE @@FETCH_STATUS = 0
BEGIN
exec master..xp_cmdshell @CMD

FETCH NEXT FROM mycursor INTO @CMD
END

CLOSE mycursor
DEALLOCATE mycursor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 03:12:47

Add a PRINT statement just before the line of exec master..xp_cmdshell @CMD, list this

PRINT @cmd
exec master..xp_cmdshell @CMD

and examine the printed statement. Then you can copy one of the printed statements and run in a dos window to see if there is a syntax error.

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

happyboy
Starting Member

21 Posts

Posted - 2009-04-24 : 03:35:36

thanks for reply,
I have print @cmd , it is ok an I run in cmd shell it's ok.
but in sql it just display as result as in cmd shell not extract file to disk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 03:40:16

All commands run through cmdshell have same security clearance as the owner of SQLAgent.
Please see the SQLAgent service owner and make that user have rights for the file path/server.

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

happyboy
Starting Member

21 Posts

Posted - 2009-04-27 : 02:18:07

I have checked it export DTS package to the disk of sql server, but I would like to export to local disk that run scripts (not on sql server computer).

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources