Thursday, August 28, 2008

SQL Server Agent musing - T-SQL code in the job steps

It turns out that the SQL Server Agent does not take part in sysdepends. This allows the SQL Server to warn if an object is misspelled: i.e.


--create a procedure that calls an object that doesn't exist
CREATE PROCEDURE tmpBlah AS
--execute a procedure that doesn't exist
EXEC dbo.blah2
GO
--clean up the procedure
DROP PROCEDURE tmpBlah



Will return:



Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.blah2'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.



Creating a SQL Server Agent job step and setting the command text to:



EXEC dbo.blah2



No warning. When the job runs, it will fail on that step withOne has to wait for the job history in order to see the error:



Could not find stored procedure 'dbo.blah2'



This reinforces a standard I've used of storing most T-SQL code that would otherwise be scheduled in a stored procedure within the target database.

In a recent project I'm working, a large amount of the data caching and aggregation code was stored in the SQL Server Agent job steps. I've established a development standard of storing most code inside the target database, and limited Agent job steps to:


  • SQL Server Integration Services Package

  • Stored procedure – ideally within the target database

  • Limited small T-SQL commands such as truncating tables, creating cached copies (as soon as the commands are more than a few lines, or are very domain-specific, they will be moved to a stored procedure)

  • Maintenance tasks (such as DBCC DBREINDEX(@TableName,' ',90)

Wednesday, August 27, 2008

Virtual PC Resources

Resource for getting started with Virtual PC

Because I just introduced Virtual PC to a few new colleagues at work, I've collected here various resources used in the last year or two in my use of Virtual PC, from emails, favorites, etc.


Microsoft Virtual PC 2007 Technical Reference

A good starting point for setting up your first Virtual PC, using a established, supported OS such as Windows 98, 2000 or XP


TechNet Webcast: Microsoft Virtual PC Technical Overview - Level 200

I’ve previously used this webcast to introduce Virtual PC to teams new to the product, It’s a 2004 version, but other than slight differences in look-and-feel in 2007, as an end-user, the features hold true.


Virtual PC Guy

Good blog about issues with Virtual PC, setting up uncommon scenarios, alternative OS's, etc.


Specific Platforms

Microsoft VHD Downloads
Microsoft makes available a number of platforms as pre-build virtual hard drives, typically as an time-limited evaluation, but in some situations, I've been able to then authorize them with the MSDN product keys when using for dev or test purposes. The Server 2003/2008 VHDs especially are great for starting off an eval of a server product, rather than stepping through the OS install...every...time.

Installing a New Microsoft Office SharePoint Server 2007 Portal: Step-by-Step Instructions
Not specifically addressing Virtual PC installations, but a common task for within a Virtual PC/Server (Download the evaluation edition)

Installing Microsoft Virtual PC and a DOS Virtual Machine
Haven't came up with an actual useful need for the DOS VM yet, but just having it and being able to boot it quickly drives the point home about being able to use Virtual PC for legacy support.

Linux
Installing Ubuntu 8.04 under Microsoft Virtual PC 2007
This was the most painless Linux distribution to install from scratch on a Virtual PC. But because if you're far from a master of the Linux, but need a test bed for some web apps - the LAMP Virtual Appliance was a great out of the box solution. This is a prebuilt Virtual Hard Drive you can add as Virtual PC, and is preconfigured with a very light-weight Linux no GUI, Apache web server, mySQL database, and the PHP, Perl, and Python engines. (To fully access it from your host relies on some reverse DNS configurations I didn't know, but it for my purposes, was easy enough to alias the IP address and use that)