Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: 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.

    Code:
    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 you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you getting an error message?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    Quote Originally Posted by blindman
    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.

  4. #4
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Use the UNC path

  5. #5
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61

    Permissions?

    Does the login you are executing the OA_ stored procs as have permission to execute them?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •