bmgoa
asked on
DTS package not working
Hello,
I recently upgraded my SQl 2000 to SQl 2005 sp2.
Microsoft says existing DTS packages would continue to run under 2005 with out changes.
However my sql given below which was working before is no longer working.
declare @Cmd varchar (200)
select @Cmd = 'dtsrun /S' + @@servername + ' /E /WTrue /NDTS_Extract_Files'
exec master..xp_cmdshell @Cmd
It does not show any error.so what is going wrong?
I downloaded the Microsoft Sql Server 2005 DTS Designer and opend my package in it. Right Click -> Execute..
It is working fine.
The above query when i run from MS Sql Server Management Studion query window, it is not working.
Any ideas?
Regards,
Bm
I recently upgraded my SQl 2000 to SQl 2005 sp2.
Microsoft says existing DTS packages would continue to run under 2005 with out changes.
However my sql given below which was working before is no longer working.
declare @Cmd varchar (200)
select @Cmd = 'dtsrun /S' + @@servername + ' /E /WTrue /NDTS_Extract_Files'
exec master..xp_cmdshell @Cmd
It does not show any error.so what is going wrong?
I downloaded the Microsoft Sql Server 2005 DTS Designer and opend my package in it. Right Click -> Execute..
It is working fine.
The above query when i run from MS Sql Server Management Studion query window, it is not working.
Any ideas?
Regards,
Bm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i ran the Path command using xp_cmd shell in studio's query window.
It seems to truncate the Path and the value C:\Program Files\Microsoft SQL Server\80\Tools\Binn is not coming in. it shows upto 1031 characters and the above path is beyond that range.
don't know why it truncates though.
It seems to truncate the Path and the value C:\Program Files\Microsoft SQL Server\80\Tools\Binn is not coming in. it shows upto 1031 characters and the above path is beyond that range.
don't know why it truncates though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think i figured it out:
actually a copy of DTSRun was lying in C:\Windows\System32 without the other requisites dll.
So the command was not working fine; I deleted the copy from System 32. and qualified the full path in the command i.e.
select @Cmd = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSRu
it worked now.
Actually C:\Program Files\Microsoft SQL Server\80\Tools\Binn path is present in the system path variable; so don't know why Studio is not taking it. Any idea on this?