Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Question Unanswered: sp_OACreate 'DTS.Package' in SQL 2000

    Hi,
    I want to export all the DTS packages on a server. So I run the following code. It works using a SQL 2000 Query Analyzer on SQL7 but I get an error when trying it on SQL2000. The error is "OLE32.DLL has been loaded at the wrong address." I have marked where i get the error any ideas?
    Many thanks,
    Iain


    ------ CODE USED -----------------
    use msdb

    set nocount on
    declare @objPackage int
    declare @PackageName varchar(128)
    declare @rc int
    declare @ServerName varchar(128)
    declare @out varchar(1000)
    declare @Path varchar(100)
    declare @id int

    select @Path = 'd:\dts\'
    select @ServerName = @@ServerName

    create table #a(s varchar(500), id int identity)

    insert #a select distinct name from sysdtspackages
    select @id = 0
    while @id < (select max(id) from #a) and (@id < 1)
    begin
    select @id = min(id) from #a where id > @id
    select @PackageName = s from #a where id = @id

    select @PackageName

    exec @rc = sp_OACreate 'DTS.Package', @objPackage output
    -- The error is here

    exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @PackageName = @PackageName, @Flags = 256

    -- Get error
    EXEC sp_OAGetErrorInfo @objPackage

    select @out = @Path + @PackageName + '.dts'
    exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile' , null, @out
    exec sp_OADestroy @objPackage
    end
    drop table #a

  2. #2
    Join Date
    Feb 2003
    Posts
    26
    I have the same problem "OLE32.DLL has been loaded at the wrong address" returned after having promoted all the objects to our live environment (it is working on dev and test). They are all Windows NT4 with Service Pack 6a installed, and SQL Server 2000 with service pack 3 applied.

    I have completely rebuilt the live server to be certain that the configuration is the same and I still get this problem. The only difference that I can identify now is that the live server is a DELL Poweredge 2500 with Raid arrays. These use different dll's for running the hardware etc.

    Does yours - or is anybody else with this problem running on the same or different hardware?
    Nicky Jones

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: sp_OACreate 'DTS.Package' in SQL 2000

    This may not address your issue directly, but there is something called DTSBackup2000 available as a free download from http://www.sqldts.com. It will transfer selected or all packages from one server to another quite well.

    HTH,

    Hugh Scott


    Originally posted by IainC
    Hi,
    I want to export all the DTS packages on a server. So I run the following code. It works using a SQL 2000 Query Analyzer on SQL7 but I get an error when trying it on SQL2000. The error is "OLE32.DLL has been loaded at the wrong address." I have marked where i get the error any ideas?
    Many thanks,
    Iain


    ------ CODE USED -----------------
    use msdb

    set nocount on
    declare @objPackage int
    declare @PackageName varchar(128)
    declare @rc int
    declare @ServerName varchar(128)
    declare @out varchar(1000)
    declare @Path varchar(100)
    declare @id int

    select @Path = 'd:\dts\'
    select @ServerName = @@ServerName

    create table #a(s varchar(500), id int identity)

    insert #a select distinct name from sysdtspackages
    select @id = 0
    while @id < (select max(id) from #a) and (@id < 1)
    begin
    select @id = min(id) from #a where id > @id
    select @PackageName = s from #a where id = @id

    select @PackageName

    exec @rc = sp_OACreate 'DTS.Package', @objPackage output
    -- The error is here

    exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @PackageName = @PackageName, @Flags = 256

    -- Get error
    EXEC sp_OAGetErrorInfo @objPackage

    select @out = @Path + @PackageName + '.dts'
    exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile' , null, @out
    exec sp_OADestroy @objPackage
    end
    drop table #a

Posting Permissions

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