Saturday, October 27, 2007
The answer to this is PowerShell, a command line shell and scripting environment which will enable you to manage almost everything. One of its most powerful features is the ability to extend it with .NET components as well as being able to use the out of the box .NET Framework objects.
For Exchange 2007 they have first written the PowerShell layer and based the GUI entirely on these scripts. The GUI will also support a Script This Action (sound like we were ahead in SQL Server) which will create the PowerShell script for you.
At the customer we have something we call DCS (Data Conversion Scripts), basically it means going from one version of the database to the next version. We had a great .NET console application combined with some batch files which does this for us. Although this worked great we felt this was getting too complex to manage because we do parallel development and support different version at the same time. A new system was required using a simpler concept which my colleague killspid had proven was possible.
We started out with a batch file which used sql scripts with SQLCMD to perform the DCS task. Although this worked fine the lead DBA had a new requirement which needed more logic and was hard to do in a batch file or T-SQL. We decided to rewrite the DCS in PowerShell and I am very glad we did. Using a PowerShell script, SQLCMD and an XML config file we now support all the requirements and we have much more control than we did in the previous versions. The support for variables in SQLCMD has also proven itself very useful so do not underestimate the power of SQLCMD either.
I must admit the syntax and way of working is a bit peculiar at first because you have to lose the .NET mindset and get into the scripting mindset. If you are used to creating administrative scripts you will probably have less of a problem.
Tip: if you want to get rid of the output SQLCMD gives you in PowerShell add >$null behind it.
eg. sqlcmd -SmyServer -dmyDatabase -E -b > $null
Tuesday, October 23, 2007
SQL Server 2008 now supports something called Table Valued Parameters (or TVP) which can help you in these situations. TVP's make it possible to use a "table" as a parameter for a procedure. A couple of limitations apply, TVP's can only be READONLY in the procedure that define them as a parameter and they can only be used as an input parameter. Apart from this the same rules apply to TVP's as to table variables for example no DDL can be executed against a TVP and no statistics are kept for TVP's.
A little example will make it very clear.
--Create test table
CREATE TABLE myUsers
(ID int, UserName varchar(50), UserRole tinyint);
--Create the required type
CREATE TYPE UserRoleType AS TABLE
( UserRole tinyint );
--Create procedure that takes the type as a parameter (READONLY is required)
CREATE PROCEDURE GetUsersInRole
@UserRoleType UserRoleType READONLY
FROM myUsers u
INNER JOIN @UserRoleType ut ON u.UserRole = ut.UserRole
--Insert some test data (multiple inserts in one go, another new feature)
INSERT INTO myUsers
VALUES (1, 'Wesley', 1),
(2, 'Tom', 2),
(3, 'Patrick', 2),
(4, 'Jan', 3),
(5, 'Bregt', 3)
--Throw in a new dmv to look at the type and check dependencies if you like
--SELECT * FROM sys.table_types
--SELECT * FROM sys.dm_sql_referenced_entities ('dbo.GetUsersInRole', 'OBJECT')
--Lets use another new features (initialize var on declare!)
DECLARE @Admin tinyint = 1
DECLARE @PowerUser tinyint = 2
DECLARE @User tinyint = 3
--Add parameter values to the table valued parameter
--INSERT INTO @UserRoleType VALUES (1), (2)
INSERT INTO @UserRoleType VALUES (@Admin), (@PowerUser)
--Call stored procedure with specific type (remember the post is about table valued parameters)
EXEC GetUsersInRole @UserRoleType;
DROP PROCEDURE GetUsersInRole
DROP TYPE UserRoleType
DROP TABLE myUsers