Re: Executing DTS from Code
- From: "WhiskyRomeo" <WhiskyRomeo@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 8 Nov 2005 12:00:02 -0800
No one will be executing the code from the server, they will be executing the
code from their workstation under their login. I am just doing it from the
server, because it is a remote server to which I rdp in.
I hadn't explicitly changed the user in QA because I logged in as
Administrator and "assumed" that is the loging used by the application. I am
sure it is but I will check.
.... I seem to vaguely recall the user has to have execute privileges for
those built in sp's. I going to test that now.
Bill
"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:
- Executing DTS from Code
- From: WhiskyRomeo
- Re: Executing DTS from Code
- From: Allan Mitchell
- Executing DTS from Code
- Prev by Date: RE: How do I programatically schedule a DTS job
- Next by Date: Re: Populating fact table : SSIS : SQL Server 2005
- Previous by thread: Re: Executing DTS from Code
- Next by thread: Re: Executing DTS from Code
- Index(es):
Relevant Pages
|
