Re: Executing DTS from Code (correction)
- From: "Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 9 Nov 2005 00:29:13 +0000
Be aware that this may leave you open to a whole worl full of hurt.
Granting execute permissions on these procs means that external Com objects can be called (dlls) etc. If they run in-process and crash then bye-bye SQL Server. You could also spawn things like XL and word which would in the main be inappropriate uses.
Allan
"WhiskyRomeo" <WhiskyRomeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BB3543F3-0294-42B2-B697-6322DCB06DC6@xxxxxxxxxxxxx:
I meant sp_OA. .. not sp_XOA . . .
"WhiskyRomeo" wrote:
> Granting execute permision to the sp_XOA..... stored procedures in the Master > database solved this problem. > > WR > > "Allan Mitchell" wrote: >
> > Ok so who will be executing the VB.Net code on the server? > > > > Get that account and log in to the server as that account. Now go to QA > > and execute the proc. > > > > Can you? > > > > Allan > > > > "WhiskyRomeo" <WhiskyRomeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message > > news:E8426EBB-4F4B-4FC2-AFE6-EAFF074C52C1@xxxxxxxxxxxxx: > >
> > > I have VB.Net code that executes a store procedure that executes a DTS > > > package. > > > > > > Everything works fine on my development workstation which has SQL Server > > > installed locally. I moved the DTS package to the production server and > > > installed the application on the production server. I can execute the > > > DTS > > > package directly (in Enterpise Manager) on the production server > > > sucessfully; > > > and I can execute sucessfully the stored procedure (in Query Analyzer) > > > that > > > runs the DTS package. When moving the application code I only have to > > > account for a different Server Name, otherwise everything else is > > > identical. > > > > > > However, when executing the Package from code ( via calling the store > > > procedure) the DTS package does not execute. I have verified the store > > > procedure executes with the correct parameters. If the DTS package > > > executed, > > > it would have written to the log file for that DTS (whether sucessfull > > > or > > > not). > > > > > > I am logged in as Administrator and the package owner is Administrator. > > > > > > I suspect this is a persmission issue; but I am cluesless at this point. > > > Any help is appreciated. > > > > > > WR > > > > > > Here is the stored procedure( which works just fine):(I have verified > > > that > > > that the parameters values passed to it are correct) > > > > > > CREATE PROCEDURE spExecuteDTS_CompImport > > > > > > @LabOrderNbr varchar(15), > > > @Server varchar(255), > > > @PkgName varchar(255), -- Package Name (Defaults to most recent version) > > > @ConnSubject varchar(25) = '', > > > @ConnAssociate varchar(25) = '', > > > @ConnLink varchar(25) = '', > > > @PathSubject varchar(255) = '', -- Path to Source File > > > @PathAssociate varchar(255) = '', > > > @PathLink varchar(255) = '', > > > @ServerPWD varchar(255) = Null, -- Server Password if using SQL Security > > > to > > > load Package (UID is SUSER_NAME()) > > > @Security bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security > > > @PkgPWD varchar(255) = '' -- Package Password > > > > > > AS > > > SET NOCOUNT ON > > > > > > DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000), @Connection > > > varchar(255) > > > > > > Select @ret = 0 > > > > > > Delete tblImportPerson where LabOrderNbr = @LabOrderNbr --this will > > > cascade > > > to tblImportAddress > > > Delete tblImportLink where LabOrderNbr = @LabOrderNbr > > > print 'after delete tblImportLink' > > > > > > -- Create a Pkg Object > > > EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT > > > print 'created package' > > > > > > SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + > > > @PkgPWD + > > > '", , , "' + @PkgName + '")' > > > Print 'Loaded from server' > > > EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL > > > Print 'Executed sp_OAMethod' > > > -- Set Connections property > > > Select @Connection = 'Connections.Item(' + @ConnSubject + ').DataSource' > > > print 'Connection' + @Connection > > > EXEC @hr = sp_OASetProperty @oPKG, @Connection, @PathSubject > > > If @hr <> 0 > > > BEGIN > > > PRINT '*** OASetProperty for Connection Subject Failed***' > > > EXEC sp_OAGetErrorInfo @oPkg > > > RETURN > > > END > > > > > > Select @Connection = 'Connections.Item(' + @ConnAssociate + > > > ').DataSource' > > > EXEC @hr = sp_OASetProperty @oPKG, @Connection, @PathAssociate > > > If @hr <> 0 > > > BEGIN > > > PRINT '*** OASetProperty for Connection Associate Failed***' > > > EXEC sp_OAGetErrorInfo @oPkg > > > RETURN > > > END > > > > > > Select @Connection = 'Connections.Item(' + @ConnLink + ').DataSource' > > > EXEC @hr = sp_OASetProperty @oPKG, @Connection, @PathLink > > > If @hr <> 0 > > > BEGIN > > > PRINT '*** OASetProperty for Connection Link Failed***' > > > EXEC sp_OAGetErrorInfo @oPkg > > > RETURN > > > END > > > > > > -- Execute Pkg > > > EXEC @hr = sp_OAMethod @oPKG, 'Execute' > > > print 'Executed Package' > > > -- Unitialize the Pkg > > > EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' > > > > > > -- Clean Up > > > EXEC @hr = sp_OADestroy @oPKG > > > > > > UPDATE tblImportPerson SET AssociatedSubject_ID = IL.SubjectImport_ID > > > FROM tblImportPerson IP INNER JOIN tblImportLink IL ON IP.Import_ID = > > > IL.AssociateImport_ID > > > WHERE IP.LabOrderNbr = @LabOrderNbr > > > > > > > > > > > > RETURN @ret > > > GO > >
> >
.
- References:
- Re: Executing DTS from Code (correction)
- From: WhiskyRomeo
- Re: Executing DTS from Code (correction)
- Prev by Date: Re: Executing DTS from Code (correction)
- Next by Date: Re: DTSRun from SP
- Previous by thread: Re: Executing DTS from Code (correction)
- Next by thread: Re: Outputting multiple datasets to excel
- Index(es):
Relevant Pages
|
Loading
