We have a database in application in SQL 2000 Server (Standard). We want to
configure a table so that when there is add, delete, or update in the table,
an Outlook calendar of a user should automatically be updated. The user is a
member of the same domain as the service account and as the Windows Server
2003 (Standard) that hosts the database. The user's mailbox is hosted by
Exchange Server 2003 Enterprise in the same domain. I am not sure if SQL can
do this, or if we have to use a middleware such as VB.NET or C#.
Please help. Thanks!
ODHi OD,
Its a pitty you aren't using SQL Server 2005, its a perfect job for CLR.
But, yes, you do need to code this outside of SQL Server.
In SQL you will need to catch the 'add', 'delete' or 'update'; this is best
done using a trigger. You can then operator a table like a queue, the
trigger writes into the table and then your VB.NET / C# application/service
reads the queue and does the biz in Exchange.
The VB.NET to do the add appointment bit is below...
Public Sub Exchange_AddAppointment(ByRef page As Page, ByVal iEventID As
Integer)
' Variables
Dim strExchSvrName As String
Dim strMailbox As String
Dim strCalendarUri As String
Dim strApptItem As String
Dim strDomain As String
Dim strUserName As String
Dim strPassword As String
Dim strApptRequest As String
Dim strMailInfo As String
Dim strCalInfo As String
Dim strXMLNSInfo As String
Dim strHeaderInfo As String
Dim PROPPATCHRequest As System.Net.HttpWebRequest
Dim PROPPATCHResponse As System.Net.WebResponse
Dim MyCredentialCache As System.Net.CredentialCache
Dim bytes() As Byte
Dim PROPPATCHRequestStream As System.IO.Stream
' Exchange server name
strExchSvrName = ConfigurationSettings.AppSettings("ExchangeServer")
' Mailbox folder name.
strMailbox = "tonyrogerson"
' Appointment item.
strApptItem = "CRMEvent.eml"
' URI of the user's calendar folder.
strCalendarUri = "http://" & strExchSvrName & "/exchange/" & _
strMailbox & "/Calendar/"
' User name and password of appointment creator.
strUserName =
ConfigurationSettings.AppSettings("CRMExchangeLogonName")
strDomain =
ConfigurationSettings.AppSettings("CRMExchangeLogonDomain")
strPassword =
ConfigurationSettings.AppSettings("CRMExchangeLogonPWD")
' XML namespace info for the WebDAV request.
strXMLNSInfo = "xmlns:g=""DAV:"" " & _
"xmlns:e=""http://schemas.microsoft.com/exchange/"" " & _
"xmlns:mapi=""http://schemas.microsoft.com/mapi/"" " & _
"xmlns:mapit=""http://schemas.microsoft.com/mapi/proptag/"" " & _
"xmlns:x=""xml:"" xmlns:cal=""urn:schemas:calendar:"" " & _
"xmlns:dt=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"" " &
_
"xmlns:header=""urn:schemas:mailheader:"" " & _
"xmlns:mail=""urn:schemas:httpmail:"""
' Set the appointment item properties. See the documentation on the
properties
' in the urn:schemas:calendar: for more information.
strCalInfo = "<cal:location>meetappt Location</cal:location>" & _
"<cal:dtstart
dt:dt=""dateTime.tz"">2004-12-19T23:00:00.000Z</cal:dtstart>" & _
"<cal:dtend
dt:dt=""dateTime.tz"">2004-12-19T23:30:00.000Z</cal:dtend>" & _
"<cal:instancetype dt:dt=""int"">0</cal:instancetype>" & _
"<cal:busystatus>BUSY</cal:busystatus>" & _
"<cal:meetingstatus>CONFIRMED</cal:meetingstatus>" & _
"<cal:alldayevent dt:dt=""boolean"">0</cal:alldayevent>" & _
"<cal:responserequested
dt:dt=""boolean"">1</cal:responserequested>"
' Set the required attendee of the appointment.
strHeaderInfo = "<header:to>" & strMailbox & "</header:to>"
' Set the subject of the appointment.
strMailInfo = "<mail:subject>Test Appointment
Subject</mail:subject>" & _
"<mail:htmldescription>Let's meet here</mail:htmldescription>"
' Build the XML body of the PROPPATCH request.
strApptRequest = "<?xml version=""1.0""?>" & _
"<g:propertyupdate " & strXMLNSInfo & ">" & _
"<g:set><g:prop>" & _
"<g:contentclass>urn:content-classes:appointment</g:contentclass>"
& _
"<e:outlookmessageclass>IPM.Appointment</e:outlookmessageclass>"
& _
strMailInfo & _
strCalInfo & _
strHeaderInfo & _
"<mapi:finvited dt:dt=""boolean"">1</mapi:finvited>" & _
"</g:prop></g:set>" & _
"</g:propertyupdate>"
Try
' Create a new CredentialCache object and fill it with the
network
' credentials required to access the server.
MyCredentialCache = New System.Net.CredentialCache
MyCredentialCache.Add(New System.Uri(strCalendarUri), _
"NTLM", _
New
System.Net.NetworkCredential(strUserName, strPassword, strDomain) _
)
' Create the HttpWebRequest object.
PROPPATCHRequest =
CType(System.Net.HttpWebRequest.Create(strCalendarUri & strApptItem), _
System.Net.HttpWebRequest)
' Add the network credentials to the request.
PROPPATCHRequest.Credentials = MyCredentialCache
' Specify the PROPPATCH method.
PROPPATCHRequest.Method = "PROPPATCH"
' Set the content type header.
PROPPATCHRequest.ContentType = "text/xml"
' Encode the body using UTF-8.
bytes = System.Text.Encoding.UTF8.GetBytes(strApptRequest)
' Set the content header length. This must be
' done before writing data to the request stream.
PROPPATCHRequest.ContentLength = bytes.Length
' Get a reference to the request stream.
PROPPATCHRequestStream = PROPPATCHRequest.GetRequestStream()
' Write the message body to the request stream.
PROPPATCHRequestStream.Write(bytes, 0, bytes.Length)
' Close the Stream object to release the connection
' for further use.
PROPPATCHRequestStream.Close()
' Create the appointment in the Calendar folder of the
' user's mailbox.
PROPPATCHResponse = CType(PROPPATCHRequest.GetResponse(),
System.Net.HttpWebResponse)
page.Response.Write("Appointment successfully created.")
' Clean up.
PROPPATCHResponse.Close()
Catch ex As Exception
' Catch any exceptions. Any error codes from the PROPPATCH
' or MOVE method requests on the server will be caught
' here, also.
page.Response.Write(ex.Message)
End Try
End Sub
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> We have a database in application in SQL 2000 Server (Standard). We want
> to configure a table so that when there is add, delete, or update in the
> table, an Outlook calendar of a user should automatically be updated. The
> user is a member of the same domain as the service account and as the
> Windows Server 2003 (Standard) that hosts the database. The user's mailbox
> is hosted by Exchange server 2003 Enterprise in the same domain. I am not
> sure if SQL can do this, or if we have to use a middleware such as VB.NET
> or C#.
> Please help. Thanks!
> OD
>|||you can do it in TSQL by refering to the activex of outlook or directly
acessing exchange via activex api if there is any. Or u can make an activex
to perform this task and call it via ur TSQL in trigger. Or u can implement
an extended stored procedure. See u have got too many options. Choose which
looks suitable.
Cheers
"OD" <oludan@.hotmail.com> wrote in message
news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> We have a database in application in SQL 2000 Server (Standard). We want
> to configure a table so that when there is add, delete, or update in the
> table, an Outlook calendar of a user should automatically be updated. The
> user is a member of the same domain as the service account and as the
> Windows Server 2003 (Standard) that hosts the database. The user's mailbox
> is hosted by Exchange server 2003 Enterprise in the same domain. I am not
> sure if SQL can do this, or if we have to use a middleware such as VB.NET
> or C#.
> Please help. Thanks!
> OD
>|||I wouldn't recommend activeX inside a trigger going out of SQL, remember the
trigger is in a transaction and the longer that transaction exists the
higher the chances of blocking; also, if the ActiveX fails it could be
problematic.
Make the trigger write the data out into another table or using a flag on
the base table and use ActiveX as you say.
tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mubashir Khan" <m@.n.com> wrote in message
news:uJGGLmnJGHA.140@.TK2MSFTNGP12.phx.gbl...
> you can do it in TSQL by refering to the activex of outlook or directly
> acessing exchange via activex api if there is any. Or u can make an
> activex to perform this task and call it via ur TSQL in trigger. Or u can
> implement an extended stored procedure. See u have got too many options.
> Choose which looks suitable.
> Cheers
> "OD" <oludan@.hotmail.com> wrote in message
> news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||First consider if you really want to hit Exchange server *every* time one of
these tables are inserted, updated or deleted, and then estimate how many
hits per day this would be. I would reccomend having a trigger insert or
update a row in an audit table whenever a relevent event on the table takes
place. If data modifications are so important that they warrant
automatically scheduling someone's calendar in Outlook, then you may want to
retain an audit history in this table anyway. A DTS package can then poll
this audit table and execute a VBScript task that makes the calls to
Exchange Server, and the DTS package can be scheduled as job to run at
specific internals. There are tons of information on MSDN about how to
script Exchange Server.
http://www.microsoft.com/technet/sc...ap.msp
x
"OD" <oludan@.hotmail.com> wrote in message
news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> We have a database in application in SQL 2000 Server (Standard). We want
> to configure a table so that when there is add, delete, or update in the
> table, an Outlook calendar of a user should automatically be updated. The
> user is a member of the same domain as the service account and as the
> Windows Server 2003 (Standard) that hosts the database. The user's mailbox
> is hosted by Exchange server 2003 Enterprise in the same domain. I am not
> sure if SQL can do this, or if we have to use a middleware such as VB.NET
> or C#.
> Please help. Thanks!
> OD
>|||Great information! I will tryout the code you supplied. This is very
helpful. I hope to migrate to SQL server 2005 in a by summer.
Thanks you again
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uwjS2inJGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi OD,
> Its a pitty you aren't using SQL Server 2005, its a perfect job for CLR.
> But, yes, you do need to code this outside of SQL Server.
> In SQL you will need to catch the 'add', 'delete' or 'update'; this is
> best done using a trigger. You can then operator a table like a queue, the
> trigger writes into the table and then your VB.NET / C#
> application/service reads the queue and does the biz in Exchange.
> The VB.NET to do the add appointment bit is below...
> Public Sub Exchange_AddAppointment(ByRef page As Page, ByVal iEventID
> As Integer)
>
> ' Variables
> Dim strExchSvrName As String
> Dim strMailbox As String
> Dim strCalendarUri As String
> Dim strApptItem As String
> Dim strDomain As String
> Dim strUserName As String
> Dim strPassword As String
> Dim strApptRequest As String
> Dim strMailInfo As String
> Dim strCalInfo As String
> Dim strXMLNSInfo As String
> Dim strHeaderInfo As String
> Dim PROPPATCHRequest As System.Net.HttpWebRequest
> Dim PROPPATCHResponse As System.Net.WebResponse
> Dim MyCredentialCache As System.Net.CredentialCache
> Dim bytes() As Byte
> Dim PROPPATCHRequestStream As System.IO.Stream
>
> ' Exchange server name
> strExchSvrName =
> ConfigurationSettings.AppSettings("ExchangeServer")
>
> ' Mailbox folder name.
> strMailbox = "tonyrogerson"
>
> ' Appointment item.
> strApptItem = "CRMEvent.eml"
>
> ' URI of the user's calendar folder.
> strCalendarUri = "http://" & strExchSvrName & "/exchange/" & _
> strMailbox & "/Calendar/"
>
> ' User name and password of appointment creator.
> strUserName =
> ConfigurationSettings.AppSettings("CRMExchangeLogonName")
> strDomain =
> ConfigurationSettings.AppSettings("CRMExchangeLogonDomain")
> strPassword =
> ConfigurationSettings.AppSettings("CRMExchangeLogonPWD")
>
> ' XML namespace info for the WebDAV request.
> strXMLNSInfo = "xmlns:g=""DAV:"" " & _
> "xmlns:e=""http://schemas.microsoft.com/exchange/"" " & _
> "xmlns:mapi=""http://schemas.microsoft.com/mapi/"" " & _
> "xmlns:mapit=""http://schemas.microsoft.com/mapi/proptag/"" " &
> _
> "xmlns:x=""xml:"" xmlns:cal=""urn:schemas:calendar:"" " & _
> "xmlns:dt=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"" " &
> _
> "xmlns:header=""urn:schemas:mailheader:"" " & _
> "xmlns:mail=""urn:schemas:httpmail:"""
>
> ' Set the appointment item properties. See the documentation on
> the properties
> ' in the urn:schemas:calendar: for more information.
> strCalInfo = "<cal:location>meetappt Location</cal:location>" & _
> "<cal:dtstart
> dt:dt=""dateTime.tz"">2004-12-19T23:00:00.000Z</cal:dtstart>" & _
> "<cal:dtend
> dt:dt=""dateTime.tz"">2004-12-19T23:30:00.000Z</cal:dtend>" & _
> "<cal:instancetype dt:dt=""int"">0</cal:instancetype>" & _
> "<cal:busystatus>BUSY</cal:busystatus>" & _
> "<cal:meetingstatus>CONFIRMED</cal:meetingstatus>" & _
> "<cal:alldayevent dt:dt=""boolean"">0</cal:alldayevent>" & _
> "<cal:responserequested
> dt:dt=""boolean"">1</cal:responserequested>"
>
> ' Set the required attendee of the appointment.
> strHeaderInfo = "<header:to>" & strMailbox & "</header:to>"
>
> ' Set the subject of the appointment.
> strMailInfo = "<mail:subject>Test Appointment
> Subject</mail:subject>" & _
> "<mail:htmldescription>Let's meet here</mail:htmldescription>"
>
> ' Build the XML body of the PROPPATCH request.
> strApptRequest = "<?xml version=""1.0""?>" & _
> "<g:propertyupdate " & strXMLNSInfo & ">" & _
> "<g:set><g:prop>" & _
>
> "<g:contentclass>urn:content-classes:appointment</g:contentclass>" & _
> "<e:outlookmessageclass>IPM.Appointment</e:outlookmessageclass>"
> & _
> strMailInfo & _
> strCalInfo & _
> strHeaderInfo & _
> "<mapi:finvited dt:dt=""boolean"">1</mapi:finvited>" & _
> "</g:prop></g:set>" & _
> "</g:propertyupdate>"
>
> Try
> ' Create a new CredentialCache object and fill it with the
> network
> ' credentials required to access the server.
> MyCredentialCache = New System.Net.CredentialCache
> MyCredentialCache.Add(New System.Uri(strCalendarUri), _
> "NTLM", _
> New
> System.Net.NetworkCredential(strUserName, strPassword, strDomain) _
> )
> ' Create the HttpWebRequest object.
> PROPPATCHRequest =
> CType(System.Net.HttpWebRequest.Create(strCalendarUri & strApptItem), _
> System.Net.HttpWebRequest)
>
> ' Add the network credentials to the request.
> PROPPATCHRequest.Credentials = MyCredentialCache
>
> ' Specify the PROPPATCH method.
> PROPPATCHRequest.Method = "PROPPATCH"
>
> ' Set the content type header.
> PROPPATCHRequest.ContentType = "text/xml"
>
> ' Encode the body using UTF-8.
> bytes = System.Text.Encoding.UTF8.GetBytes(strApptRequest)
>
> ' Set the content header length. This must be
> ' done before writing data to the request stream.
> PROPPATCHRequest.ContentLength = bytes.Length
>
> ' Get a reference to the request stream.
> PROPPATCHRequestStream = PROPPATCHRequest.GetRequestStream()
>
> ' Write the message body to the request stream.
> PROPPATCHRequestStream.Write(bytes, 0, bytes.Length)
>
> ' Close the Stream object to release the connection
> ' for further use.
> PROPPATCHRequestStream.Close()
>
> ' Create the appointment in the Calendar folder of the
> ' user's mailbox.
> PROPPATCHResponse = CType(PROPPATCHRequest.GetResponse(),
> System.Net.HttpWebResponse)
>
> page.Response.Write("Appointment successfully created.")
>
> ' Clean up.
> PROPPATCHResponse.Close()
>
> Catch ex As Exception
> ' Catch any exceptions. Any error codes from the PROPPATCH
> ' or MOVE method requests on the server will be caught
> ' here, also.
> page.Response.Write(ex.Message)
>
> End Try
>
> End Sub
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||Thank you very much guys. I know now, that I have a few option. I need to
complete this task in two w
s. This solution will be used to schedule our
tecnicians when a user/clients enter a task requests. So it may be necessary
to touch Exchange server at least every time there is an insert or delete.
Thanks again.
OD
"JT" <someone@.microsoft.com> wrote in message
news:uVWETSoJGHA.648@.TK2MSFTNGP14.phx.gbl...
> First consider if you really want to hit Exchange server *every* time one
> of these tables are inserted, updated or deleted, and then estimate how
> many hits per day this would be. I would reccomend having a trigger insert
> or update a row in an audit table whenever a relevent event on the table
> takes place. If data modifications are so important that they warrant
> automatically scheduling someone's calendar in Outlook, then you may want
> to retain an audit history in this table anyway. A DTS package can then
> poll this audit table and execute a VBScript task that makes the calls to
> Exchange Server, and the DTS package can be scheduled as job to run at
> specific internals. There are tons of information on MSDN about how to
> script Exchange Server.
> http://www.microsoft.com/technet/sc...ap.m
spx
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||
quote:
Originally posted by Tony Rogerson
Hi OD,
Its a pitty you aren't using SQL Server 2005, its a perfect job for CLR.>
Could you help out on how you would acomplish this using the SQL 2005 CLR'
Oz
