I have a client that I setup with such a scenario about 2 years ago and everything has been going smooth ever since. It consists of a .NET windows service that uses uniOLEDB and UO.NET to keep sql server data in sync with a universe database. The service itself is a 'dumb' data mover that can handle a number of different data scenarios - creating the destination datatables, moving entire files, specific records, custom exported data such as report data etc. The data that is synchronized is determined entirely on the universe side of things. The service kicks of at regular intervals ( hourly ) and checks with universe for queued sync requests. Very customizable. Very slick. If I could find the time I'd like to package it up for sale along with a number of other services I've written.
Gerry -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tony Gravagno Sent: February 2, 2006 19:15 To: u2-users@listserver.u2ug.org Subject: RE: [U2][UV] Universe and SQL Server Just because you're exchanging data with a relational database doesn't mean you need to use SQL or ODBC with U2. You can use a middle-tier ADO.NET dataset, and then use that as your data source for SQL Server or Universe. For ADO.NET to U2 connectivity I'd use mv.NET, but you can use any tool available in this market once you setup the structures. The way you're doing this now, and this middle-tier approach, both allow you some freedom to make the data exchanges asynchronously - if one system is down the other can continue exporting or importing data with the middle tier. You don't have that option if you go direct from Universe to relational. It also allows you to change your data extraction methods without affecting the "other" side: If you're doing SQL queries from Universe, you need someone who understands both SQL and Universe to get in and make changes. If you keep the sides separate then you don't need one person with all skillsets. mv.NET is ideal for this sort of thing. With one of the built-in libraries (Core Objects) you can exchange data just with calls to BASIC rules, or you can open files and process dynamic arrays directly using familiar syntax from VB.NET, C#, or other languages. With another included library (Adapter Objects) you can use SQL-like syntax with DICT references for doing retrievals _and_ insert/update/delete operations. With SQL Server 2005 now allowing managed code for stored procedures, SQL Server is actually getting closer to the MV model, with code inside the environment itself. I haven't tried this, but I'd guess you could use mv.NET or UO.NET directly within SQL Server to poll Universe for data. The session pooling capabilities in mv.NET also allow for many users to process queries from a single DBMS license. Of course higher traffic or longer operations will require more licenses, but for short burst transactions this can be very economical. For a single process that sends data from Universe to SQL Server you only need one low-cost license. If you want to do this without getting an mv.NET developer license, I'll be happy to whip up a data exchange component to suit your needs, it would run as a Windows service that exchanges data in the background. I've been wanting to do this for a while anyway. The advantage of having a developer license of course is that instead of purchasing a single-use component you'll have a general purpose toolkit for doing all sorts of things. E-mail for more info: Tony Gravagno TG@ removethisNebula-RnD.com Nick Cipollina wrote: > Hello all, > > We are currently looking into ways to share our data between universe > and SQL Server, and I am trying to get some suggestions on the best > way to do this. We are currently generating text files in UV and > ftp'ing those files to a designated location. We have a task > scheduler that picks up the files when they arrive and inserts them > into SQL server. I know there are ways to communicate directly > between SQL server and Universe and I am looking for the best way to > do this. It doesn't matter if Universe reaches out to SQL Server or > vice versa. > Thanks. > > Nick Cipollina > > Pick Programmer > ACS Heritage, Inc. > 2810 North Parham Road, Suite 210 > Richmond, VA 23294 > (804)644-8707 x 314 > > ------- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
