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?

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.

Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.

"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.c om...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Marcus wrote:

> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus

The Oracle client is required. So is paying attention to the license
agreement.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)sql

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)