In a previous post (Easing the Testing Stored Procedures with Temporary Tables) I introduced the sp_describe_first_result_set stored procedure. This procedure works like sp_executesql, except that it returns the columns that a query returns rather than the results of the query. But it has a problem. The column name is separated from the datatype and thus […]
Using a temporary table to handle sp_describe_first_result_set Read More »
Recently someone posed a puzzling question on the MSDN forums (How to reorder a column in a table which also has a foreign key). They needed to be able to add columns to a table in a specific order. If you know basics of relational theory, you would understand that the order of columns in
Using view as source for application Read More »
One of the query pains in SQL Server involves a simple split of a string. You want to pass into a stored procedure a comma delimited list of items and use that in your procedure. In order to get a tabular result from that string, you had to write your own function. But no longer.
Splitting strings in SQL Server Read More »
I think that we would all agree that we should test our code before we release it. Sometimes it very easy to do so. For example, testing a view is not difficult. Say you had to make a small change to the view. You create a new view that returns the same results that the
Easing the Testing Stored Procedures with Temporary Tables Read More »
One of the things that I do frequently is to put together some message that involves a template filled in with variables. For example, I want my error message to fit a certain pattern. Or I want to put a meaningful log message text. In SQL Server, I repeat the following ugliness in my code:
Displaying parameterized string in SQL Server Read More »
I am working on a small project for a small non-profit company. It has been an interesting project that could involve a variety of interesting technologies. But if it is going to be useful, that company needs to be able to support the solution going forward. What are the factors that might make the project
Solution appropriate to user Read More »
One problem that you might encounter with data is where you are gathering information from multiple lines and want to output only one line. If you are going to simply aggregate the results, then you can use the Aggregate transformation. But what do you do if you want to create simply append one row’s value
SSIS Script Component – Last Row Read More »
When you pivot data, there are three dimensions that you need to set: the Set Keys, the Pivot Keys and the Pivot Values (to use the SSIS terms). In Excel it is very easy to select multiple Set Keys (ROWS), multiple Pivot Keys (COLUMNS) and even multiple Pivot Values (Values). However, using the SSIS Pivot
SSIS Pivot Transformation with Multiple Set Keys Read More »
One of the problems I have encountered is trying to unpivot a file that has a variable number of columns. There are a set of columns that are fixed, that represent the Row keys. And then there are variable numbers of columns. This is especially true when your columns are based on dates. This sample
SSIS Flexible UnPivot Read More »
Sometime your data source has duplicates. SSIS has an out of the box tool to remove these duplicates. The Sort transformation has a “Remove rows with duplicate sort values” property (https://www.mssqltips.com/sqlservertip/3036/removing-duplicates-rows-with-ssis-sort-transformation/). But it has a problem. It is very random as to the values that it removes. How do you specify which row to remove?
SSIS Removing Duplicate Rows Using Conditions Read More »
You must be logged in to post a comment.