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

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 (AT) discussions (DOT) microsoft.com> wrote

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:

Granting execute permision to the sp_XOA..... stored procedures in the Master
database solved this problem.

WR

"Allan Mitchell" wrote:

I meant sp_OA. .. not sp_XOA . . .

"WhiskyRomeo" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote