Speaking in Code

Wednesday, May 01, 2013

Get Chocolatey

Chocolatey.org....it's a revelation
Install great software silently with almost zero effort.

Do yourself a favor and get Chocolatey today!

Tuesday, February 19, 2013

Office 2010 requires MSXML version 6.10.1129

Windows XP with Service Pack 3 is identified by Microsoft as suitable for installing Office 2010. However during the installation routine you may get the following message:

The installation of Microsoft Office 2010 requires that MSXML version 6.10.1129.0 be installed on your computer. Install this component and re-run setup.

You may then discover that your operating system has a newer version of MSXML6, like 6.20. You will be unable to uninstall MSXML6 version 6.20 and if you attempt to re-install version 6.10.1129 you will get the following message:

Installation of MSXML 6.0 Parser failed because a higher version already exists on the machine. To proceed, uninstall the higher version and then run MSXML 6.0 Parser Setup again.

The advice I found in several places was to contact Microsoft Technical Support, have them remote into your PC and fix this problem. It took me half a day to connect with them but you can fix it yourself in about fifteen minutes. Here’s what they did for me:

1. Download Windows Installer Cleanup Utility from Softpedia (http://www.softpedia.com/get/Security/Secure-cleaning/Windows-Installer-CleanUp-Utility.shtml) This is a free utility so there are lots of “download” buttons for other software. Be certain you get the WICU utility.
Install the utility and launch it. It will show a list of software installed on your PC. Scroll down the list until you see the item named MSXML 6.0 Parser version 6.20.xxx. (See the screen capture below where I have MSXML 6.0 Parser version 6.10.1129 highlighted. My 6.20 has been removed and 6.10 is now in its place.) You need to highlight 6.20.xxx and click the [Remove] button.



2. Download XML6 for Office 2010. (http://www.microsoft.com/en-us/download/details.aspx?id=6276) This is called “Microsoft Core XML Services (MSXML) 6.0 Service Pack 1. Since I’m running a 32 bit processor I downloaded the file named msxml6_x86.msi and double-clicked it to install it.

3. Restart the computer.

4. Install Office 2010.

Now I have MSXML6 version 6.10 and have not yet found the application needing version 6.20. If I find it I will post an update to this article.

Labels: , ,

Friday, February 08, 2013

Apply SQL Agent notification to all jobs that lack it | SQL Tidbits

Based on the script I found here:
Create notifications for failed jobs with one easy script | SQL Tidbits:

I slightly revised to only script notification addition if it wasn't already present:

DECLARE @QuotedIdentifier CHAR(1);

SET @QuotedIdentifier = '' -- use '''' for single quote

DECLARE @ListDelimeter CHAR(1);

SET @ListDelimeter = ';'

DECLARE @CSVlist VARCHAR(max) --use varchar(8000) for SQL Server 2000

--no event log, email on failure
SELECT @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier + '
EXEC msdb.dbo.sp_update_job @job_id=N''' + convert(VARCHAR(max), [job_id]) + ''',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_email_operator_name=N''DbaOperator''' + @QuotedIdentifier
FROM msdb.dbo.sysjobs j
WHERE j.[enabled] = 1
AND j.[notify_level_email] <> 2

--print @csvlist
EXEC (@CSVlist)

Thursday, February 07, 2013

Move over GETDATE() Here Comes SYSDATETIME()

Since SQL Server 2008 and the introduction of the DateTime2 data type, neither GETDATE() nor Current_Timestamp() are the best functions to return the current date and time. Instead, SysDateTime() is preferable.
Read more here if you like.

Friday, January 11, 2013

sp_BLITZ – SQL Server Takeover Script | Brent Ozar Unlimited

Free script for finding problems in a SQL Server. Cool stuff!
sp_BLITZ – SQL Server Takeover Script | Brent Ozar Unlimited:

'via Blog this'

Thursday, January 10, 2013

SQL# (SQLsharp) - Expanding the capabilities of T-SQL via SQL CLR

I'm volunteering to shill for SQL# because it allows me to put RegEx capability in SQL Server with practically zero effort.
I did have to follow a couple suggestions with the comments of the deployment script. No biggie. Loads of free capabilities. Thanks to Solomon Rutzky!

SQL# (SQLsharp) - Expanding the capabilities of T-SQL via SQL CLR:

'via Blog this'

Saturday, October 20, 2012

Size of Tables in SQL Server

In order to find the size of all user tables in the database I usually search for an example of the sp_spaceused and sp_foreachtable procedures. I had a really good one but can't find it now. So today I found this one which works quite well.

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY t.Name, p.Rows
ORDER BY TotalSpaceKB DESC