OLE Automation programming:

  • I have been trying to use the sp_oacreate with a linked server pointing to a second instance of an sql.

    The linked server has a "\" in there of course and this is causing an issue retrieving server objects for scripting purposes (see URL of the original source code)

    http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html

    -- prepare scripting object

    select @context = 'create dmo object'

    exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    if @SourceUID is null

    begin

    select @context = 'set integrated security ' + @SourceSVR

    exec @rc = sp_OASetProperty @objServer, LoginSecure, 1

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    end

    select @context = 'connect to server ' + @SourceSVR

    exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    It works with any default instance on any linked server but not the second instance of.. (even added an alias on the destination server instance)

    I also created other types of instances that actually would use the native client 10.0 and that did not work either.

    The funny part is that the OPENQUERY for getting object listing from such linked server is not an issue..

    except the OA methods.

    any assistance and direction will help.

    thx guys

    Cheers,
    John Esraelo

  • The main error on creation is:

    failed on creating DMO object

    Cheers,
    John Esraelo

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply