Executing DTS within a Stored Procedure

[vortex1172]

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

[wisemx]

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. http://community.discountasp.net/emoticons/wink.gif
Salute,
Mark

[vortex1172]

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.

[wisemx]

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:p>