Link to home
Start Free TrialLog in
Avatar of bcp5190
bcp5190

asked on

How to Run Activex COM from sql server job

I have written a activex dll in VB and i'm using sql server to schedule.
I used follwing code in sql agent job and activex package. In package it works fine but in sql job it cannot download data from website.

Dim sURL    
Dim oWininet
Set oWininet = CreateObject("ObjWininet.Download")
sURL = "http://www.yahoo.com"
strusername = Username
strPassword = Password
Call oWininet.Save(sURL, strusername, strPassword, "e:\temp\yahoo.htm")
Set oWininet = Nothing

------------------------------------
I'm using following api in activex com

Private Declare Function HttpOpenRequest Lib "wininet.dll" Alias "HttpOpenRequestA" (ByVal hHttpSession As Long, ByVal sVerb As String, ByVal sObjectName As String, ByVal sVersion As String, ByVal sReferer As String, ByVal something As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long

Private Declare Function HttpSendRequest Lib "wininet.dll" Alias "HttpSendRequestA" (ByVal hHttpRequest As Long, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal sOptional As String, ByVal lOptionalLength As Long) As Integer

Private Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer


Private Declare Function InternetSetOption Lib "wininet.dll" Alias "InternetSetOptionA" (ByVal hInternet As Long, ByVal lOption As Long, ByRef sBuffer As Any, ByVal lBufferLength As Long) As Integer

......
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

What error is occurring.

If you are using SQL 2K, FTP download is built in.

Also, please maintin your open questions:
How can i shhedule to run exe using at command. Date: 06/19/2002 06:51AM PST
https://www.experts-exchange.com/questions/20313716/How-can-i-shhedule-to-run-exe-using-at-command.html
How can i schedule to run myapp.exe which use SendKeys? Date: 06/20/2002 07:17AM PST  
https://www.experts-exchange.com/questions/20314283/How-can-i-schedule-to-run-myapp-exe-which-use-SendKeys.html
How can i schedule registry file? Date: 06/21/2002 09:14AM PST  
https://www.experts-exchange.com/questions/20314727/How-can-i-schedule-registry-file.html
How can i download data from website. Date: 06/21/2002 12:29PM PST
https://www.experts-exchange.com/questions/20314789/How-can-i-download-data-from-website.html
How can i schedule to run myapp.exe which use SendKeys? Date: 06/21/2002 09:17AM PST
https://www.experts-exchange.com/questions/20314729/How-can-i-schedule-to-run-myapp-exe-which-use-SendKeys.html
How can i save pdf file using internet object Date: 06/12/2002 11:41AM PST
https://www.experts-exchange.com/questions/20310920/How-can-i-save-pdf-file-using-internet-object.html
How can i automate proxy logon Authentication.  Date: 05/22/2002 10:05AM PST  
https://www.experts-exchange.com/questions/20303583/How-can-i-automate-proxy-logon-Authentication.html

The next questions have no contributions, so you should
post a message in Community Support to have them deleted:
SqlServer Job Agent  error Memory cannot be written. Date: 06/05/2002 06:49AM PST
https://www.experts-exchange.com/questions/20308096/SqlServer-Job-Agent-error-Memory-cannot-be-written.html
How can i bypass proxy logon? Date: 06/26/2002 09:51AM PST  
https://www.experts-exchange.com/questions/20316522/How-can-i-bypass-proxy-logon.html

Thanks,
Anthony
Avatar of PUMASOFT
PUMASOFT

In sql 7 you can call external exe's etc through a DTS package
Avatar of bcp5190

ASKER

I cannot use other then my own dll because o have to authetiacate and proxy logon and it perfectly works with package and from asp page. It only fail from sql job.
Without knowing all about this - it could be that the dll requires installation/registering on the relevant SQL server machine - I think - bit of a guess.
Avatar of bcp5190

ASKER

I already register dll on both pc and thats why it works on sql package. I just dont understand why its not working from sql job. Job actually runs but it downot download blank page from website. I used wininet.dll in activex com object
ASKER CERTIFIED SOLUTION
Avatar of PUMASOFT
PUMASOFT

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bcp5190

ASKER

How can i register withing DTS.
Sothing strange... In development machine If i add new administrator user and then set start up SQLServerAgent log on as This account . Then job gives same result (blank page) I think it has to do with sequrity but i don't who why. because both user (old user and new user has administrator previlages)
bcp,

to meet the issue as is I believe you need to assign appropriate permissions to the account running package from sql job using dcomcnfg utility. Potentially allow sql agent account configured to run the job to override sql server account, or check if the sql server or sql agent account has appropriate permissions on the network. Apparently when you "run" package you click on appropriate controls and launch it implicitly under authority of your own account, which is let me guess - System Administrator. If either sql agnet job or sql server is run by a different accoun you will encounter failures.

Personally prefer windows scheduled task very easy to setup and can be accessed only by Windows administrator - safe by design.

Cheers
Avatar of bcp5190

ASKER

In job if i change a scheduler user  then my job fails. That means it only works in one perticular user only. but Why?
I also try another administartor user but it also fail.

in short - security.