Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2007, 09:37 AM
Registered User
 
Join Date: Aug 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default RunDTSPackage

The situation:
We have 2 servers, one for web and one that runs SQL Server 2000. Last week our server that runs SQL Server crashed and we had to reinstall the OS and all. We recovered from this and lost a few days of data but nothing severe. The problem facing me is that when calling this code we receive the error '*** Load Package failed' which can be traced to the SP below.

Basically what we do is to upload an excel sheet that contains 2 columns to our webserver. This excel doc is then renamed to the data&time and should be imported into a table in my database but it never gets that far.

Things I have done so far:
At first we were receiving the error: Could not find stored procedure 'sp_displayoaerrorinfo' but I tracked it down to Books Online and it is now loaded along with sp_hexadecimal. I most recently gave the "sql_user" (defined in the SP below) EXEC rights on all sp_OA* extended stored procs in the master database. Now I am stuck in the --Loading the package and I'm not sure where to look.





Code:
CREATE PROC sp___Admin_RunDTSPackage

@ExecPersonID int,
@PackageName varchar(100),
@Response varchar(100) OUTPUT

AS

DECLARE    @hr int, --just a return code?
        @oPKG int, --just a return code?
        @LoadFromSQLServer varchar(200), --command to load DTS pkg
        @CRUDDetails varchar(100) --logging details

--The load from server command will differ depending on the server on which it is running.
--Rather than pass all these parameters in, i'll just provide the variations here, and we can
--comment out the unused ones on each server.
SET @LoadFromSQLServer = 'LoadFromSQLServer("ip_of_my_webserver", "sql_user", "sql_user_password", 0, , , , "' + @PackageName + '")'

--The following code was based on the sample given at the following URL
--http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1

--creating the package object:
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
    SET @Response = '***  Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

--Loading the package:
EXEC @hr = sp_OAMethod @oPKG, @LoadFromSQLServer, NULL
IF @hr <> 0
BEGIN
    SET @Response = '***  Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

--Executing the Package: 
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
    SET @Response = '***  Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN
END

--Cleaning up: 
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
    SET @Response = '***  Destroy Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

--END OF CODE based on example at http://www.databasejournal.com/featu...0894_1459181_1


--Log the execution
IF @Response IS NULL
SET @Response = 'Success'

SET @CRUDDetails = 'Run DTS pkg: ' + @PackageName + ' ::result msg.: ' + @Response

EXEC sp__CRUDLog_Insert
    @CRUDObjectTypeID = 31, --DTS
    @CRUDObjectID =  0, --object id not relevant in this case
    @CRUDAction = 'E',
    @CRUDDetails = @CRUDDetails,
    @PersonID = @ExecPersonID
GO









Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.