March 10, 2010 at 2:01 pm
Even though it came out with SQL server 2005, I am new sql service broker...
I have a process whereby I would like to accomplish the following... I am wondering if the sql broker service may be a way to go ?
The setting includes many client workstations using a VB.net application to
communicate with a database via ADO connections.
The workstations will be adding "transactional" information into some sql
server tables.
Been there, done that... so far, so good...
Unfortunately, that is only half of the "transaction"...
Once the first half of the transaction has been successfully committed to
the sql tables, elements of this data must then be used to create the second
half of the transaction which includes pushing it into an ERP system (via a
COM oriented procedure). Actually, what I would like to do is invoke a
second VB.net program using data from the sql tables. See the basic vb code process below at end of this message...
All the examples I have seen thus far show TSQL being used to update a table.
Can you actually run a vb.net program using the service broker ? Should
you ? Any online samples detailing this ? Good books illustrating this - in vb.net not C# ?
I AM interested in invoking the second half of the transaction as soon as
possible following the first half of the transaction.
However, I am not interested in reversing the first half of the transaction
if the second half of the transaction is not successful.
-------------------------------------------
Brief flow description...
UI
- gets data from sql tables and inputs transactions back to sql tables
- Do I tell sql server in the UI that the second piece of the transaction
needs to be started, or will the broker know based on how it is set-up or on
some type of "trigger" ?
NEXT Step - below is a pared down version of what happens next - it needs to
be loosely coupled and not stop the part (above) from continuing
- Build xml strings Parameter and Transaction
Dim strParameters As StringBuilder = New StringBuilder
strParameters ("<?xml version=""1.0"" encoding=""Windows-1252""?>")
Dim strParameters As String = strParameters ()
Dim strTransaction As New StringBuilder
strTransaction ("<?xml version=""1.0"" encoding=""Windows-1252""?>")
Dim strTransaction As String = strTransaction ()
- Post transaction to ERP which utilizes a COM object
xmlout = Post(strParameters ,strTransaction )
- Read the xmlout to look for errors or a successful transaction ID
Dim XmlDoc As New XmlDocument
XmlDoc.LoadXml(xmlout)
For Each ErrorNumbernode In
XmlDoc.DocumentElement.SelectNodes("//ErrorNumber")
XErrorNumber = XErrorNumber & ErrorNumbernode.InnerText
Next
- Write results back to the sql table - back to the same row that initiated
the transaction
cnn = New SqlConnection(connectionString)
cnn.Open()
Dim cmd6 As New SqlCommand
cmd6 = cnn.CreateCommand
cmd6.CommandText = "spResults"
cmd6.ExecuteNonQuery()
intOutCnt3 = Convert.ToInt32(cmd6.Parameters("@OutCnt").Value())
What is your opinion on how to handle the second piece ?
It appears that there are many options, but how to choose the right one ?
Should I use sql service broker to call a webservice to handle the second
piece ? Is that possible ? What about a .net assembly that contains the
code above ?
It appears that using clr in stored proc is not a good idea in this case- I
have read where that may be considered "unsafe" due to COM component being
employed ? Also performance and memory leaks appear to be associated with
COM objects being used in this fashion ?
Thanks in advance !
March 11, 2010 at 7:55 am
This seems like a good opportunity to use Service Broker.
I can't find it now, but I have read an article/blog post where the author used Service Broker and a .NET service to transfer data. Basically the person had a stored procedure that RECEIVED/READ the data from the queue and they called that procedure in the .NET service and then processed the results when there were results. If I find it I'll post it here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply