Re: Executing DTS from Code (correction)
- From: "WhiskyRomeo" <WhiskyRomeo@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 8 Nov 2005 15:58:02 -0800
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
> >
> >
.
- Follow-Ups:
- Re: Executing DTS from Code (correction)
- From: Allan Mitchell
- Re: Executing DTS from Code (correction)
- References:
- Executing DTS from Code
- From: WhiskyRomeo
- Re: Executing DTS from Code
- From: Allan Mitchell
- Re: Executing DTS from Code
- From: WhiskyRomeo
- Executing DTS from Code
- Prev by Date: Re: Executing DTS from Code
- Next by Date: Re: Executing DTS from Code (correction)
- Previous by thread: Re: Executing DTS from Code
- Next by thread: Re: Executing DTS from Code (correction)
- Index(es):
Relevant Pages
|
