DataTable – Serializable January 20, 2009

Problems using an XML Web service that returns a DataTable
Symptons:
When you browse to the URL of an XML Web service that returns a DataTable from one of its Web methods, you may receive the following error message:
System.Data.DataRelation cannot be serialized because it does not have a default public constructor.
You may also see an error message similar to the following when you try to set a Web reference to this XML Web service in the Visual Studio .NET integrated development environment (IDE):
Internal Server Error. Unable to request “http://localhost/Webservice1/Service1.asmx?WSDL”. The server responded with error code “ProtocolError”.
With the .NET Framework 1.1 Service Pack 1 (SP1), you may receive the following error message:
System.NotSupportedException: Cannot serialize member System.ComponentModel.MarshalByValueComponent.Site of type System.ComponentModel.ISite because it is an interface.
You may also receive a blank page instead of any of these error messages if Show friendly HTTP error messages is enabled in Microsoft Internet Explorer. By default, the Show friendly HTTP error messages option is enabled.

If you try to add a Web reference to an XML Web service that returns a DataTable, you may receive the following error message:
The document at the url http:///vdir/service1.asmx was not recognized as a known document type.

Cause
The DataTable, DataRow, DataView, and DataViewManager objects cannot be serialized and cannot be returned from an XML Web service. To return less than a complete DataSet, you must copy the data that you want to return to a new DataSet.

Solution:
To resolve this issue, return a DataSet instead of a DataTable. DataSet objects can contain one or more DataTable objects.
Datatable is not returned through webservice beacause if you could, you could use for other non .net applications–and it a matter of policy I guess. But from the webservice prospective this should be considered a poor practice as well as from a service-orientation perspective. One of the tenets of service-orientation is that services share contract and schema … and nothing else. Passing a DataSet between Web services violates this tenet, and the result is that it will only work so long as both endpoints are always .NET platforms. (Microsoft products).
A better solution would be to pass entity data that is domain- or business-specific. Not a DataTable, but a set of data that has an actual XSD schema generated in the WSDL for the Web services so that it can be used by clients on any platform. (Microsoft doesnt want folks to use any other platform and so any support thereof is removed)

Removing Duplicate Records using SQL Server 2005 November 19, 2008

Duplicate records can occur numerous ways, such as loading source files too many times, keying the same data more than once, or from just bad database coding. Having a primary key on your table (and you always should have one) can will in the removal of the duplicate records, but even a primary key it is never a fun task to have handed to you to complete.

I will demonstrate how you can use a common-table expression (CTE) in 2005 to easily remove duplicate entries from a table:

;WITH PendingCalculationCTE(EmployeeId, Start, Finish, [Timestamp], TransactionId, LockFlag, Ranking)
AS(
SELECT EmployeeId, Start, Finish, [Timestamp], TransactionId, LockFlag,
Ranking = DENSE_RANK()
OVER(PARTITION BY EmployeeId, Start, Finish, [Timestamp], TransactionId, LockFlag
ORDER BY NEWID() ASC)
FROM PendingCalculation
)

DELETE FROM PendingCalculationCTE WHERE Ranking > 1

 The script above defines my CTE. I am using a windowing function named DENSE_RANK to group the records together based on the EmployeeId, Start, Finish, [Timestamp], TransactionId, LockFlag fields, and assign them a sequential value randomly. This means that if I have two records with the exact same EmployeeId, Start, Finish, [Timestamp], TransactionId, LockFlag values, the first record will be ranked as 1, the second as 2, and so on.
Because a CTE acts as a virtual table, I am able to process data modification statements against it, and the underlying table will be affected. In this case, I am removing any record from the PendingCalculationCTE that is ranked higher than 1. This will remove all of my duplicate records.

DENSE_RANK – Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Count rows number from database tables November 12, 2008

SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = ‘U‘ AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 1 ASC

Can’t View CHM Files in IE7 – A FIX September 24, 2008

If you are having problems viewing CHM files (like the new WCF documentation CHM files), you might be seeing a blank IE screen when you click on any of the subject headings contained ni the file. If this is the case, right-click on the CHM file and select Properties from the provided menu. You can then click on the Unblock button at the bottom. This will then allow the CHM file to be viewed using IE as it should be.

Microsoft JScript runtime error: Automation server can’t create object September 18, 2008

Microsoft JScript runtime error: Automation server can’t create object.
Microsoft JScript runtime error: Object doesn’t support this property or method.
When you implement AJAX features in your ASP.NET page, the client’s browser got the above error messages. When you use client debugging, you further the following code:
// MicrosoftAjax.js
Function.__typeName=”Function”;Function.__class=true;
Function.createCallback=function(b,a){return function(){var e=arguments.length;if(e>0)
{var d=[];for(var c=0;cFunction.createDelegate=function(a,b){return function(){return b.apply(a,arguments)}};Function.emptyFunction=Function.emptyMethod=function(){};
Function._validateParams=function(e,c){var a;a=Function._validateParameterCount(e,c);if(a)
{a.popStackFrame();return a}
for(var b=0;bif(d.parameterArray)f+=”["+(b-c.length+1)+"]“;a=Function._validateParameter(e[b],d,f);
if(a){a.popStackFrame();return a}}return null}

Solution:
This is because the MicrosoftAjax.js in Resource.axd is trying to create object using: xmlHttp=new ActiveXObject(“Msxml2.XMLHTTP”); But in client’s Internet Explorer, the security setting is high. Especially the “Script ActiveX controls marked safe for scripting” in Security tab is disabled. Solution: Make sure that the Internet Zone in the Security Zones settings is set to Medium.

(And Run ActiveX controls and plug-ins  – Enabled)

BEST PRACTICES Boxing and unboxing September 18, 2008

Boxing and unboxing incur overhead, so you should avoid them when programming intensely
repetitive tasks. Boxing also occurs when you call virtual methods that a structure inherits from
System.Object, such as ToString. Follow these tips to avoid unnecessary boxing:
■ Implement type-specific versions (overloads) for procedures that accept various value types.
It is better practice to create several overloaded procedures than one that accepts an Object argument.
■ Use generics whenever possible instead of accepting Object arguments.
■ Override the ToString, Equals, and GetHash virtual members when defining structures.

Private vs. Public Member Variables August 29, 2008

In addition to the usual visibility concerns, you should also avoid unnecessary public members to prevent any additional serialization overhead when you use the XmlSerializer class, which serializes all public members by default.

List of Unicode characters August 21, 2008

http://en.wikipedia.org/wiki/List_of_Unicode_characters

SQL Tips – Inner Join Select August 21, 2008

SELECT tabela1.*, tabela2.Shares, tabela3.domainID
FROM
(SELECT ID, col2, col3, col4
FROM tabela1) tabela1
INNER JOIN
(SELECT col1,COUNT(col1) AS Shares FROM tabela2
GROUP BY col1) tabela2
ON tabela1.ID = tabela2.col1 INNER JOIN
(SELECT col1, col2 FROM tabela2) AS tabela3
ON tabela3.col1= tabela2.col1

TRUNCATE TABLE WITH FOREIGN KEY
dbcc checkident (numeTabela,reseed,1)

SQL Tips – Temp Table August 21, 2008

DECLARE @TabelaTemp AS TABLE (EmployeeID UniqueIdentifier)

INSERT INTO @TabelaTemp
SELECT * FROM dbo.fn_SplitGuidIds(@employeeList,@delimitator)