No more ISAPI Filter in SPS

All SharePoint developesl will be pleased to know that Microsoft has finally removed the ISAPI filter of SPS which sometimes becomes pain in the neck. Now all the requests will be processed by the ASP.Net ISAPI and the redirection will be controlled by HTTP Modules and Hanlders. So if you want to write your own hooks in the processing of SPS, you just have to write either handler or module. No more un-managed code.

Document Conversion in Office 2007

Currently I am working in Petroleum Development Oman for the implementation of Office 2007.

Here I have Office 2007, SQL and Domain Controller all installed on the same machine. While using document conversion services. I got error that failed to perform the action CE_Other. I tried all the options but failed to configure it. Finally, someone from Microsoft told me that Document Conversion services do not work on the DC. You need to use them from some other machines due to some reason. Well it is good thing that these services are not allowed to run on DC as they are working with the file system as well but it should be documented at some place.

Lets see what else will come.

Navision Vs Axapta

Many of us who are working on Microsoft Technologies are interested in the differences between GP, Axapta and Navision. In this search I came to the following annoted by Gerrit Combrink and Rene on the Microsoft.Public.Axapta newsgroup which might be helpful:

The focus areas of both products are around Manufacturing, Distribution and Services. The main differences are with functionality and technology.

(In Axapta, all this functionality is standard. There is no standard Services functionality in Navision. Such functionality needs to be built by ISVs.)

Axapta is aimed more towards the top end Mid market to corporate customers with complex requirements, while Navision looks after the lower to middle mid market segment with less complex requirements.

(Lower meaning: about 10 concurrent users. Above 20 users: Axapta)

Technology within Axapta also allows it to be a much stronger, more stable, higher performing competitor in a 3-tier environment as this is built into the existing architecture. You can also run a lot more concurrent users on a Axapta system than on Navision.

(Axapta's AOS technology (Axapta Object Server) provides scalability. Axapta has been tested from 1 user up to 3500 concurrent users.)


http://www.microsoft.com/BusinessSolutions/default.mspx

These are certain guidlines given by Oliver in deciding which product to use:



1. If the customer is more a technology freak -> Axapta
2. If the user need a vertical solutions -> Navision
3. If you need web services -> Axapta
4. If you have less than 30 Users -> Navision
5. If you have more than 500 Users -> run Axapta
The conclusion is that the use of Axapta or Navision is not decided on a rational basis, but on the basis emotions and sympathy for one of these products by the prospect.

Distributed Queries

Introduction

Many times we face the scenario where we have to execute some query on more than one database. One such example may be you have separate databases for each regional office and corporate office. At the end of year, you want o compile accounts and the effect of each regional office should reflect in these accounts. So you have to query each regional database but how to do this.

Well it is not so difficult. SQL server provides us system stored procedures and SQL functions that allow us to perform such operations.

Using the code

It is really easy to perform operations on different databases using distributed queries. Distributed queries provide SQL Server users with access to:

• Distributed data stored in multiple instances of SQL Server.
• Heterogeneous data stored in various relational and non-relational data sources accessed using an OLE DB provider.

Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory™, other SQL Servers and so on) using the security context of the Microsoft Windows account under which the SQL Server service is running. SQL Server 2000 impersonates the login appropriately for Windows NT logins; however, that is not possible for SQL Server logins.

Tables and views in external data sources can be referenced directly in SELECT, INSERT, UPDATE, and DELETE Transact-SQL statements. Because distributed queries use OLE DB as the underlying interface, distributed queries can access traditional relational DBMS systems with SQL query processors, as well as data managed by data sources of varying capabilities and sophistication. As long as the software owning the data exposes it in a tabular rowset through an OLE DB provider, the data can be used in distributed queries.

The simple way of performing a select operation against different data sources is:

Exec sp_addlinkedserver @server='Test', @srvproduct='', @provider='SQLOLEDB', @datasrc='192.168.1.254', @catalog='LuckyERP_LAL_Live'

Exec sp_serveroption 'Test', 'data access', 'true'

Exec sp_addlinkedsrvlogin 'Test', 'true'

Select * From OPENQUERY(Test, 'SELECT * FROM dbo.SalesContractHeader')

Where

@server: Is the label for the data-source that will be used to refer to it.

@srvproduct: Is the product name of the OLEDB data-source to be linked as the linked server. In case of SQL Server, you can either set it as ‘SQL Server’, in that case you can’t specify other parameters or you can leave it empty and set other parameters like catalog name and the IP address or host name of the SQL Server.

@provider: Is the name of the OLEDB provider for the data-source.

@datasrc: Is the name of the datasource as interpreted by the OLEDB provider. It can be either IP address of the server or its host name.

@catalog: Is the name of the catalog. In case of SQL Server, it is the name of the database to be linked to.

Executing sp_serveroption is optional. It is used to configure linked server. You can refer msdn for details. In our case, I enable ‘data access’ to linked server. The default behavior also allows data access to linked server. So doing this is not necessary.

Sp_addlinkedsvrlogin creates or updates a mapping between logins on the local instance of Microsoft SQL Server and remote logins on the linked server. For details refer msdn. The first parameter of this stored procedure is the local name of the remote server as specified in sp_addlinkedserver. The second parameter is @useself. A value of true specifies that SQL server authenticated logins use their own credentials to connect to remote server. In case the credentials are different then use false and specify locallogin, remote login and remote password after @useself parameter as 3rd, 4th and 5th parameters.

Using this stored procedure is also optional as default behavior is to use SQL server authenticated logins’ credentials to connect to remote server.

Now after all things are properly configured, we come to actually executing a distributed query. Will it be different than executing ordinary select or DML queries? I don’t think so. There is only a slight difference. Use OPENQUERY to execute the specified pass through query on the given linked server and here we are done with executing a distributed query.

Points of Interest

Have you notice that it is not necessary that the linked server is always SQL Server. Yup you can use any OLEDB source as the data-source in distributed queries including SQL Server, Oracle, MS-Access, ODBC data-source, File system of Indexing Service, MS-Excel Sheets and IBM DB2 Databases. Wow what a long list and you can also develop your own data-source by implementing OLEDB interface. Thanks to Microsoft SQL we are able to perform operations on heterogeneous data stored in such diversified data-sources.