Executing DTS within a Stored Procedure

Hello,

I'mtrying to execute a DTS from within a stored procedure on my database, using OLE Automation SP's. If I do that on my local database server, it works fine, but when I try to upload the SP's to Discount's server, does not even let me create the stored procedure at my database (Server: MSSQL07.DISCOUNTASP.NET).

This lines are example of what I'm doing:
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
EXEC @hr = sp_OAMethod@oPKG, 'LoadFromSqlServer',NULL, @ServerName='MSSQL07.DISCOUNTASP.NET', @ServerUserName='sa', @PackageName='DTSAlertOperator', @Flags='256'

When I Comment these lines, the stored procedure is saved normally, but when I uncomment again, it shows the follow error:
"Error 0: This server has been disconnected. You must reconnect to perform this operation" and does not let me do anything else about it.

Hope someone can me help on this. I was looking through the forum but couldn't find anything like that. I really need to get this solved A.S.A.P.

Sincerely,

Douglas Borrero

Post Edited (vortex1172) : 10/1/2008 9:32:50 PM GMT

 

Hi,
DTS packages can only be run locally on your machines, not on Shared SQL Servers.
You have to admit DASP would be pretty crazy to allow that onShared servers.
Salute,
Mark

 

Hi Mark, thanks for your reply, butI have to disagree with you. In fact, DTS are allowed in DiscountASP databases and many other services. That's not my issue. What I want is to execute a DTS inside a stored procedure, but I can´t even create the SP when command sp_OACreate or any other similar is present.


Anyway, thanks again.


Doug.

 

Hi,
Contact Support, it has always been my understanding that DTS packages have to be run locally by us here.
I've used DTS a lot for business purposes but never on a Shared host.
Salute,
Mark</o>