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