John Howell

Found an interesting post about the SQL ISNULL function.  Apparently, it behaves differently if you’re using it to test strings.  For example:

DECLARE @a VARCHAR(100)
DECLARE @b VARCHAR(10)

SET @a=’This is much longer than 10 characters‘
SET @b=NULL

— This will display
—        This is mu
SELECT ISNULL(@b,@a)

— This will display
—        This is much longer than 10 characters
SELECT ISNULL(CONVERT(NVARCHAR(MAX), @b), @a)

Knowing this could keep you from pulling your hair out trying to investigate a bug when the truncated data gets down the pipeline.  Hope that helps someone out there.

Happy Coding!

I haven’t been as active on my Blog but I have been active on Twitter.  I’ll work on the blog but in the meantime you can follow me at #JohnMarkHowell on Twitter!

Everyone likes free books! Thanks to Chris for this one.

Powershell is one of those tools that people know is useful yet they are hesitant to pick it up and try it out.  Well, now you have no more excuses.  Dr. Tobias Weltner has provided a free ebook and blog to get you up to speed.  If you’ve wanted to start learning how powershell can help you, you really should check it out.  Where else can you get free ‘training’ like this?

Happy Coding!

When moving databases around, often people will truncate the logs and/or run DBCC SHRINKDATABASE but those only reduce the actual used space. They don’t actually shrink the database files themselves. DBCC SHRINKFILE is a bit picky in that in you pass it the logical file names. If you have the normal setup where you create a database with MDF, LDF and NDF, then it can get cumbersome to look up those names. What I use is the following piece of SQL which will shrink all the files for the current database. Note that SHRINKFILE only works on the current database.

USE [YourDatabaseGoesHere]
go
IF DATABASEPROPERTYEX(N'{0}', N'Status') IS NOT NULL BEGIN DECLARE @fileName SYSNAME 
    DECLARE @command NVARCHAR(1024) 
    DECLARE curFiles CURSOR 
        FOR SELECT [name] FROM sys.database_files 
    OPEN curFiles 
    FETCH NEXT FROM curFiles INTO @fileName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @command = 'DBCC SHRINKFILE(' + @fileName + ', 0, TRUNCATEONLY);'
        PRINT @command
        EXEC(@command)
        FETCH NEXT FROM curFiles INTO @fileName
    END
END;

Happy Coding!

The WEvtUtil utility is something I wrote about last year and up until recently I’ve just been using the qe command and piping the output.  However, I decided to use the epl (export-log) command to pull down the event log from a remote production server and discovered a significant gotcha.  I spun a quick console app to pull the logs down and was running into a problem where the command would execute but I was not seeing the file.   So I decided to try giving a different path, then it failed saying access was denied.  What I discovered was that it was in fact working but the output file was being created ON THE REMOTE SERVER!!  And no, I do not have permissions to access any of the drives on the remote computer but WEvtUtil was still able to create it!  I then created a local open share and WEvtUtil could not dump to that file.  It seems it can only dump locally to whatever server you’re hitting.  This utility has been out for a long time so there are bound to be a few shortcomings but this one is just outright dangerous.  Be safe and careful with it.

Happy (and safe) coding!

Just a reminder that you’ll want to install and check out the update to the power tools!  Good stuff!

Happy Coding!

After spending several days in balmy (read – HOT & HUMID!) Alabama seeing my wonderful kids, I’m back and there’s more good stuff to talk about.  The first is VS LightSwitch 2011 is out!  LightSwitch lets you build nice looking business apps quickly while still supporting extensions and third party plug-ins.  And the VS 2010 SP1 for Web Developers added Razor syntax support and more!  And (hold on to your hats) Denali Express CTP3 is now available!  So many great new tools to play with!  The biggest problem is finding which one to start with!

Happy Coding!

This post isn’t about a technology but I read J. D. Meier’s post about Why Teams Fail and just had to share it.  We all have been there, in those dysfunctional projects.  J. D.’s post is a succinct list and a short read and I feel should be posted on every door, elevator and window.   We all have to watch out for the ‘anti-patterns’ as he refers to them and when we see them, we need to stop them in their tracks!

Happy Coding!

Most developers write code to go against databases and often when doing performance testing and analysis, you can find yourself with a error something like “The transaction log for database ‘blahblah’ is full.”.   And many times this can come at the worst times (for example at the 23rd hour of a 24 hour test run).  Sometime back I found a handy script that I use as a scheduled job in these situations that will clear out the transaction log.  I forget where I saw this so I apologize and take no credit for this one, but it does work so thank you anonymous author. 

Happy Coding

SET NOCOUNT ON 
CREATE TABLE #TransactionLogFiles (
	DBName VARCHAR(150), 
	LogFileName VARCHAR(150) 
)
DECLARE DBList CURSOR 
	FOR SELECT name FROM master..sysdatabases WHERE 
	NAME NOT IN ('master','tempdb','model','msdb','distribution')
DECLARE @DB VARCHAR(100)
DECLARE @SQL VARCHAR(8000) 
OPEN DBList
FETCH NEXT FROM DBList INTO @DB
WHILE @@FETCH_STATUS <> -1 
BEGIN
	SET @SQL = 'USE ' + @DB 
		+ ' INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''
		+ @DB + ''', Name FROM sysfiles WHERE FileID=2'
	EXEC(@SQL) 
	FETCH NEXT FROM DBList INTO @DB 
END

DEALLOCATE DBList 
DECLARE TranLogList CURSOR 
	FOR SELECT DBName, LogFileName FROM #TransactionLogFiles
DECLARE @LogFile VARCHAR(100) 

OPEN TranLogList 
FETCH NEXT FROM TranLogList INTO @DB, @LogFile
WHILE @@FETCH_STATUS <> -1 
BEGIN 
	-- If you want to watch what it's processing.
	--PRINT @DB +',' + @LogFile
	SELECT @SQL = 'EXEC sp_dbOption ' + @DB 
		+ ', ''trunc. log on chkpt.'', ''True'''
	EXEC (@SQL)
	SELECT @SQL = 'USE ' + @DB + ' DBCC SHRINKFILE(''' 
		+ @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'
	EXEC (@SQL) 
	SELECT @SQL = 'EXEC sp_dbOption ' + @DB 
		+ ', ''trunc. log on chkpt.'', ''False'''
	EXEC(@SQL)
	FETCH NEXT FROM TranLogList INTO @DB, @LogFile 
END

DEALLOCATE TranLogList
DROP TABLE #TransactionLogFiles