April 13, 2007 at 5:58 am
I have followed this article http://www.sqlteam.com/item.asp?ItemID=19595 so that I can run a dts package from an aspx page. When running the stored procedure my job creates succsfully but fails. I have run the dts on its own without problem.
The command line of job is:
"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "(local)" /N "rm_vr_VDC101_Import" /W "0" /E
Does this look ok?
April 13, 2007 at 6:21 am
I remember having issues with the quotes. Sometimes you need the standard " other times I've had to use 2 single quotes together ''. They look quite similar and are very hard to distinguish when typed. Try reversing what you are using and see what happens.
Regards,
Matt
April 17, 2007 at 1:12 am
Ive used every combination and still no luck.
In the failed Job my command line reads DTSRun /S "(local)" /N "rm_vr_VDC101_Import" /W "0" /E
April 17, 2007 at 2:22 am
If the aspx page is hosted on a different machine from the SQL Server, I don't think it's going to like "(local)" very much. Try changing that to the actual server name that the aspx host would know it as, and let us know if that works.
April 17, 2007 at 2:55 am
why dont u use sql server to create the job by just right clicking the dts and selecting the "schedule package" option? the job will still use dtsrun with encrypted parameter ..
April 17, 2007 at 3:31 am
not sure if that would matter as the asp page calls the stored procedure, which sits on the same server as the dts. I toook your advice however and generated the full command using the DTSRun utility and got:
DTSRun /S "Server_name" /N "rm_vr_VDC101_Import" /G "{1F4F2CCA-2FFE-444B-8AD6-56DF05811D68}" /W "0" /E
When the sp was run however it still fails. Is there a way to actually see what failed in the job?
April 17, 2007 at 3:32 am
because I need the job to be created, run and deleted automatically from as asp page
April 17, 2007 at 6:28 am
This should fix it for you. From your ASP page call a proc that calls the DTS. This is how I did it. Below is the proc code:
DECLARE @myline as varchar(7000)
SET @myline = 'dtsrun /Sservername /Uusername /Ppassword /N"DTSName" + ''
EXEC master..xp_cmdshell @myline
Regards,
Matt
April 17, 2007 at 7:37 am
Do you need administrater access to run this, because I haven't, and wont be able to get this access
April 17, 2007 at 11:58 am
I believe so. That was my sample from Dev, where I had control, but as I recall once it was pushed to Prod the DBA's assumed control and simply edited the User and Password so that it functioned correctly.
At least here, my DBA's were willing to take that responsibility. Hope that helps.
Regards,
Matt
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply