DTS from mapped drive problem

Hey guys

I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.

CREATE PROC spExecuteDTS
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Check Pkg Errors
EXEC @.ret=spDisplayPkgErrors @.oPKG

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

RETURN @.ret
GO

that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?

thank youAre you getting an error message?|||Are you getting an error message?

*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.|||Use the UNC path|||Does the login you are executing the OA_ stored procs as have permission to execute them?|||Wow

What to say

Usually people use DTS to avoid sprocs...but you're combing the 2

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

DTC and firewalls

We have a requirement to utilize MS-DTC from internet facing to an internal
system running SQL Server 2000. Can the DTC be stand alone on a dedicated
server in the external environment and then contact the SQL database through
a specific port? Is this technically possible? Any security implications?Alexis wrote:
> We have a requirement to utilize MS-DTC from internet facing to an internal
> system running SQL Server 2000. Can the DTC be stand alone on a dedicated
> server in the external environment and then contact the SQL database through
> a specific port?
Yes.
> Is this technically possible?
Yes.
> Any security implications?
DTS is a usual SQL Server Client application. All the security
implications are based on this fact.|||Yura, perhaps you misread DTC as DTS. DTC, AFAIK, speaks only RPC which probably makes it tricky
with firewalls (I'm not network expert...). I'm fairly certain that I've read a KB about this,
should be relatively easy to find.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yura Shalak" <yuras@.nospam.nospam> wrote in message news:up%23UoeohHHA.1456@.TK2MSFTNGP04.phx.gbl...
> Alexis wrote:
>> We have a requirement to utilize MS-DTC from internet facing to an internal system running SQL
>> Server 2000. Can the DTC be stand alone on a dedicated server in the external environment and
>> then contact the SQL database through a specific port?
> Yes.
>> Is this technically possible?
> Yes.
>> Any security implications?
> DTS is a usual SQL Server Client application. All the security implications are based on this
> fact.

DTC and firewalls

We have a requirement to utilize MS-DTC from internet facing to an internal
system running SQL Server 2000. Can the DTC be stand alone on a dedicated
server in the external environment and then contact the SQL database through
a specific port? Is this technically possible? Any security implications?
Alexis wrote:
> We have a requirement to utilize MS-DTC from internet facing to an internal
> system running SQL Server 2000. Can the DTC be stand alone on a dedicated
> server in the external environment and then contact the SQL database through
> a specific port?
Yes.

> Is this technically possible?
Yes.

> Any security implications?
DTS is a usual SQL Server Client application. All the security
implications are based on this fact.
|||Yura, perhaps you misread DTC as DTS. DTC, AFAIK, speaks only RPC which probably makes it tricky
with firewalls (I'm not network expert...). I'm fairly certain that I've read a KB about this,
should be relatively easy to find.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yura Shalak" <yuras@.nospam.nospam> wrote in message news:up%23UoeohHHA.1456@.TK2MSFTNGP04.phx.gbl...
> Alexis wrote:
> Yes.
>
> Yes.
>
> DTS is a usual SQL Server Client application. All the security implications are based on this
> fact.

DTC and firewalls

We have a requirement to utilize MS-DTC from internet facing to an internal
system running SQL Server 2000. Can the DTC be stand alone on a dedicated
server in the external environment and then contact the SQL database through
a specific port? Is this technically possible? Any security implications?Alexis wrote:
> We have a requirement to utilize MS-DTC from internet facing to an interna
l
> system running SQL Server 2000. Can the DTC be stand alone on a dedicated
> server in the external environment and then contact the SQL database throu
gh
> a specific port?
Yes.

> Is this technically possible?
Yes.

> Any security implications?
DTS is a usual SQL Server Client application. All the security
implications are based on this fact.|||Yura, perhaps you misread DTC as DTS. DTC, AFAIK, speaks only RPC which prob
ably makes it tricky
with firewalls (I'm not network expert...). I'm fairly certain that I've rea
d a KB about this,
should be relatively easy to find.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yura Shalak" <yuras@.nospam.nospam> wrote in message news:up%23UoeohHHA.1456@.TK2MSFTNGP04.ph
x.gbl...
> Alexis wrote:
> Yes.
>
> Yes.
>
> DTS is a usual SQL Server Client application. All the security implication
s are based on this
> fact.