Cannot get the COM Object working

Hello,

I am trying to instantiate browser Internet Explorer and open the browser with a default url. I can create a new Object but cannot make it visible or make it navigate to a the default url.

Here is the code.

declare @.rc int
declare @.obj int
declare @.name varchar(255)
declare @.url varchar(5000)
declare @.source varchar(255), @.description varchar(255)

set @.url = 'http://www.hotmail.com'

exec @.rc = sp_OAcreate 'InternetExplorer.Application', @.obj out
If @.rc <> 0 begin
PRINT '*** InternetExplorer Object Creation failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END

exec @.rc = sp_OAgetProperty @.obj, 'Name', @.name out
If @.rc <> 0 begin
PRINT '*** Get Property Failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END
else
print @.name

exec @.rc = sp_OAsetProperty @.obj, 'Visible', 'True'
If @.rc <> 0 begin
PRINT '*** Set Property Failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END

exec @.rc = sp_OAgetProperty @.obj, 'Visible', @.name out
If @.rc <> 0 begin
PRINT '*** Get Property Failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END
else
print @.name

exec @.rc = sp_OAgetProperty @.obj, 'Width', @.name out
If @.rc <> 0 begin
PRINT '*** Get Property Failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END
else
print @.name

exec @.rc = sp_OAgetProperty @.obj, 'Height', @.name out
If @.rc <> 0 begin
PRINT '*** Get Property Failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END
else
print @.name

exec @.rc = sp_oaMethod @.obj, 'Navigate', @.url
If @.rc <> 0 begin
PRINT '*** Navigation Method Failed'
EXEC sp_OAGetErrorInfo @.obj, @.source out, @.description out
print @.source
print @.description
RETURN
END
else
print @.nameAll OLE objects are automatically destroyed at the end of each batch. So if it were possible the object would be destroyed and you would never see it.|||If the object was destroyed after the first batch then all the getProperty executions would fail as well. But thats not the case. All my getProperty calls return correct results. Its just the Method i.e. Navigate method fails.

You can actually copy this code and paste in Query Analyser and it will work i.e. you will get the results for all the getProperty calls.

Thanx for responding.|||How will the object be displayed if it is destroyed ? What are you considering as a batch ? Yes - you can retrieve the properties - but once this code is done the object is destroyed.|||I got confused by the term batch used in your first reply but now I understand what u mean by the term batch (all the T-sql statements).

Coming back to your first reply..I am not sure If the browser object is destroyed..How do I check if the object is destroyed or does exist

All this would be simple I can execute some Vbscript from T-sql (trigger) because creating a browser object and navigating it to a default url works fine.

Is there anyway wheuere I can execute a Vbscript from Sql Server ?|||could you use xp_cmdshell to do it? just create a vbs file and execute it?|||Originally posted by rokslide
could you use xp_cmdshell to do it? just create a vbs file and execute it?

Have never used xp_cmdShell...any tips on this would be helpful.

Thanx|||Yes it is destroyed - look at bol.

Can you describe in detail what you are trying to do ? Why do you want to invoke ie ?|||Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

eg.
xp_cmdshell 'dir *.exe', NO_OUTPUT

so in your case you'd be after something like...

xp_cmdshell 'openhotmail.vbs', NO_OUTPUT

I think|||I tried creating a .vbs file as posted below and tried executing the script from Query Analyzer. Same Result. I dont see a new instance of the browser.

My T-sql script is:
DECLARE @.result int

execute @.result = master.dbo.xp_cmdShell 'C:\WINNT\Temp\test.vbs'
If @.result = 0
print 'Success'
Else
print 'Failure'

My .vbs file look like
set Wshell = Wscript.CreateObject("Wscript.Shell")
Wshell.Run("http://www.yahoo.com")
set Wshell = nothing

In Query Analyzer I get a Success message but no new browser pointing to www.yahoo.com

Thanx for the help|||Originally posted by rnealejr
Yes it is destroyed - look at bol.

Can you describe in detail what you are trying to do ? Why do you want to invoke ie ?

I have to execute a cgi-script running on a real time system from an asp page. This asp page has to be called when an event like customer deactivation occurs.

The event handler (trigger) will call the asp page which does the needful on the realtime system.|||try this instead...

set Wshell = Wscript.CreateObject("Wscript.Shell")
Wshell.Run("iexplore.exe http://www.yahoo.com")
set Wshell = nothing|||Originally posted by rokslide
try this instead...

set Wshell = Wscript.CreateObject("Wscript.Shell")
Wshell.Run("iexplore.exe http://www.yahoo.com")
set Wshell = nothing
Tried the code posted by you but no luck. Do you think theres some kind fo security issue or some kind of OS (Windows 2000 SP4) issue|||Well I just had a little play... and did this...

DECLARE @.result int

execute @.result = master.dbo.xp_cmdShell 'calc.exe'
If @.result = 0
print 'Success'
Else
print 'Failure'

I am doing this remotely eg. the server is not the same box as I am running the query on.

On the server I can see the calc process but I am not sure who "owns" it. I was logged in as admin at the time.

The process was probably created under the account used to run sql.

Perhaps this is what you are seeing (or not seeing). Your IE thread is owned by someone else??

What you could try is rather than calling your cgi script directly using IE create an instance of the XML parser what has a HTTP object in it and do a http post to the page you want. In theory this should trigger the cgi script (I think).|||So effectively you would call a vbs again... but it would look something like...

Dim HttpReq
Set HttpReq = CreateObject("MSXML2.XMLHTTP40")
HttpReq.open "GET", "http://XMLSampleServer/CatalogServer.asp", False
HttpReq.send
set HttpReq = nothing

but pointing to your cgi script...

in theory the request should get sent and when it returns your vbs will complete and return back to the query analyzer...

of course I haven't tried this before so... no promises. :D|||Thanx for the help.

I tried to execute the code but it fails ar Line 2. I get ActiveX Component can't create object :MSXML2.XMLHTTP40|||have you got the XMLParser 4.0 installed??|||opps... not 4.0 try this download...

http://www.microsoft.com/downloads/details.aspx?FamilyId=C0F86022-2D4C-4162-8FB8-66BFC12F32B0&displaylang=en|||Downloaded and installed the XML Parser but I still get the same error
ActiveX Componenet can't create object: MSXML2.XMLHTTP40|||try this in your vbs instead...

Dim xmlHttp
set xmlHttp = CreateObject("Msxml2.XMLHTTP")
xmlHttp.open "GET", "http://localhost/sample.xml", False
xmlHttp.send
set xmlHttp = nothing|||Tried this one but I get access denied error. Source is msxml3.dll. I guess the system is till using msxml3 and not msxml4. How do i uninstall 3 and install msxml4|||pass...

at what point do you get the access denied??|||Line 4

Actually I have changed the code a little bit

Dim xmlHttp
set xmlHttp = CreateObject("MSXML2.XMLHTTP.3.0")
xmlHttp.open "GET", "http://hotmail.com", False
xmlHttp.send
set xmlHttp = nothing

is this incorrect ?

Also I tried
Dim xmlHttp
set xmlHttp = CreateObject("MSXML2.XMLHTTP")
xmlHttp.open "GET", "http://hotmail.com", False
xmlHttp.send
set xmlHttp = nothing

but the result is same..error in Line 4|||technically looks fine to me,.. the fact that you get access denied when you get the the send is interesting...

perhaps this might help...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk30/htm/xmmthopenixmlhttprequest.asp

there is a username and password oyu can supply with then open command, it doesn't really make much sense to me though...

do you go through a proxy or anything that requires authentication??|||Rokslide ..

have researched a lot on this and was not able to instantiate iexplore from SQL server. Do PM me in case you get to an answer , though i will keep an eye on this thread|||If you instantiate an object that requires a ui nothing will be displayed - these processes are all background processes (think of a console application).

For xp_cmdshell, this is a huge security hole in sql server - so use at your own risk. xp_cmdshell is synchronous - so it will wait and wait and wait and wait ... rokslide - that command you ran with calc was hung. Also, xp_cmdshell runs as a background process. So basically, you only invoke code from sql server that can run without user intervention - the whole idea is to run something, grab the result and continue (that does not require user input or user interface).

The following is from ms:
"There is no way to get the user input from any DB-Library (DB-Lib) client utility to the server"

So what does the web page actually do ? What happens when the asp page is invoked ?