In my most recent adventure, I was tasked with creating a real-time push subscription to our companies distributed publication server. This effort kicked off a lot of ideas, foremost being the “Asynchronous trigger” promoted by the service broker. I used the example found in the back of the book Pro SQL Server 2008 Service Broker. While this asynchronous trigger is asynchronous in respect to “writing to an internal SQL Server object” (i.e. a table) it still takes a bit of time to execute (and thus return the initial transaction). I was disappointed to now associate the word asynchronous to “timely”. Service broker has (and will) bring about many advances in the near-term future, however the speed of submission and true asynchrony will hopefully be revisited sometime soon. The second option for performing the real-time push is to use a synchronous trigger, or an “after update” trigger. The after update trigger would push the record to a common table which would then be polled by a job. Initially I thought there was a way to insert to this common table then execute another trigger on this table to publish the record. However I soon learned that all triggers that call triggers contribute to the same transaction. When one trigger inserts into a table and that table also contains a trigger, this is a “nested trigger”. The reason that nested triggers are a concern is because the first call that performs the initial insert does not return until the last trigger in […]
Continue reading ...
A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass or fail. Determining the object to report on The first step is to understand the main object of data you are monitoring. This could range from monitoring customers, loans, employees.. Basically any object that has data surrounding it can be monitored. This object will then be the focus of the rules engine report. Sometimes the data being monitored may be composed of more than one object. You may want to monitor the data surrounding customers at certain store locations. In which case, the granularity of your application will be customer-location combinations. In our example we will monitor customers for our retail store AlphaBeta. The rule data we want to collect falls within 3 categories: Failure to Pay Significant Purchases Returns These 3 categories are parent categories for a series of “Rules”. These rules are actually stored procedures that check for specific conditions surrounding our customers. Here are the rules in each category: Failure to pay: Rule 1 – Has failed credit card authorizations within 30 days Rule 2 – Insufficient funds outstanding Significant purchases: Rule 1 – Order amount over $500 within 30 days Rule 2 – Alcohol purchase over $200 […]
Continue reading ...
Change Data Capture, CDC is a feature that is revolutionizing SQL Server in respects to data flow. Previous to change data capture, triggers or high water marks needed to be used in order to perform delta transfers of data to data warehouses or disparate systems. This example shows the use of a custom CDC solution that utilizes a definition table which stored table and column names in order to create a meta-data driven CDC subscription. First create your CDC subscription table [cc lang=”sql”] CREATE TABLE [dbo].[Columns_To_Monitor_Sto]( [ColumnID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [varchar](40) NULL, [SchemaName] [varchar](20) NULL, [TableName] [varchar](255) NULL, [ColumnName] [varchar](255) NULL, [Ordinal] [int] NULL, [IsActive] [tinyint] NULL, ) [/cc] With the table now created, we have a meta data repository of columns. Anything added or removed from this table will automatically get added or removed from the CDC subscription. [cc lang=”sql”] DECLARE @DebugMode int = 1 DECLARE @source_schema varchar(20) DECLARE @source_name varchar(255) DECLARE @role_name varchar(20) = ‘cdc_manager’ DECLARE @supports_net_changes int = 1 DECLARE @captured_column_list varchar(MAX) = ” DECLARE @TableCount int DECLARE @iOrigCount int DECLARE @capture_instance nvarchar(MAX) DECLARE @TableName varchar(255) DECLARE @IsCDCEnabled int CREATE TABLE #TablesToMonitor ( ID int IDENTITY(1,1) ,SchemaName varchar(20) ,TableName varchar(255) ,ColumnsToMonitor varchar(MAX) ,CaptureInstance varchar(MAX) ,IsCDCEnabled bit ) INSERT INTO #TablesToMonitor ( TableName ,SchemaName ,ColumnsToMonitor ,CaptureInstance ,IsCDCEnabled ) SELECT DISTINCT rcm.TableName ,rcm.SchemaName ,ColumnsToMonitor = SUBSTRING((SELECT ‘,’ + r.ColumnName FROM( SELECT DISTINCT TableName,ColumnName FROM dbo.Columns_To_Monitor_Sto WHERE IsActive = 1 ) r WHERE (r.TableName = rcm.TableName) FOR XML PATH(”) ), 2, 8000) ,CaptureInstance = rcm.SchemaName + ‘_’ + […]
Continue reading ...
Endpoints are essentially web services that expose database access over HTTP. Aside from architectural design decisions, these are useful is that your application development team does not have to have management studio access to SQL Server in order to begin development. The WSDL generated shows the parameters required for the endpoint. There are a few security issues to keep in mind when exposing this functionality. First, if you are not connecting over a secure channel (SSL) then you need to use Integrated or NTLM authentication. Meaning your web application will have to impersonate a user or use the logged in user’s context in order to access the service. (Yes, that does mean enabling impersonation). If you are connecting over a secure channel, then you can use SQL Authentication (mixed mode). The second security consideration is to disable BATCHES. Batches basically allows pass-through SQL calls. Before you can create an endpoint, you have to reserve an HTTP namespace (URL). This will ensure that you do not overwrite a URL already created in IIS or vice-versa. Create the namespace using the following command: [cc lang=”sql”] EXEC sp_reserve_http_namespace N’http://YourServerName:80/WebServices/’ [/cc] Now you can create the endpoint. [cc lang=”sql”] IF EXISTS ( SELECT name from sys.http_endpoints WHERE name = ‘MyEndpoint’ ) BEGIN DROP ENDPOINT MyEndpoint END GO CREATE ENDPOINT MyEndpoint AUTHORIZATION [domainyouruser] — with optional authorization for owner STATE = STARTED — the state of the endpoint — http or tcp AS HTTP ( path=’/WebServices/’, — the virtual path AUTHENTICATION=(INTEGRATED), — type of authentication […]
Continue reading ...
This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) select * FROM SQLPaging where ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc]
Continue reading ...
