Sunday, October 21, 2007

Importing Word and Excel as InfoPath 2007 Forms

If you have been working with business forms, you probably have created a few in either Microsoft Word or Excel. One thing to make managing business forms a lot easy is to upload them in your Sharepoint portal. This makes it easy to have a standardized form easily available to the rest of the organization. You can also port them over as InfoPath forms. In Sharepoint 2007, you have the option to use Forms Services instead of individual forms. This makes it easy for users to have a unified and standardized format plus the advantage of binding the form to a backend database for data collection. If you have existing Word or Excel forms, you can easily import and convert them in InfoPath. Just run the Import Form Wizard in InfoPath to convert your existing Word and Excel forms to InfoPath forms - no need to re-create those forms. For more information, check out the Introduction to importing and exporting form data and form templates

When SQL Server backups can't be restored

We always think that having a backup is more than enough to keep us afloat during a disaster. I always say that any backup will be useless unless it is tested. I came across one case where a SQL Server 2000 backup for the master database cannot be restored because it was generated using a different patch set for SQL Server 2000. The backups were generated before applying SQL Server 2000 hotfix 2191. After the patches were applied, a restore was attempted but failed. An error was encountered which specifies that the backup was generated by an earlier version of SQL Server. A best practice approach is to generate backups before and after a patch will be applied. This makes sure that you have valid backups in case something happened. It may be a bit costly as far as disk space is concerned but it sure beats not having a valid backup. It may even save you from spending sleepless nights trying to rollback to the patchset which generated the backups.

Wednesday, October 17, 2007

What happened to SQL Server Enterprise Manager?

I was trying to fix my installation of SQL Server 2005 as my IIS was totally screwed up (this is what happens when you toy around with your machine too much). I uninstalled and reinstalled SQL Server 2005 and the other related services on my test machine which has SQL Server 2000 instances as well. After completing the process, I tested whether both Enterprise Manager and Management Studio works and to my surprise, this is what I got.





Apparently, the uninstall also took out the SQL Server 2005 Backward Compatibility Components. Good thing that a specific Microsoft KB has been released for this problem (Error message when you open SQL Server Enterprise Manager after you uninstall the SQL Server 2005 Backward Compatibility Components: "Snap-in failed to initialize"). My case happens to be a broken registration of the Sqlmmc.dll file so no need to create a registry key named SqlPath. A simple regsvr32.exe to this file which is typically located in the Drive:\Program Files\Microsoft SQL Server\80\Tools\Binn\ folder fixed the problem

Friday, October 12, 2007

Change what? - Change Data Capture in SQL Server 2008

It must have been tough trying to capture INSERT, UPDATE and DELETE activities in a SQL Server database. Usually, developers may resort to creating DML triggers just to solve this problem in previous versions. The problem with this approach is that it introduces unnecessary overhead in the database engine. Another approach is to read the transaction log using either the undocumented DBCC LOG and fn_dblog or using third-party tools like Lugiment Log Explorer, ApexSQL Log, or RedGate SQL Log Rescue. In SQL Server 2005, the concept of DDL triggers were introduced, thus making it easier to audit DDL activities. This new feature of SQL Server 2008 called Change Data Capture(CDC), system stored procedures are used to mark which types of objects you want to audit, and the stored procedures take care of how the auditing occurs. How it works is that it captures DML statements (this is quite interesting) asynchronously, reading the transaction logs (so much for third-party tools) and populates the CDC table with the row's data which change, and expose the changes through a relational interface which can be consumed easily. The downside of this is that the feature is only available on Enterprise and Developer Editions of SQL Server 2008. To use this feature, you have to enable it on the database level. Again, I'll use the very familiar Northwind database for this sample.

USE Northwind
EXEC sys.sp_cdc_enable_db

Once CDC is enabled, it creates the corresponding objects are created: cdc schema, cdc user, metadata tables and other system objects. The thing about this is that you have to enable it on a per table basis (I don't know why Microsoft chose to do it this way but if you decide to do this on all user-defined tables, you definitely need to write a script to accomplish this)

EXEC sp_cdc_enable_table 'dbo', 'EmployeeTerritories', @role_name = 'cdc_role';

Just to explain the parameters passed to the stored procedure, the dbo happens to be the schema in which the table belongs, EmployeeTerritories is the name of the table which will be enabled for CDC while the @role_name parameter is the name of the database role used to have access to change data. If the role already exists, permissions are given to that role. If it doesn't, it will be created with the specified name. Now, let's simulate changes in the dbo.EmployeeTerritories table (don't worry about referential integrity as this has been taken cared of)

INSERT dbo.EmployeeTerritories VALUES (4,98004), (4,98052);
WAITFOR DELAY '00:00:05';
UPDATE dbo.EmployeeTerritories SET TerritoryID =80202
WHERE employeeid = 4 AND TerritoryID=98052;


These information is stored in a new table created named schema_table_CT in the cdc schema. In this case, it would be the cdc.dbo_EmployeeTerritories_CT table. Querying this table is similar to executing the DBCC LOG command in previous versions of SQL Server - it just doesn't make sense. Good thing Microsoft came up with table-valued functions which makes querying this table a lot easier (but not straight-forward). One function which we can look at is the cdc.fn_cdc_get_all_changes__ function. This function returns one row for each change applied to the source table within the specified log sequence number (LSN) range. We don't really care about LSN ranges but this is how SQL Server tracks all changes in the transaction log. This is also part of the information stored in our database backups. Since we have this data in our cdc.dbo_EmployeeTerritories_CT table, we can use this data and pass it as a parameter in this function. Below is a sample script to use this function to query the cdc.dbo_EmployeeTerritories_CT table but would return a more meaningful resultset


DECLARE
@begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);

-- Obtain the beginning of the time interval.
SET @begin_time = GETDATE()-1
;

-- Obtain the end of the time interval.
SET @end_time = GETDATE();

-- Map the time interval to a change data capture query range.
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time
);

-- Return the changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_EmployeeTerritories(@from_lsn, @to_lsn, 'all');
GO

You might notice the new function introduced - fn_cdc_map_time_to_lsn. This just returns the log sequence number (which we don't really care about) corresponding to the specified time (which we are more familiar with). The resultset would be something we could understand. What we need to really look at is the __$operation column which returns code values for the operations done for the specified table: 1 = delete, 2 = insert, 3 = update (before image), and 4 = update (after image). Imagine an UPDATE statement to be like a DELETE followed by an INSERT that's why you have a before and after images. Another way to translate the results of the cdc.dbo_EmployeeTerritories_CT table is to use the query below

SELECT
operation = CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'UPDATE(BEFORE)'
WHEN 4 THEN 'UPDATE(AFTER)'
ELSE 'UNDEFINED' END,
*
FROM cdc.dbo_EmployeeTerritories_CT


Bear in mind though that the data in the cdc tables are available within 3 days expressed in minutes (4320). You can clean this up when necessary using the sp_cdc_cleanup_change_table stored procedure.

This gives you an overview about change data capture and how you can use it in SQL Server 2008. Download the RC0 and start playing around with it. Check out my SQL Server 2008 videos at BlogCastRepository.com
Google