I was able to attend the SQL in the City event in New York this past Friday. I highly recommend that anyone who can spare the time and travel should attend the next SQL in the City day in your area. I found it to be a very well put-together… Read more
I recently had the need to script the execution of a command-line utility and thought it would be a good learning experience to try doing it with PowerShell. The utility would sometimes output to the STDOUT, but would also send its output to STDERR if there was a problem. This… Read more
I was googling (with Bing AND Google) like crazy for a solution to this, but didn't find one from any of the open resource sites, so I figured I'd blog about my fix for this to help the next poor dev to come along.
I was getting user feedback that… Read more
One annoying thing about SSIS (2008) and some of the other project types in Visual Studio is the lack of an "Open Project Directory..." option on the project right-click menu. Visual Studio's "Tools" menu can provide a work-around this.
Go to Tools... External Tools... and add a new entry… Read more
Use of the PIVOT operator in T-SQL has always been tantalizing yet confounding to me. It offers the promise of more concise and expressive code compared to a subselect pattern, yet I've always found it difficult to get the code just right so that I could apply it in a… Read more
This presentation gives an excellent overview of the process of upgrading SQL Server 2008 SSIS packages to work with SQL Server 2012 SSIS. I’m very impressed at the much-improved capabilities for deploying SSIS packages in a less-scary way using package and project parameters, support for SSIS “environments” (“prod”, “dev”, “test”,… Read more
There was a great deal of feedback to my previous post One Way To Insert Many Rows Very Fast From Your .NET Application.
The post focused on the performance benefit of using a single SQL statement with an XML-based parameter to do inserts of many rows to a table… Read more
This is my attempt to explain something that I have wanted to figure out for a long time. I have read many articles on this very subject but they always seem to fall short for various reasons - generally by including way more detail or complexity than is necessary to… Read more
If you don’t change the version number of your .NET project output every time you create a setup package, you may have found that it’s difficult to have the Setup program reliably uninstall the existing version of your software before installing your update.
Here’s how to add a batch file… Read more
I recently discovered this great little free Visual Studio Add-in that allows pasting text as preformatted strings in VB or C#.
Meaning it will turn this text:
This is line 1.
This is line 2.
To this when pasted:
“This is line 1.” & vbCrLf & _
“This is line… Read more
I just ran into an issue with one of my applications where a SQL statement had updated a status code field to a lower-case status code such as “active”. We were expecting “Active” in the application and as a result our application began exhibiting odd behavior. There are a host… Read more
This will be a series of handy hints for people that know SQL and are frustrated by Excel. I am going to be writing this for Excel 2007 (what I currently have at work) but the tips should work for most recent versions. The SQL stuff should also work with… Read more
Full disclosure: There might be a better way to do this. I am not aware of it.
- Maintaining multiple DB environments (Local, Shared Dev, Staging, Prod) means needing to maintain multiple SSIS environments.
- SSIS packages are tricky to configure using the existing toolset – it’s easy to accidentally use…
One of the databases that I support reports all money values in both the local currency and US Dollars. Very often we will do all of our calculations in a table variable using the local currency and then calculate USD equivalents at the very end.
With this methodology, adding a… Read more
Tobias Ternstrom from Microsoft gave a great talk at Tech Ed regarding upcoming features of SQL Server Denali. His presentation was engaging, funny, and informative!
You can check it out on the Channel 9 site here (72 minutes):
I have to say that I am very excited about all… Read more
(Yet another boring org chart example – except this one has multiple roots)
If you want to test if you’re currently debugging an SSIS package via BIDS/Visual Studio, or running normally via DTExec, you can use the following utility functions inside a Script task.
Public Function SSIS_IsBIDS() As Boolean
Return (SSIS_Environment() = "DtsDebugHost")
Public Function SSIS_IsDTExec()… Read more
I was recently working on a data cleanup problem where I had to do lots of comparisons of one row to the next row and I was trying to do my best to avoid using cursor for this. I was using the old trick of having an IDENTITY() field and… Read more
I have two tables in an application that I support. One is called [Report], and the other is [ReportParameter].
DECLARE @Report TABLE (
ReportID INT PRIMARY KEY,
ReportName VARCHAR Read more