Apr 29 2011

How to setup Oracle Database Gateway for SQL Server

Category: Oracle DB,SQL ServerRussell Pitre @ 11:08 pm

We’ve been working on an ERP implementation at work for the past two or three months and up until a few days ago we have been making steady progress. Currently, we are extracting data from 3 mssql databases into another mssql database that is used as a staging database through a set of stored procedures.  Here we are translating the data to fit the ERP data model.  From the staging database we then use another set of stored procedures to move the data to an Oracle 11g database via a linked server.  This approach worked fine until we started to move tables with records reaching over 100,000 and saw this error:

SQL Server Database Error: The OLE DB provider “OraOLEDB.Oracle” for linked server “CONV” could not INSERT INTO table “[OraOLEDB.Oracle]“

I Google’d around for a bit looking for configuration hints but was unable to find anything helpful.

Continue reading “How to setup Oracle Database Gateway for SQL Server”

Feb 24 2010

An Event Driven Service Oriented Solution – Introduction

Category: ActiveMQ,Camel,Java,Spring Framework,SQL ServerRussell Pitre @ 7:55 pm

This is the first post in a series on the design and implementation of an event-driven service oriented solution using SQL Server 2008, Apache ActiveMQ, Apache Camel, and Spring Web Services along with a whole slew of other technologies.


I work for a medium-sized construction management firm in the New England area. We use an off-the-shelf closed source software product to facilitate our core business processes. This is a typical fat client, whereby the business logic is executed on the client side and the data is then persisted to a central SQL Server database.

We’ve been able to mitigate our lack of integration options by using server-side SQL Server triggers and stored procedures. We use INSERT, UPDATE, & DELETE triggers to execute some of our own custom business logic. We’ve been able to do financial calculations, update columns based upon conditions of other columns, insert new records into “queue” tables, your typical SQL trigger use cases. This is about as far as we’ve been able to push the use of triggers without compromising the performance of the system and even today, on high transactional days, we see sporadic performance issues. Triggers are very powerful but do have their limitations such as being synchronous in nature and can cause some serious problems if you don’t know what you’re doing.

Today, we are running SQL Server 2000 with plans to upgrade in the very near future to SQL Server 2008 . One new feature we’ll be seeing is the Service Broker, which can be utilized for asynchronous triggers.

With the use of CLR stored procedures we have the power of the .Net framework at our hands to call services hosted outside of SQL Server and then by combining CLR stored procedures with the Service Broker we can design very powerful and scalable business solutions.

The Big Idea

Below is the system diagram of our SQL server event-driven service oriented solution. The diagram is pretty self-explanatory but we’ll go through a narrative anyhow. The basic idea is that when a record is created, updated, or deleted in SQL server, an event will be



generated through the use of a trigger. As I mentioned earlier, triggers are synchronous and if we try to call a service outside of SQL server we’ll surely run into performance issues, and not to mention what would happen if the external service went down for maintenance or system failure. In more ways than one, that would have been a poor design decision. So here’s where the Service Broker will help us out and allow us to make an asynchronous calls outside of SQL server. The trigger will be programmed to use the SQL Server Service Broker whereby messages will be sent to a queue asynchronously within the same database. A CLR stored procedure will be activated (and pooled) when messages arrive on the queue and then here is where we’ll make our call to the external service.

The external service we’ll be calling is a SOAP-based web service that serves as a SOAP API to ActiveMQ. I understand there is a .Net Messaging API called ActiveMQ NMS which we could use inside SQL Server to connect directly to ActiveMQ, perhaps we’ll refactor our system to NMS instead. The SOAP-based web service will then connect ActiveMQ and put the event message on a queue. To the right of ActiveMQ we have a OSGI based runtime that will host Apache Camel routes. Apache Camel is an integration framework implementing most of the Enterprise Integration Patterns. We’ll be using the message dispatcher functionality to dispatch our event messages to other network based services.

In a nutshell, that’s the system we’ll be building; there’s a lot to be done so let’s get started!