October 14, 2011 at 11:06 am
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
October 14, 2011 at 11:15 am
The main error on creation is:
failed on creating DMO object
Cheers,
John Esraelo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply