First, thanks to Hipster Ipsum for providing really fun random string data! I created a proc this week that was a simple SELECT ALL from a lookup and the output was going to be used in a drop down menu in a Reporting Services report. The data wasn’t the prettiest and I wanted to capitalize [...]
One area I’ve always felt MSSQL was lacking is handling dates. This week I needed to declare a date variable and select the beginning of the previous hour. I cobbled together a solution that I thought would be useful to add to the blogosphere. Below is a screenshot and the code you can copy and [...]
Neat trick that Lee Ervin showed me:
I recently inherited a SSIS package that moves a daily snapshot of a log table on a MySQL database over to a MSSQL database. The existing tasks truncate the destination tables and perform a fresh insert every night which works for now since the tables are relatively small. A log table, on the other hand, [...]
There are oodles of solutions to this problem out there, but this is one I hacked together when I needed a quick solution. Say you have a varchar parameter containing a comma separated list of integers e.g. ’101,102, … ‘ that you need inserted to a table. This script will do that pretty quickly, with [...]
Short little script that allows you to select Odd or Even rows from a table with an identity column: IF OBJECT_ID(‘tempdb.dbo.#TestData’,'U’) IS NOT NULL DROP TABLE #TestData; CREATE TABLE #TestData( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, Glaven VARCHAR(100)) INSERT INTO #TestData(Glaven) SELECT NEWID() UNION SELECT NEWID() UNION SELECT NEWID() UNION SELECT NEWID() UNION SELECT NEWID() [...]
When I’m running while loops I like to see what’s going on. The benefits to this are obvious. Here is a little script I like to use to see the number of items finishedout of the whole, the latest timestamp, and the currently processing item. First, let’s insert 10,000 rows to a temporary table. I’m [...]
Before I was a DBA my job was pulling email campaigns for Marketing. Back then I didn’t have DBA permissions or experience with SSIS, so I couldn’t have deployed a package even if I knew how to make one. Instead, I got around that limitation by moving data via BCP. Some claim that BCP is [...]
While coding a recent report (the same one mentioned in my post on Querying AD with Linked Servers) I needed to shred a column with XML values. The challenge was twofold: the data was stored as VARCHAR(MAX), not XML, and contained malformed XML insofar as there were different root elements and different nodes inside the roots. [...]
Recently I built a report that involved inserting Active Directory information to a temp table from within a stored procedure. Creating a linked server, testing it, and tracking down the information I needed took some time; this post documents the steps I took and the issues I encountered. All code is executed on SQL Server [...]