Month: March 2014

Quick script that will connect to a server and retrieve all the ssis packages from the msdb database and create a report of all the connections in said packages. I shamelessly stole the xslt (and modified a bit) from here. This will copy all the ssis packages on the server to your user profile directory under the ServerName directory you pass in. Not thoroughly tested at all, use at your own risk.

This script just executes a query on a per-database level to find objects via their definitions in the sys.all_sql_modules table that reference a linked server defined on the current server. Right now it just spits the results out to a gridview. Minimally tested, use at your own risk.

Whilst moving a database from a dying Sql Server 2000 box to a newer Sql Server 2008R2 server I ran a checkdb to ensure everything was kosher with the database (of course, after running DBCC UPDATEUSAGE). Lo and behold, everything was not okay:

What does this mean exactly? Well, it’s saying that (in my case), on page 131693 of file 1 a datetime value is not a valid value for the datetime type. What is the invalid value? Unfortunately, every time you try to do a select to see the value, you’ll get this same error. So, we’ll have to run DBCC PAGE() in order to view the page data. Start by turning on trace flag 3604 in order to print the results to the screen.

DBCC TRACEON (3604)

Next, we’ll need to pass in both the file number and the page number to DBCC PAGE in order to see the contents. This is contained within the original error message (screenshot below). DBCC PAGE also takes a 3rd parameter for its print options, which is a number between 0 and 3. Rather than describe them here, here is a link to the description. In my scenario, I’ll be using print option 3, which is to show the page header plus the detailed per-row information. I need this in order to be able to view the invalid data row.

DBCC PAGE ('DatabaseName', 1, 131693 , 3)

The DBCC PAGE command will spit out the page information in text format. I usually copy this out and paste it into Notepad++ (or whatever text editor you’re comfortable with) in order to make searching easier. Here it is in the SSMS results pain:

So, now we’ve got a dump of the data, let’s look for the invalid row. In notepad++, do a find on the column name that has the invalid data. For example, in my case we’ll search on the CreateDate and look at the column values until we see something out of sorts:

Found it! So, now I can scroll up to find the unique primary key value so we can update the column to something not invalid:

Just to be sure, check to make sure that this record does indeed contain the invalid column:

Yup, sure does. Now, as to what to the invalid date value for the column should be, who knows? In my case, the dates of the other records around this invalid column were all only differing by milliseconds, and since we can assume that the CreateDate is just that, I doubt a difference of a few milliseconds is going to make a huge difference. Make sure to check with whoever owns the data to make sure whatever value you need to replace is an acceptable value.

In my case, I just grabbed the next records’ CreateDate value and updated the invalid record to its value (the owners of the data had no clue what to set it to):

Okay, all fixed. So, run DBCC CheckTable one more time to make sure there are no more errors:

Fixed. Luckily, in my case it was just the one row. Imagine having to do this to 1000 rows? This is why you should be running your checks nightly if possible.