Thursday, July 31, 2008

Comparing Two Tables By SQL Stored Procedure

I have created a SQL Stored Procedure to compare any two tables based on Microsoft SQL Server 2005 new syntax EXCEPT and INTERCEPT. Basically, I used EXCEPT and UNION to get the result of differences between tables and INTERCEPT to get the same result of two tables.

In addition to that, the SP will compare two tables by specifying column fields and conditions and display the result by optional ORDER BY clause. Here is the SP:

CREATE procedure [dbo].[SP_CompareTables] (
@table1 varchar(100),
@table2 varchar(100),
@table_colList varchar(3000) = NULL,
@whereClause varchar(3000) = NULL,
@orderByClause varchar(3000) = NULL,
@difference0 int = 0
)
AS
DECLARE
@sql varchar(8000);
DECLARE @colList varchar(3000);
BEGIN
if
( @table_colList is null Or @table_colList = '' )
begin
set
@colList = '*';
end
else
begin
set
@colList = @table_colList;
end
if
( @difference0 = 0 )
begin
set
@sql =REPLACE(REPLACE(REPLACE('
SELECT ''@table1'' AS TblName, *
FROM (
SELECT @colList
FROM @table1
EXCEPT (
SELECT @colList
FROM @table2)
) x
UNION ALL
SELECT ''@table2'' AS TblName, *
FROM (
SELECT @colList
FROM @table2
EXCEPT (
SELECT @colList
FROM @table1)
) y'
,
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
end;
else
begin
set
@sql =REPLACE(REPLACE(REPLACE('
SELECT @colList
FROM @table1
INTERSECT (
SELECT @colList
FROM @table2)'
,
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
end;
if ( @whereClause is not null And len(@whereClause) > 0 )
begin
set
@sql = REPLACE(REPLACE('
SELECT * FROM (@sql) v
WHERE @whereClause'
,
'@sql', @sql),
'@whereClause', @whereClause);
end
if
( @orderByClause is not null And len(@orderByClause) > 0 )
begin
set
@sql = REPLACE(REPLACE('@sql
ORDER BY @orderByClause'
,
'@sql', @sql),
'@orderByClause', @orderByClause);
end;
print @sql;
exec(@sql);
return 0;
END


To use this SP is very simple. For example, to compare two tables of [Employees] and [Employees_backup], you can just run the following script to compare two whole tables:

EXEC SP_CompareTables 'Employees', 'Employees_backup';


More examples by specifying columns, where clause and order clause:

EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName',
'FirstName like ''D%'' AND BirthDate Between ''Jan 01, 1990'' AND ''Jul 30, 2008''';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName',
NULL, 'FirstName, BirthDate' , 1; -- get same results

Read More...

Saturday, July 26, 2008

SQL Server Project (4)

I have to close this series articles on SQL Server Project. The final part will cover some special issues related to SQL Server Project.

The first issue is the connection to SQL server. As I mentioned in SQL Server Project (2), it is recommended to use the context connection since the CLR assembly is already in a SQL server running process. However, this connection can only be created once. You cannot create another context connection for other executions. Normally, you don't need another one in one stored procedure for example. However, if you create several SQL procedures, functions, and triggers in one dll, you might get exceptions if one calls another since only one context connection is allowed. You don't have control who is going to run these stored procedures, functions, or triggers.

Therefore, I think it is better to create one SQL item (SP, function or trigger) in one dll. You could create a normal SQL connection with catalog for a db table, user name, and password information in a connection string if you have to, and that connections can be created more than once. In a SQL server context, it does not make sense to do that unless you need to connect to another SQL server or Oracle server.

An related issue is that always to handle exceptions in your assembly and close any opened connections. As I mentioned before, the assembly is loaded to SQL server running process, and it would not unloaded automatically when your SP exits. The assembly may still in memory. If you don't handle exceptions, the opened connection will block the same SP being called again.

The second issue is that some assemblies may not work in SQL server project. I tried NHibernate and some other dlls as my references. What I found is that some assembly reflection functions are not working in SQL server. For example, I found that there always exceptions when these dlls try to load another assembly file to get class or property information. All these kind calls cause exceptions. This is very unfortunate and I think this is very bad limitation for SQL server project. I have no idea why and how exceptions would happen.

If you are going to write your SQL server project in C#, all the parameters in a SP have to be specified whey the SP is called, unlike MS Transact SQL SP could have default values for parameters.

The installation and deployment process for a SQL Server Project is a complicated one. As I mentioned in my previous articles, some asymmetric keys, log in permissions have to be created, and dependency assemblies have to be registered. The deployment of SQL server project is one click process if you have source codes and Visual Studio avalailbe. However, that click-only-once deployment may hide some SQL calls to register and set up all SPs, function and triggers. If you want to create an automation process in SQL, you have to keep all the assembly files available somewhere even they are not referenced after the installation and deployment. To uninstall it, the process is reverse. You have to remove all the SPs, functions, and triggers first, and then to delete assemblies, and then other dependency items. Since there are many things involved, anything wrong may cause your assembly not being functional.

I had a case that my CLR assembly SP did not work one day. It says about some permissions to load assembly failure. I tried to remove all the assembly and to re-install again, but I could not remove them as well. I was stuck in the middle. Finally I found it is the case SQL server was in low virtual memory. The error message actually was misleading. I restarted the SQL sever and reloaded files I did in the middle, then everything worked fine. Therefore, be prepared to handle all the uncertainties, document all the procedures in a well organized way and save all the source codes and dependency files in repository.

Read More...