Protean

From time to time these days I have to put my DBA hat back on and review database schema designed by those whose focus is on application development. What I often find is that attempts to improve performance through indexing have been done in vain as the query optimizer either doesn’t use them enough to merit their creation or they have been created on the wrong columns.

Frequently, optimisation is referred to as a dark art, but here I am going to attempt to give some basic pointers to help enighten the novice database designer…

Read the rest of this entry »

In my previous post on the pivot component, we created a report from a partially populated translation table.

In this post I’m going to show how we re-import our completed report back to our SQL Server table using the SSIS Unpivot component.

Read the rest of this entry »

The SQL Command OLE DB component doesn’t like inline comments when using the Native SQL Server OLEDBConnection. Leave them in your SQL Code and you get the following error:-

An OLE DB error has occurred. Error code: 0×80004005.  An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0×80004005  Description: “Syntax error, permission violation, or other nonspecific error”.

Take them out and hey presto…

It may be me, but as yet I haven’t seen too much written about the SSIS pivot and unpivot components.

Whilst they are not the easiest to configure, when faced with the option of importing to SQL Server and using a UNION or the new PIVOT clauses, I actually prefer the SSIS component.

Rather than actually explain the component, I thought it might be useful to go through a real example, so here goes…

Read the rest of this entry »

In my never ending quest to simplify administration tasks on SQL Server, I recently came up with a method to automate the creation of new publications with a one script fits all piece of SQL Code.

Considering that in most enterprises, the default configuration options for replication will remain the same, the only thing that changes are the destination servers and the publications/articles themselves.

Read the rest of this entry »

More Excel woes with SSIS…

Be very careful when using Excel as your transformation destination. Given that it is hard to maintain a consistent controlled version of any excel file you are using, it can be very prone to fail on account of the encoding that is used within it.

Read the rest of this entry »

Remember when you use a Stored Procedure as the OLE DB Source that you need to turn off the ‘x many rows affected’ statement at the end.

Do this by including in the CREATE PROCEDURE script the statement:

SET NOCOUNT ON

Also, ensure that you make the full call to the procedure:

EXEC spStoredProcedure @myString = ?, @myInteger = ?

This makes parameter mapping much easier, however, don’t expect to be able to preview the data… this only seems to work at run-time.

We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.

Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.

The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.

There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…

Read the rest of this entry »

Ever had a multitude of SQL Agent jobs with dependencies on one another in order for your nightly batch to succeed? Ever had a situation where you forgot to set the steps correctly within the job resulting in the batch failing or not even running at all?

Me too… here’s what I did in order to protect myself from human error.

Read the rest of this entry »

I have just finished the code for a new transactional replication setup.

Requirements were to be able to filter rows according to the destinations that formed part of the fullfilment of the order. This could theoretically include up to 10 destinations however in reality it tended to be 2 or 3. The data would be real-time messages flowing out of the mainframe into the SQL Server via BizTalk.

Read the rest of this entry »