[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Send Steady UDP Stream From MSSQL

Posted on 2013-01-25
11
Medium Priority
?
110 Views
Last Modified: 2015-10-01
HELLO...

We're looking either for some advice, direction, and/or a recommendation here.

We have an MSSQL database application, that runs our Point of Sale front end.  The publisher of the Video Surveillance system that we use, to properly "integrate" the POS data to their system, requires us to transmit the MSSQL data...via UDP...in an ASCII "CSV" stream...to their servers.  Note...this is a ONE-WAY stream...MSSQL must output *TO* the Vidoeo Surveillance system, and *NEVER* vice-versa.  Because this is near real-time transactionally based, there will be a lot of traffic as a result...and we both expect that, and have the network infrastructure to handle it.

Having never done this before...we were hoping that someone that has done this...can shed some light here.  Even if there's some 3rd Party package that can handle the data conduit...certainly that would be fine.

So...any suggestions y'all?  Anyone ever done this?  Looking for something tried & true.  Please let us know...and THANK YOU very much in advance!...Mark
0
Comment
Question by:datatechcorp
  • 6
  • 5
11 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38823428
Hi,

Soooo, what will you do with this stream. What is the point? How will you use this to recover?

SQL - especially SQL 2012 - has a bunch of different dr/recovery options - backups, replication, mirroring, log shipping, clustering. If I'm right, I suggest you look at that kind of option, rather than rolling your own.

Why not just do a network backup - use the inbuilt compression and do a copy_only backup to that network share?

Regards
  David

PS Please confirm version and edition of SQL, size of database, expected transaction volume ...
0
 

Author Comment

by:datatechcorp
ID: 38823465
Hi David...

Thank you for replying.  The MSSQL version we're on...is 2008 R2.  I think I understand your suggestion, but it cannot apply in this situation.

Because this is a Video Surveillance system...what has to occur...is that as data is appended to a table on our MSSQL Server, a (corollary) "stream" of that *SAME* data...needs to be ported out...to a Video Surveillance "Server"...and the author's system (it's Agillence)...will *ONLY* accept a UDP formatted stream.  Uugh, I know... :-(

Is there a way to accomplish this with, say, a trigger on the MSSQL table...whereby the "output" is then "redirected" to, say, UDP?  Any thoughts here?

Please let us know...Thanks!...Mark
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38823474
Hi,

Short answer - no, not easily.

That was why I was suggesting all the standard SQL mechanisms.

A T-SQL trigger is only going to do an insert/update/delete. While triggers are great, they are a catch 22 - of course you could invoke some complex system stored procedure or write your own CLR procedure/function, but that is too big to be called by a trigger.

Okay, thinking a bit outside the box - get the insert trigger to put the rowid of that record/those records (never assume that what fired a trigger is only one row!) in a queue (use a table for this)
Schedule a task - can get it as fine-grained as every minute - to use the CLR procedure you will write to do this to extract data and write data to the stream. As it does so, it removes the ids from the queue (table).

Now, writing CLR stuff ain't for the novice developer, but its a lot easier than developing an extended procedure which is how you'll have had to do this with SQL 2000.

HTH
  David

PS What is the data that is being inserted? Is it surveillance ie time-lapse photo?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:datatechcorp
ID: 38825118
It's all the POS system's transactional "pieces"...i.e., Clerk starts a transaction...clerk rings up an item...clerk rings up a second item...cleark does a price override...clerk adds a third item...clerk voids a line...clerk receives tender payment for sale...clerk gives change due...and so forth.

With Surveillance Systems that "integrate" (actually, "tie-in" would be more appropriate terminology)...all those facets of a transaction...are coordinated...with the video feed.  Thus, when a security audit is necessary, the data, with the video, coexist.

I hope this makes sense...Thanks!...Mark
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38825145
Hi Mark,

Makes sense.

Did my comments about writing a CLR procedure help any?

How have other clients of your supplier managed this?

Regards
  David
0
 

Author Comment

by:datatechcorp
ID: 38825165
Yeah...it kinda' makes sense David.  Don't have any others that have used Agilence...but, apparently, Agilence is picking up momentum these days...and they WILL NOT write an interface (uugh!).  They tell the end-user, flat-out, "here" is our "UDP" spec...here are the maps to the field data we expect...no go ahead and itegrate it...y'know?

We've *never* had to go down this road in the past...like, ever.  Have you written such a CLR (and/or Extended Stored Procedure)?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 38825197
Hi Mark,

While I have some C++ in my background, I'm not fluent at present.

However, I took a small console app, and converted it to a CLR function. Its not that hard. Specifically to stream the data, that might need a bit more work ...

All the best
  David
0
 

Author Comment

by:datatechcorp
ID: 39043273
Hi David...

Sorry...it's been a little while...but we had some family emergencies, and I've been quite "bi-coastal" in the past (2) months.  I'd like to pick this conversation up again, when you have time.

Thank you for your patience!...Mark
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39045105
Hi Mark,

Sorry to hear about your family. Hope its sorted now.

Regards
  David
0
 

Author Comment

by:datatechcorp
ID: 39275893
Hi All...

Getting back into swing again...please feel free to chime in on responses...Thanks!...Mark
0
 

Author Closing Comment

by:datatechcorp
ID: 41020035
Hi David...

I just realized...that with all the "family drama" I had at that time (my Dad passing)...that I'd abandoned this question/issue.  Soooooo sorry for that!  I'm granting you all the points.  Thanks & take care!...Mark
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 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