My Note on Solutions.

Wednesday, September 15, 2010

programmatically retrieve SQL Server stored procedure source code, query wheter the code contains some keyword.

Here is a quick script to receptive the SQL Server sp source code, Or query whether it contains some keyword or Not. the keyword could be a table name, snippet. etc.

create table #test1( text varchar(2000))
create table #test2( text varchar(2000))
insert into #test1 select SCHEMA_NAME(schema_id) + '.'+name from sys.procedures

DECLARE db_cursor CURSOR FOR
select text from #test1
declare @name varchar(3000)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
       insert into #test2   exec sp_HelpText @name
       if exists(select * from #test2 where text like '%yourkeyword%')
        print 'found,procedure is ' + @name
        FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
drop table #test1
drop table #test2

If you query all adventoreworks sp for AS keyword. you will get

(11 row(s) affected)
found,procedure is dbo.uspPrintError

(43 row(s) affected)
found,procedure is dbo.uspLogError

(31 row(s) affected)
found,procedure is dbo.uspGetBillOfMaterials

(30 row(s) affected)
found,procedure is dbo.uspGetEmployeeManagers

(30 row(s) affected)
found,procedure is dbo.uspGetManagerEmployees

(31 row(s) affected)
found,procedure is dbo.uspGetWhereUsedProductID

(35 row(s) affected)
found,procedure is HumanResources.uspUpdateEmployeeHireInfo

(24 row(s) affected)
found,procedure is HumanResources.uspUpdateEmployeeLogin

(22 row(s) affected)
found,procedure is HumanResources.uspUpdateEmployeePersonalInfo

Tuesday, September 14, 2010

Fix WCF AddressFilter mismatch error, customized IServiceBehavior , WCF service behind Load balancer or Firewall

If you WCF service is hosed behind Load balancer or firewall , or been forwarded by any kind of device. you may end with an error like

The message with To 'http://127.0.0.1:8888/bHost1/WCFService1.svc' cannot be processed at the receiver, due to an AddressFilter mismatch at the EndpointDispatcher. Check that the sender and receiver's EndpointAddresses agree.

if you have the source code, you may have to put one attribute to each of those services.

[ServiceBehavior(AddressFilterMode=AddressFilterMode.Any)]

that’s fine if you own the source code. or just handful of service. If you have hundreds of service, that will be terrible change. How to do that in that case.

answse is pretty simple, write a behavior and apply it to the configuration. All you have to do is writing a behavior which will change the default addressfilter, and Apply the behavior by changing the web.config or app.config

using System;
using System.Collections.Generic;
using System.Text;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using System.ServiceModel.Configuration;

namespace Androidyou.TestLib
{
    public class OverrideWCFAddressFilterServiceBehavior : IServiceBehavior
    {
        public void AddBindingParameters(ServiceDescription serviceDescription, System.ServiceModel.ServiceHostBase serviceHostBase, System.Collections.ObjectModel.Collection<ServiceEndpoint> endpoints, System.ServiceModel.Channels.BindingParameterCollection bindingParameters)
        {

        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, System.ServiceModel.ServiceHostBase serviceHostBase)
        {
            for (int i = 0; i < serviceHostBase.ChannelDispatchers.Count; i++)
            {
                ChannelDispatcher channelDispatcher = serviceHostBase.ChannelDispatchers[i] as ChannelDispatcher;

                foreach (EndpointDispatcher dispatcher2 in channelDispatcher.Endpoints)
                {
                    dispatcher2.AddressFilter = new MatchAllMessageFilter();
                }
            }
        }

        public void Validate(ServiceDescription serviceDescription, System.ServiceModel.ServiceHostBase serviceHostBase)
        {

        }
    }
    public class OverrideAddressFilterModeElement : BehaviorExtensionElement
    {

        public override Type BehaviorType
        {
            get { return typeof(OverrideWCFAddressFilterServiceBehavior); }
        }

        protected override object CreateBehavior()
        {
            return new OverrideWCFAddressFilterServiceBehavior();
        }
    }
}

Config change.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.serviceModel>
        <behaviors>
            <serviceBehaviors>
                <behavior name="newbehavior">
                    <matchalladdressfilter/>
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <extensions>
            <behaviorExtensions>
               <add name="matchalladdressfilter" type="Androidyou.TestLib.OverrideAddressFilterModeElement, Androidyou.TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
            </behaviorExtensions>
        </extensions>

        <services>
            <service name="ConsoleApplication4.SayService" behaviorConfiguration="newbehavior">
              <host>
                <baseAddresses>
                  <add baseAddress="http://localhost:9999/svc"/>

                </baseAddresses>
              </host>
                <endpoint address="ws" binding="wsHttpBinding" bindingConfiguration="wsbinding"
                    contract="ConsoleApplication4.SayService" />
            </service>
        </services>
    </system.serviceModel>
</configuration>

Or you may apply the behavior in Code.

host.Description.Behaviors.Add(new Androidyou.TestLib.WCFSecurityLib.OverrideWCFAddressFilterServiceBehavior());

if you want to change addressfiltermode programmatically, you may put a switch into the above behaviors, like a static variable, then change the switch programmatically which will cause the behavior to refresh the filtermode.

Friday, September 10, 2010

Biztalk 64bit Host CPU 100% , Microsoft.BizTalk.MsgBoxPerfCounters.CounterManager.RunCacheThread

I’ve been involved to nail down one CPU 100% issue. per my understanding, the ETW trace API failed and caused the System to run the refresh in an infinite loop.

what’s the symbol of the event?

there are 9 HOSTs in one Biztalk server. only 1 Host kept hitting 100% cpu. even there was no business activity. ( no message came in, no orchestration get invoked. )

what did I do to locate the problem.

Run a memory Dump, then open the dump in Windbg, see which thread is CPU hungry.

run !runaway
91:1235 0 days 3:35:28.796
92:9740 0 days 3:34:24.140

threads 91 and 92 are the top threads which consume a lot CPU time.

then Run a clrstack to see what are those two threads doing.

!91s //switch to thread 91
!clrstack –a //view the stacktrace, and show the instance of the variable
Both 91 and 92 get the same result.

00000000143fc810 00000642783437fa System.Diagnostics.StackTrace.ToString(TraceFormat)
00000000143fc8f0 00000642808b34e6 System.Exception.get_StackTrace()
00000000143fc930 000006427f535468 Microsoft.BizTalk.MsgBoxPerfCounters.CounterManager.RunCacheThread()
00000000143fe850 00000642808b3ac7 Microsoft.BizTalk.MsgBoxPerfCounters.MgmtDbAccessEntity.UpdateDeltaMACacheRefreshInterval()
00000000143fe8e0 00000642808b32b7 Microsoft.BizTalk.MsgBoxPerfCounters.MgmtDbAccessEntity.RunCacheUpdates(Int32)
00000000143fe970 00000642782f173b Microsoft.BizTalk.MsgBoxPerfCounters.CounterManager.RunCacheThread()
00000000143fea10 000006427838959d System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
00000000143fea60 000006427f602672 System.Threading.ThreadHelper.ThreadStart()

Let’s see the stacktrace from bottom up, the closed Biz code is the Microsoft.BizTalk.MsgBoxPerfCounters.CounterManager.RunCacheThread
the update by default should be invoked every 60 seconds. why it keep invoking. ???

since memory dump is a static analysis and snapshot of the memory. then a run a cordbg, what surprised me is that the process keep throwing exceptions.
like this

first chance exception generated: (0xe00a18b8) <System.Threading.ThreadAbortExcetion>
first chance exception generated: (0x1600a0e70) <System.Threading.ThreadAbortExcption>
first chance exception generated: (0xe00a2ff0) <System.Threading.ThreadAbortExcetion>
first chance exception generated: (0x1600a21b0) <System.Threading.ThreadAbortExcption>
first chance exception generated: (0xe00a4580) <System.Threading.ThreadAbortExcetion>
first chance exception generated: (0x1600a38d8) <System.Threading.ThreadAbortExcption>
first chance exception generated: (0xe00a58d8) <System.Threading.ThreadAbortExcetion>
first chance exception generated: (0x1600a4e60) <System.Threading.ThreadAbortExcption>
first chance exception generated: (0xe00a7010) <System.Threading.ThreadAbortExcetion>
first chance exception generated: (0x1600a61a0) <System.Threading.ThreadAbortExcption>
first chance exception generated: (0xe00a85a0) <System.Threading.ThreadAbortExcetion>

then I turned on the “unhanled exceptino on” by run “ca e” get the exception details

first chance exception generated: (0x120128fd0) <System.Threading.ThreadAbortExc
ption>
_className=<null>
_exceptionMethod=<null>
_exceptionMethodString=<null>
_message=(0x120129058) "Thread was being aborted."
_data=<null>
_innerException=<null>
_helpURL=<null>
_stackTrace=(0x1201290a8) <System.SByte[]>
_stackTraceString=<null>
_remoteStackTraceString=<null>
_remoteStackIndex=0
_dynamicMethods=<null>
_HResult=-2146233040
_source=<null>
_xptrs=0
_xcode=-532459699
xception is called:FIRST_CHANCE
ative disassembly not available.
cordbg)

is it the same stacktrace, yes it is. run W

)* Microsoft.BizTalk.MsgBoxPerfCounters.MgmtDbAccessEntity::UpdateDeltaMACacheR
freshInterval +0171[native] +0035[IL] in <Unknown File Name>:<Unknown Line Numb
r>
) Microsoft.BizTalk.MsgBoxPerfCounters.MgmtDbAccessEntity::RunCacheUpdates +01
1[native] +0039[IL] in <Unknown File Name>:<Unknown Line Number>
) Microsoft.BizTalk.MsgBoxPerfCounters.CounterManager::RunCacheThread +0279[na
ive] +0093[IL] in <Unknown File Name>:<Unknown Line Number>
) System.Threading.ExecutionContext::Run +0155[native] +0095[IL] in <Unknown F
le Name>:<Unknown Line Number>
) System.Threading.ThreadHelper::ThreadStart +0077[native] +0025[IL] in <Unkno
n File Name>:<Unknown Line Number>
) [Internal Frame, 'AD switch':(AD ''. #) -->(AD '__XDomain_3.0.1.0_0'. #7)]

then I use the ILDASM , and Open the assembly, go the the 0x 23 Lines.

IL_001c: callvirt   instance void [Microsoft.BizTalk.Tracing]Microsoft.BizTalk.Tracing.Trace/HackTraceProvider::TraceMessage(uint32,
                                                                                                                               string,
                                                                                                                               object[])
IL_0021: ldnull
IL_0022: stloc.2d

it is a ETW trace API.

Trace.Tracer.TraceMessage(4, "MgmtDbAccessEntity: Entering RunCacheUpdates fn for host " + this.hostName, new object[0])

what a bad design for the code that Tracing API (non-functional) broke the functional application, and even didn't catch the unhandled exception

More troubleshooting entries.

Free WCF / ASMX Test Trace tool. SOAPbox by vordel

in Free WCF / ASMX Test Trace tool. SoapUI and TCPTrace, I explained the features of SoapUI. besides, SoapUI, SOAPBox is also one great tool to be used as a wcf/asmx test tool. SoapBox is offered for free download by the xml gateway vendor vordel.

Download and install from the link , http://www.vordel.com/products/soapbox/GetSOAPbox.html

Given a very basic Service.

when run the host application, you should be able to access the WSDL.

When you start the SOAPbox , the screen looks as bellow.

Click File->Import WSDL. you can browse the WSDL file locally or enter the url.

then select the operations available in the WSDL to test

then Click the blue arrow button to run.

hint: right click the content panel, click format to make the xml more user-friendly to view.

you can click the service and change the Port Url.

if the service requires a basic authentication, you can input the token here.

also you may chose Kerberos authentication.

for rest service or http testing, you may change the http verb.

for more security features, like Encryption, signature.

Conclusion, SOAPBox has more security features than SoapUI. while SOAPUI has more support on different protocols. like jms, jdbc, etc.

more FREE tools for application developers and system administrators.

Wednesday, September 8, 2010

Free MAC HFS file system viewer on windows

When you’ve installed windows 7 on a Macbook, and always switch between two Mac and windows. you may have noticed that you can’t view the MAC file system on windows 7.

when you open the disk manager utility, the partition of mac system is unknown.

what’s the gap ? Windows is based on NTFS/Fat, while MAC uses different file system called HFS. By default, windows doesn’t recognize and understand the file system format, Microsoft might meant to do that. there is a PC , why Mac:)

with the help of a free utility called HFSexplorer. you can read the MAC file system in windows.

simply download the zip and extract the bits. click the HFSexplorer.exe please make sure java runtime is installed on the pc, the utility is based on java technology.

Click file->load file from device.

click load , then you will be able to navigation the Mac system.

here you have to select the file or folder, click the EXTRACT button to transform the file to windows format.

more FREE tools for application developers and system administrators.

Hello Lucene, Indexing and searching

I am reading the book Lucene in Action, Second Edition: Covers Apache Lucene 3.0. in the chapter one, there is one basic java program which do the 101 indexing and searching.

Here are some basic tutorial to do that.
1. there is only one core jar file necessary for the engine to run, you can download it from http://www.apache.org/dyn/closer.cgi/lucene/java/

2. open the eclipse , create one java project and reference the core jar file.

3. Create a text file , and put some contents. then save it as test.txt

4. write some java code to do the indexing and searching.

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;

import org.apache.lucene.analysis.Analyzer;
import org.apache.lucene.analysis.standard.StandardAnalyzer;
import org.apache.lucene.document.Document;
import org.apache.lucene.document.Field;
import org.apache.lucene.document.Field.Index;
import org.apache.lucene.document.Field.Store;
import org.apache.lucene.document.Fieldable;
import org.apache.lucene.index.*;
import org.apache.lucene.index.IndexWriter.MaxFieldLength;
import org.apache.lucene.queryParser.QueryParser;
import org.apache.lucene.search.IndexSearcher;
import org.apache.lucene.search.Query;
import org.apache.lucene.search.ScoreDoc;
import org.apache.lucene.search.TopDocs;
import org.apache.lucene.store.Directory;
import org.apache.lucene.store.FSDirectory;
import org.apache.lucene.store.LockObtainFailedException;
import org.apache.lucene.util.Version;

public class Program {

    public static void main(String[] args) {
        try {
            IndexFile("/Users/androidyou/Documents/lucence/data/test.txt",
                    "/Users/androidyou/Documents/lucence/index");

Search("/Users/androidyou/Documents/lucence/index","nonexistedkeyworld");
Search("/Users/androidyou/Documents/lucence/index","apache");

        } catch (Exception e) {
            // TODO Auto-generated catch block
        }
        System.out.println("done");
    }

    private static void Search(String indexpath, String keyword) throws Exception, IOException {
        IndexSearcher searcher=new IndexSearcher(FSDirectory.open(new File(indexpath)));
        System.out.println("Search keyword " + keyword);
        Query query=new QueryParser(Version.LUCENE_30, "content", new StandardAnalyzer(Version.LUCENE_30)).parse(keyword);

        TopDocs docs= searcher.search(query, 10);
        System.out.println("hits " + docs.totalHits);
        for(ScoreDoc doc: docs.scoreDocs)
        {
            System.out.println("doc id" + doc.doc + "doc filename" + searcher.doc(doc.doc).get("filename")) ;
        }

}

    private static void IndexFile(String datafolder, String indexfolder) throws CorruptIndexException, LockObtainFailedException, IOException {
        Analyzer a=new StandardAnalyzer(Version.LUCENE_30);
        Directory d=FSDirectory.open(new File(indexfolder));
        MaxFieldLength mfl=new MaxFieldLength(4000);
        IndexWriter iw=new IndexWriter(d, a, mfl);

        Document doc=new Document();
        Fieldable contentfield=new Field("content", new FileReader(datafolder));
        doc.add(contentfield);
        Fieldable namefield=new Field("filename",datafolder, Store.YES, Index.NOT_ANALYZED);
        doc.add(namefield);

iw.addDocument(doc);
iw.commit();

}
}

And here, if you run the program three times, there will be three “Documents” in the index repository.
here, I will get

Search keyword nonexistedkeyworld
hits 0
Search keyword apache
hits 3
doc id 0 doc filename/Users/androidyou/Documents/lucence/data/test.txt
doc id 1 doc filename/Users/androidyou/Documents/lucence/data/test.txt
doc id 2 doc filename/Users/androidyou/Documents/lucence/data/test.txt
done

also you can download the lucene toolkit luke. and Open the index directory.

from the snapshoot above, you can see there are 3 documents inside the Index. for each document, it has two fields. totally 58+1=59 terms

for the content field. by default . method Field(name , filereader) only index the field, not store it.
when you click Documents tab, you can browse the document individually. also you can verify that only filename is stored in the index. for the content filed, just terms. (indexed content.)

Friday, September 3, 2010

Update CSV File to Solr

By Default, Solr has several RequestHandlers that been mapped to different URLs.

you can check these settings in the solr.xml

so you can post document (xml format) to /update
or CSV document to /update/csv

Here is one quick example.

Let's say you have a simple CSV file named EE.csv

id;title
1;AndroidYou
2;David

post via CSV
run

curl "http://localhost:8080/solr/update/csv?commit=true&separator=%3b" --data-binary @ee.csv -H "Content-Type:text/plain"

then you may query

http://localhost:8080/solr/select/?q=id:2 OR id:1 to see these documents

Pages