Solved

How to Run Activex COM from sql server job

Posted on 2002-07-22
10
279 Views
Last Modified: 2013-11-25
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

......
0
Comment
Question by:bcp5190
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7170484
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
http://www.experts-exchange.com/winnt/Q_20313716.html
How can i schedule to run myapp.exe which use SendKeys? Date: 06/20/2002 07:17AM PST  
http://www.experts-exchange.com/mssql/Q_20314283.html
How can i schedule registry file? Date: 06/21/2002 09:14AM PST  
http://www.experts-exchange.com/winprog/Q_20314727.html
How can i download data from website. Date: 06/21/2002 12:29PM PST
http://www.experts-exchange.com/visualbasic/Q_20314789.html
How can i schedule to run myapp.exe which use SendKeys? Date: 06/21/2002 09:17AM PST
http://www.experts-exchange.com/visualbasic/Q_20314729.html
How can i save pdf file using internet object Date: 06/12/2002 11:41AM PST
http://www.experts-exchange.com/visualbasic/Q_20310920.html
How can i automate proxy logon Authentication.  Date: 05/22/2002 10:05AM PST  
http://www.experts-exchange.com/visualbasic/Q_20303583.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
http://www.experts-exchange.com/mssql/Q_20308096.html
How can i bypass proxy logon? Date: 06/26/2002 09:51AM PST  
http://www.experts-exchange.com/visualbasic/Q_20316522.html

Thanks,
Anthony
0
 
LVL 2

Expert Comment

by:PUMASOFT
ID: 7171352
In sql 7 you can call external exe's etc through a DTS package
0
 

Author Comment

by:bcp5190
ID: 7171757
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.
0
 
LVL 2

Expert Comment

by:PUMASOFT
ID: 7171827
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.
0
 

Author Comment

by:bcp5190
ID: 7172051
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Accepted Solution

by:
PUMASOFT earned 20 total points
ID: 7172099
Have you tried registering the DLL as a custom task within DTS designer in SQL server - again a bit of a wild guess
0
 

Author Comment

by:bcp5190
ID: 7172585
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)
0
 
LVL 9

Expert Comment

by:miron
ID: 7173395
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
0
 

Author Comment

by:bcp5190
ID: 7188839
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.

0
 
LVL 9

Expert Comment

by:miron
ID: 7188903
in short - security.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now