FAST Impulse – SQL Queries to Monitor the JDBC Connector Execution

If you run FAST ESP + Impulse and use the Impulse and the JDBC connectors to load data into FAST you probably need to monitor how the connector is running and processing documents for each of your collections.

I use the following SQL queries to get the current status of the items on the Impulste Items database (ImpulseItems schema).

This query shows all the documents per collection and per different statuses:

SELECT collection_name, update_flag, COUNT(*)
FROM ImpulseItems.status (NOLOCK)
GROUP BY collection_name, update_flag
ORDER BY collection_name

This query shows the statuses fo the documents for a specific collection:

SELECT update_flag, COUNT(*)
FROM ImpulseItems.status (NOLOCK)
WHERE collection_name = '[collection name]'
GROUP BY update_flag

The status is defined by the update_flag field.

The update_flag field value means:

  • < -1: document is being deleted by one of the JDBC connector instances. This number represents the number of the JDBC process defined on the NodeConf.xml file.
  • -1: document should be deleted when the JDBC connector instance runs.
  • 0: no updates on the document / document doesn’t need to be processed.
  • 1: document has been update and needs to be processed.
  • between 2 and 221: document is being processed by one of the JDBC connector instances. This number represents the number of the JDBC process defined on the NodeConf.xml file.
  • 222: document is being loaded by the EXLT connector.
  • 333: document is locked by EML server.

It is important for you to run the queries using the NO LOCK query hint in order to avoid interference on the execution of the connector (no blocking on SQL Server processes).

See you,

Amadeu.

Advertisements

SharePoint 2010 Configuration Wizard – Cannot connect to database master at SQL Server at server_name

This happened today and I’d like to share with you guys. The SharePoint 2010 Configuration Wizard sometimes gives you error messages that can mislead you about the root cause of the problem.  Take the following error message as an example:

“Cannot connect to database master at SQL Server at <server_name>. The
database might not exist, or the current user does not have permission
to connect to it.”

I got this error message trying to run the Configuration Wizard on a WFE server in a farm. I’ve seen several blog posts about this error message and the cause can vary a lot. After checking the possible cause I found out that the server name used by this WFE has already been used by an old server that used to be part of the same farm.

In order to solve it, I removed the old server from the farm using Central Administration, cancelled the configuration wizard and reran it. The wizard worked as a charm.

This kind of experience makes me think: is it so much harder to thrown meaningful error message instead of misleading ones?

Am I being excessively demanding?

See you,

Amadeu.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

You know that sometimes you are requested to work on weird situations with crazy errors messages, no clue on what is the actual problem and pretty simple solutions (after you finally discover what is causing the error). It can happen to all of us IT people and this happened to me a few weeks ago.

We were running an .NET application to access an Oracle database. All of a sudden it started to throw the following error message:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

This is a very deceiving error message. It makes you believe you don’t have to Oracle client installed. We are using Oracle client 9i and we detected the same error both on the server and on the developer workstation.

If you search on Google this error message you’ll see a bunch of related articles.

It seems that the main cause of this error message is lack of permissions for the Authenticated Users group on ORACLE_HOME directory. On example of a solution for this issue can be found here. This article describes how to change the permissions on the ORACLE_HOME folder to allow the authenticated users to access it.

The first thing we tried was to grant the correct permissions to the authenticated users group. It didn’t work. We also tried to go to the machines and check for more than one Oracle client installations. We didn’t find it.

Another attempt to debug this issue was to check all the references on the Visual Studio project. I couldn’t find an issue. The reference was pointing to the correct version of the System.Data.OracleClient.dll.

Other step was to make sure the application pool was running in 32 bits since the Oracle client used is a 32 bit version. You just need to go to IIS Manager, select the application pool and edit advanced settings. Change the ‘Enable 32-bit Applications’ property to True.

Then we tried to copy part of the code to a new simple console application and it worked just fine. It was kind of weird. So we thought about  checking the Oracle client DLL loading process. Using Processor Monitor to check how the application loaded the Oracle client we could have an idea on how to loading process worked. A good way to do it is using the filter on Processor Monitor to check for the Oracle client library (the oci.dll). The filter used in this case was path ending with “oci.dll”.

First, download and unzip Process Monitor. When you open it, it will start showing all information about all processes running on your computer, so it will lots of entries.

So, you should stop it while you configure the filters. Go to the File menu and uncheck the Capture Events option.

The go to the filter menu and choose Filter or click on the filter button on the toolbar.

On the filter dialog choose the conditions: [Path] [ends with] [oci.dll] [Include] and click on the Add button. Click OK to close the dialog.

Enable the event capture again, run the application and check out where it is looking for the oci.dll. Using this filter we could see that the application was trying to load the oci.dll from a different path than the ORACLE_HOME directory. In our case, you can see by the image, it was looking for it inside the GAC folder where the System.Data.OracleClient is installed and the path defined on the operating system:

  • C:\Windows\SysWOW64\inetsrv
  • C:\Windows\SysWOW64
  • C:\Windows\System
  • C:\Windows
  • And then a strange folder which doesn’t exist on the server: C:\[some username]\product\instantclient_11_2

This last folder was very weird and I was expecting the ORACLE_HOME folder to be on this list instead. So, the only explanation for that was: the code. Checking the code we found out it was setting the ORACLE_HOME environment variables and that was the cause of the error. Removing those lines of codes solved our issue. It was done by a developer not involved on this debugging process….so it took a while to figure it out 😦

I have a recommendation for this case if you are a developer: DON’T SET ENVIRONMENT VARIABLES  INSIDE YOUR CODE!!!! It causes the debug process to be very long and painful if the developer who did it is not involved.
If you really need to do it, think about using configuration files (such as the web.config) or using conditional compilation statements and create specific build configurations so your developers can compile the code to local versions when they need to.

To demonstrate the conditional compilation feature I created a Visual Studio solution. The first task you have to perform is to create a new configuration on the Visual Studio configuration manager (go to Build > Configuration Manager).

Then create a new configuration, give it a name and select which existing configuration should be used as a template for this new one.


Go to the project’s properties and on the Build tab, select the configuration you created. On the Conditional compilation symbols field specify the conditional constants you need for your code separated by space.


Go back to your code window, select the configuration on the drop-down box and use the conditional constant on your code.


In this sample code I just use the conditional constant to change the value of a variable, but you could do whatever you need to run your Oracle client locally.

Running the code on Local mode, you get this result:

Running in Debug mode the result is different:

So, if you know what is causing the error is pretty simple to solve it, but with non-explanatory error messages you might have a hard time doing it.

This post is dedicated to @tucaz. As they say in Brazil: “It can happen in the best families.” lol… 🙂

See you,

Amadeu.