May 282010
 

For those of you that wished this site was accessible on mobile clients, this request is now complete. The site will detect that you are browing with a mobile client and present you automatically with a format suited for your device. Please let me know if you run into issues.

Also, when viewing the mobile version, at the bottom of the page is an option to switch back to the full site (see the switch titled “Mobile Theme”). Enjoy.

Switch Moble Version Image

May 282010
 

Should you come across this error message, “cannot show visual studio 2008 tools for applications”, you may have to clean up your VSTA environment.

You may also have the following additional error message:
The System Cannot Find The File Specified

Here’s how to fix it, thanks to Silviu Guea, Microsoft.

Create a batch file with the following code. Execute it. Ignore any errors. Try again. If it doesn’t work, you may have to contact Microsoft support or try many uninstall/reinstall combinations. For me, this worked the first time I tried it. Finally, the code:


@rem start batch file
@rem delete the VSTA registry cache from the current user
REG DELETE HKCU\Software\Microsoft\VSTA /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

@rem delete the VSTA registration cache for SSIS script task and data flow script component
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

@rem delete the cached files from the HDD
rd /s /q "%AppData%\Microsoft\VSTA"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptTask"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent"

rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTA"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptComponent"

@rem re-generate the VSTA files for SSIS script task and component
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptTask /setup
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptComponent /setup

@rem end batch file

Download file here: vsta_cleanup.txt

May 242010
 

For those of you that work extensively with sys.dm_exec_query_stats and associated plan cache DMVs, be aware that altering the server’s ‘max degree of parallelism’ setting will flush the query plan cache.

You may see this message in your server’s SQL Log file:
“SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.”

In the case of MAXDOP changing, if you look before the above event happened, you should see that MAXDOP was changed and hence caused the flush.

This makes sense. If you are changing the MAXDOP setting, all current query plans are worthless as they’re out of date with respect to this parameter. Some plans may now be able to benefit from parallelism, whereas before they could not. Or if constraining MAXDOP, some parallel plans may not be able to work anymore. (Or some combination thereof.) The easiest way to rectify this issue is to simply flush the plan cache and let all plans recompile at their next execution.

A common situation for changing MAXDOP could be for maintenance operations, where during normal operations MAXDOP is constrained to 1, yet while doing weekly work during a maintenance window the DBAs may elect to set MAXDOP to 0 (unrestricted).

So, when you’re scratching your head wondering where your plan cache went, it could be due to administrative tasks.

May 042010
 

It is quite easy in SQL Server 2005 to accidentally (or intentionally, I suppose) create data corruption. It’s best to just show via a demo, so here you go:

(Ref: http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx. Thanks, Paul White, for doing all of the work for this post. ;) )


-- In SQL Server 2005 only

-- fails
select round(0.5,0) 

-- 0.5 is cast as a numeric(1,1), which is used as the output type as well.
-- Since 0.5 rounds up to 1.0, this value is larger than a numeric(1,1) can store, 
-- which is 0.9

-- works!
select round(0.5,0) as rnd 
  into testround

-- view metadata of 'testround'  
sp_help testround  

-- Observe that the 'rnd' column is defined as a numeric(1,1), 
-- which technically means 0.9 is the max value

-- Now let's view the record:
select *
  from testround
  
-- WHAT?!  "Arithmetic Overflow"?  But, but, but the insert worked

-- Hmm, does this work? (Yes)
select cast(rnd as numeric(2,1)) as rnd
  from testround

-- Well, let's see what's going on here:

DBCC CHECKTABLE('testround') -- also catchable via DBCC CHECKDB, but not 
                             -- with PHYSICAL_ONLY

/*
DBCC results for 'testround'.
Msg 2570, Level 16, State 3, Line 1
Page (1:1811349), slot 0 in object ID 2099048, index ID 0, 
partition ID 72057594063028224, alloc unit ID 72057594096123904 (type 
"In-row data"). Column "rnd" value is out of range for data type "numeric".  
Update column to a legal value.
There are 1 rows in 2 pages for object "testround".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 
'testround' (object ID 2099048).
DBCC execution completed. If DBCC printed error messages, contact your 
system administrator.
*/

-- Okay, now let's see what's *REALLY* going on
DBCC TRACEON(3604)
GO

declare @db int
declare @filenumber int
declare @pagenumber int
set @db = db_id()
set @filenumber = 1       -- From DBCC output, adjust accordingly: Page (1:1811349)
set @pagenumber = 1811349 -- From DBCC output, adjust accordingly: Page (1:1811349)

DBCC PAGE(@db, @filenumber, @pagenumber, 3); -- 3 == full detail with ascii output 

-- Find the entry for slot 0 (according to the DBCC output above), note the out of range 
-- value: "rnd = 1.0"

/*
Slot 0 Offset 0x60 Length 12

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x67EAC060

00000000:   10000900 010a0000 000100fe ††††††††††............             

Slot 0 Column 0 Offset 0x4 Length 5

rnd = 1.0       
*/

-- Let's fix it

update testround
   set rnd = 0.9;
GO

-- Run CHECKTABLE again   
DBCC CHECKTABLE('testround') -- success!  No issues.

-- Okay, example's over.  Turn off our TRACEON command and drop the table.

-- It is important to note that this does not work in 2008.  Whew.

DBCC TRACEOFF(3604)
GO

drop table testround;
GO