SQL Developer Ramblings

Download Source Code: Here In the previous post Generate SSRS Reports in SSIS – The Report – 1 of 4 we developed an SSRS report that we will now call from within our SSIS package.  We will also continue to use the same solution files as we did in the first post.

Download Source Files: Here One of the frustrating things about using the SQL Server PIVOT function is that you need to explicitly name the field values that you want to be pivoted across the horizontal access of your result set.  The problem with this is that this list of values could change over time as new data is added to the database.  So what's the solution?  In this post I’ll demonstrate how you can create a stored proc that will take a SQL query, derive the unique values in a column and dynamically create the SQL necessary to generate PIVOT table based on these values.

Download Source Files: Here In this post I will illustrate a high performance method for calculating running total by traversing a table index. I’ve seen many example of people using sub queries and CROSS JOIN’s in order to accomplish the same thing, the only problem with these is the overhead of joining so many rows together.  In this example I’ll illustrate how to traverse an index and create a running total at the same time.

Download Source Files: Here In this post I will illustrate how you can multiply rows in a table together without using a cursor.

Download Source Files: Here This is the first of four posts looking at how to generate and save SSRS reports from within SSIS.  In this post I will look at creating the stored procedure to retrieve the report information, and creating and deploying the sample report to the report server. The Second post will look at a method of calling to the report server and generating reports in the control flow of SSIS.  We’ll look at how to connect to the web service, pass parameters and save a report to disk. The third post in this series will show how to do the same thing in Data Flow, but instead of writing the reports out to disk I’m going to save the reports in a binary format straight to the database.  The fourth post in this series will show you how to use SSIS to retrieve the binary files saved in SQL server and render them back to disk.  I’ll also illustrate in this example how you can attach the files to an email and send them. I hope that at the end of thi...

Download Source Files: Here In the first of what hope to be many posts. I’ve taken my first step into really utilising some of the many enhancements delivered in the most recent release of Microsoft SQL Server. Two of the most eagerly awaited enhancements to come out from Microsoft have been the advent of table data types and the new MERGE syntax. Table data types now allow SQL developers to pass table variables directly into stored procedures as a parameter, in the past ingenious way had been devised to do this with the use of temp tables or with XML, but table data types now make these process redundant and offer great performance improvements. For the data warehouse developers out there the MERGE statement now allows INSERT, UPDATE and DELETE statements to be applied to a table in a single pass, simplify the SCD (Slowly Changing Dimension) process.