Sometimes one might like to identify a particular SQL Server resource using the Page ID this resource is under, you might notice a Page ID sometimes on the Activity Monitor page under the Wait Resource column, if a certain query is waiting on a particular resource, some DMVs will reference Page IDs as well as the SQL Server log files, I find it handy to be able to see exactly what SQL Server Object falls under this Page ID, in order to diagnose any issues with access to that particular resource.

Read more →

Without a doubt one of the most annoying and least useful errors I have ever encountered during my experience with SSIS.

This error has crept up on an SSIS packages that processes a dimension. The SSIS package is very simple and all it has is a OLE DB Source that dumps data from a query into a Dimension Processing task. The package executes fine until it reaches a particular row in the OLE DB source batch, in which it will fail with the following error message:

Dimension Processing Error: XML parsing failed at line 4, column 3391: Illegal xml character.

Descriptive!

Read more →

There are many ways to obtain the execution plan of a SQL Server query, each of which serves a particular purpose, for example if you can run a query easily, then the best way to get an execution plan is to simply enable the execution plan (either through SSMS or SETting the correct session option), but if the query takes a long time to run, or you simply cannot run the query any other reason, then you might decide to profile the query when it is naturally running (perhaps on the live environment) through SQL Profiler.

In this post I will go through a simple SQL query to extract the execution plan from SQL’s own plan cache. There are many reasons why this might not be an accurate way to extract a query, but it is certainly helpful in situation were you have good understanding of your DB setup.

Read more →

It has been a while since I released a post, this has mostly been due to a recent involvement in some very time consuming scale-out architecture, there is nothing like building a whole SQL Server architecture from scratch, something very powerful and enterprise-y.

Anyhow the purpose of this post is to discuss one of the many issuesam finding with the new SSIS 2012. This error relates to error logging when having a Script Component task within a Data Flow task that is being used as a data “Source”, particularly when this Script Component is followed by a Lookup task, or any task that works on results In-Stream, as shown in the image above.

Read more →

Resolving the following SQL Server Replication error is easy enough, and is general linked to access to replication folders on the Distributor file-system, from the Subscriber Agent account.

The full error message will look something like this:

The process could not read file ‘‘ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)

Read more →

Sometimes when trying to initialize a subscription on a SQL Server database that is involved in a replication topology, you could run into the following error message:

Cannot reinitialize subscriptions of non-immediate_sync publications
Change database context to ‘[db-name]‘.
Error: 20159

A pretty nasty message that means very little.

Read more →

While setting up SQL Server Replication (Transactional Replication to be specific), I had successfully created the Publication and the Subscriptions, but nothing was being replicated between the publisher SQL Server instance and the subscriber SQL Server instance.

When viewing the subscription details through the Replication Monitor tool, the subscription was not throwing an error, and hanging with the following message:

The initial snapshot for publication is not yet available.

In the post I will explain why this is appearing, and how to resolve the issue and generate the required Replication Snapshot.

Read more →

Microsoft has been trying to leverage its Azure (SQL Cloud) platform for a while now through enhanced availability and performance, as well as creating a few “unexpected” connectors, such as the Hadoop to SQL DB Connector, which is now available as a beta in an Azure environment.

Read more →

While trying to migrate my old SQL Server Integration Services (SSIS) packages from the legacy Package Deployment Model project to a Project Deployment Model one, I ran into the following error on some (not all) SSIS package after selecting it for import:

Object name ‘[object-name]‘ is not valid. Name cannot end with a whitespace.

This is a typical frustrating SSIS error message, uninformative and goes nowhere. Hopefully the tip here should help you get around the error in this particular situation.

Read more →

With SQL Server 2012, Microsoft has released a major and much needed update to its SQL Server Integration Services, including features that enable much better manageability features, performance tracking (and SSIS performance tuning) features, as well as better (closer) integration with SQL Server Database Engine.

In this post I introduce the new features of Integration Services that are enabled through the new Project Deployment Model, as well as highlight some of the short-comings of this new technology, particularly with regard to Scale-Out and Distributed Processing.

Read more →