I take it that the extranet web server doesn't have access to the SQL
server, but the intranet web server does?
I have a couple of ideas for this: (but here are the ones I choose to share)
"Push #1"
Assuming the SQL server can access the Extranet webserver; create a job that
makes msxml.httprequests of the extranet server and pushes a fresh list of
"Job Offerings" to the server which is stored in application cache. This
would be a complete replacement of the cachce collection, unless the amount
of data is huge, in which you then push only changes periodically and
completely replace the data during offpeak hours.
"Push #2"
Same as #1, only create a .Net assembly (winform exe, no forms) that
performs the xmlhttp work and is shelled via the SQL server.
"Push #3"
Same as #2, only the server this runs from is place somewhere on the network
that can access the Intranet Server and the Extranet Server. Poll the jobs
from the Intranet server and Pull them to the Extranet server.
Here is some untested, incomplete code I was amusing myself with to solve
for #1
Sub PushJobs()
Dim oSoap 'As XMLSoap
Dim arrJobs() 'as Jobs
Dim iRet, i 'as Long
Set oSoap = New XMLSoap
'Remove All Jobs from Extranet Server
oSoap.ClearJobs
'Load all Jobs from Database as Job Objects
iRet = GetDBJobs(arrJobs)
'Loop over the jobs
If iRet > 0 Then
For i = 0 To iRet - 1
'Push the job to the extranet server
Call oSoap.PushJob(arrJobs(i))
Next 'i
End If
Set oSoap = Nothing
End Sub
Class XMLSoap
Public Sub PushJob(Job)
' Sumbits a job to the webservice
'
'
Dim iRet 'As Long
iRet = DoPushJob("Bob", "Password", JobID)
End Sub
Public Sub QueryJob(JobID)
' Asks the server if there is a specific job defined
' returns a Job Class on success
'
Dim sResult 'As String
sResult = DoQueryJob("Bob", "Password", JobID)
Set QueryJob = ParseJob(sResult)
End Sub
Private Function ParseJob(SoapMessage)
'Expected Input
'<?xml version="1.0" encoding="utf-8"?>
'<soap:Envelope xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="
http://www.w3.org/2001/XMLSchema"
xmlns:soap="
http://schemas.xmlsoap.org/soap/envelope/">
' <soap:Body>
' <QueuryJobResponse xmlns="
http://myserver/myservices/JobServices">
' <QueuryJobResult>
' <JobID>long</JobID>
' <Title>string</Title>
' <Description>string</Description>
' <HireDate>dateTime</HireDate>
' <SalaryHigh>decimal</SalaryHigh>
' <SalaryLow>decimal</SalaryLow>
' <HRContact>string</HRContact>
' <HREmail>string</HREmail>
' </QueuryJobResult>
' </QueuryJobResponse>
' </soap:Body>
'</soap:Envelope>
Dim objXML 'As DOMDocument
Dim objJob 'As Job
Set objJob = New Job
Set objXML = CreateObject("MSXML.DOMDocument")
objXML.LoadXML (SoapMessage)
If Not objXML.Parse.Error > 0 Then
objJob.JobID =
GetElementValue("soap:Envelope/soap:Body/QueuryJobResponse/QueuryJobResult/JobID",
objXML, -1)
objJob.Title =
GetElementValue("soap:Envelope/soap:Body/QueuryJobResponse/QueuryJobResult/Title",
objXML, "")
objJob.Description =
GetElementValue("soap:Envelope/soap:Body/QueuryJobResponse/QueuryJobResult/Description",
objXML, "")
'
' Populate Accordingly
'
Set ParseJob = objJob
Else
Exit Function
End If
Set objXML = Nothing
End Function
Public Function DoQueryJob(UserName, Password, JobID)
'Expected Output
'<?xml version="1.0" encoding="utf-8"?>
'<soap:Envelope xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="
http://www.w3.org/2001/XMLSchema"
xmlns:soap="
http://schemas.xmlsoap.org/soap/envelope/">
' <soap:Header>
' <CredentialSoapHeader
xmlns="
http://myserver/myservices/JobServices">
' <Username>string</Username>
' <Password>string</Password>
' </CredentialSoapHeader>
' </soap:Header>
' <soap:Body>
' <QueuryJob xmlns="
http://myserver/myservices/JobServices">
' <JobID>int</JobID>
' </QueuryJob>
' </soap:Body>
'</soap:Envelope>
Dim sReturn 'As String
Dim objDoc 'DOMDocument
Dim XMLHttpRequest 'As MSXML.XMLHTTP
Set objDoc = CreateObject("MSXML2.DOMDocument")
SetElement "soap:Envelope/soap:Header/CredentialSoapHeader/Username",
objDoc, UserName
SetElement "soap:Envelope/soap:Header/CredentialSoapHeader/Password",
objDoc, Password
SetElement "soap:Envelope/soap:Body/QueuryJob/JobID", objDoc, JobID
Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")
XMLHttpRequest.Open "GET",
"
https://myserver/myservices/JobServices.asmx", True
XMLHttpRequest.Send objDoc.Text
sReturn = XMLHttpRequest.Response
Set XMLHttpRequest = Nothing
Set objDoc = Nothing
End Function
Private Function GetElementValue(XPath, XMLDoc, ValueOnVoid)
Dim objNode
objNode = objXML.SelectSingleNode(XPath)
If Not objNode Is Nothing Then
GetElementValue = objNode.NodeTypedValue
Else
GetElementValue = ValueOnVoid
End If
End Function
Private Sub SetElement(XPath, XMLDoc, Value)
'split the path into parts using split()
'ensure each path part exists
'set value
End Sub
End Class
Class Job
Private mJobID 'As Long
Private mTitle 'As String
Private mDescription 'As String
Private mHireDate 'As Date
Private mSalaryHigh 'As Currency
Private mSalaryLow 'As Currency
Private mHRContact 'As String
Private mHREmail 'As String
Private Sub Class_Intialize()
mJobID = ""
mTitle = ""
mDescription = ""
mHireDate = #1/1/1990#
mSalaryHigh = 0@
mSalaryLow = 0@
mHRContact = ""
mHREmail = ""
End Sub
Public Property Let JobID(Value)
mJobID = Value
End Property
Public Property Get JobID()
JobID = mJobID
End Property
' Populate Accordingly
'
'
End Class
