Microsoft CRM integration: Oracle database access

Microsoft CRM integration: Oracle database access from MS CRM

by: Boris Makushkin

Today’s article topic is customization possibility demonstration for user web interface of Microsoft CRM. As an example we’ll use MS CRM integration with ASP.Net application, accessing customer data access, when customers are stored in Oracle 10g database. Let’s begin:

1. First, let’s create the table to store customer information in Oracle database. We’ll use web application iSQL for table metadata manipulation:

2. Table is now created and contains four fields: CUSTOMER_ID, FIRST_NAME, LAST_NAME и ADDRESS. Fill it with text data:

3. Now we’ll work with data access to Oracle database from ASP.Net application. We should download from Oracle site http://www.oracle.com Windows Instant Client. We don’t have to install it – just unpack all the files in the directory of your choice, for example c:\oracle and set environmental variable TNS_ADMIN, pointing to this directorty.

4. In c:\oracle directory (or where TNS_ADMIN point out) create file tnsnames.ora as following (change host and service names):

ORCL1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost.youtdomain.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL1)

)

)

5. Make correction to windows registry to have MS SQL Linked Server work properly withOracle OLE DB Provider. In the hive KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI make these changes:

OracleXaLib = กoracleclient8.dllก

OracleSqlLib = กorasql8.dllก

OracleOciLib = กoci.dllก

6. Now let’s create Linked Server in MS SQL Server 2000:

Note: in the Security tab we need to use security context with the credentials, having valid access to Oracle Database.

7. Linked Server is ready – let’s test it functioning – open table list. We should see customer table there:

8. Now we’ll create stored procedure for Oracle data access:

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

GO

CREATE PROCEDURE MyCustomersList AS

SELECT * FROM OPENQUERY(ORACLE, กSELECT * FROM Customerก)

RETURN

9. Next step is customizing Microsoft CRM using interface. We’ll add customer list button into Quote screen toolbar. Edit isv.config:

Change Url to your host name.

10. To create ASPX page we’ll use RAD for ASP.Net WebMatrix:

11. Create new page for data access:

12. Change it’s code to access our data:

Sub Page_Load(Sender As Object, E As EventArgs)

Dim ConnectionString As String = กserver=(local);database=Albaspectrum;trusted_connection=trueก

Dim CommandText As String = กEXEC MyCustomersListก

Dim myConnection As New SqlConnection(ConnectionString)

Dim myCommand As New SqlCommand(CommandText, myConnection)

myConnection.Open()

DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

DataGrid1.DataBind()

End Sub

13. Now we’ll test our web application by calling it from MS CRM:

Happy programming, implementation, customization and modification! If you want us to do the job – call use 16309615918, 18665280577, Europe: +49 231 4387600! [email protected]

About The Author

Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies – USA nationwide Oracle, Navision, Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, Texas, Georgia, Florida, New York, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Oracle, Unix, Microsoft CRM SDK, Navision, C#, VB.Net, SQL developer.

[email protected]

This article was posted on February 21

by Boris Makushkin

Microsoft CRM Integration with Oracle – overview f

Microsoft CRM Integration with Oracle – overview for programmer

by: Andrew Karasev

Microsoft Business Solutions CRM is very good budget alternative to Siebel and actually beloved by Microsoftoriented IT department system. Considering the fact that in many cases Microsoft CRM should somehow fit into and cooperate with existing legacy business applications, integration question maybe the first to place. We saw multiple cases when company uses Lotus Notes/Domino, and replaces portion of it with Microsoft CRM and wants to use Domino email server, connected to Microsoft CRM. Also large number of legacy applications (legacy doesn’t necessary mean they are not supported – rather company is trying to get CRM solution fitting its budget). Here we would like to give you some technology highlights on how you can have lookups from Microsoft CRM into Oracle system. Oracle might be custom database, designed for the business, such as cargo tracking system for freight forwarding company, or even Oracle Financials.

Microsoft CRM web interface is built on .Net architecture and Microsoft CRM SDK is married with .Net. We suggest you to use Microsoft tools to connect to Oracle database via ODBC.

Create ODBC linked server to Oracle in Microsoft SQL Server enterprise manager. Again – it is safer to begin from Microsoft .Net side (linked server will be exposed in ADO.Net). Microsoft should support its components in the future and interplatform connection should be standard ODBC

Configure web.config to enable Microsoft CRM customization

Configure isv.config to place the button or navigation bar on one of your MS CRM customizable forms: Lead, Account, Contact, etc.

Use MS CRM SDK sample code to come into Microsoft CRM Security realm

Create separate Database on the same SQL server where Microsoft CRM is hosted and place heterogeneous stored procedures or views – which will be pulling data from Oracle linked server

Program ADO.Net calls and returned dataset processing in Visual Sudio.Net (preferably C#.Net, because Microsoft CRM SDK is C# oriented)

We probably should mention that isv.config buttons allow you to transfer GUID of the Contact, Lead or Account to your integrated web application

Crystal Reports caution – MS CRM has builtin Crystal Reports Enterprise, licensed for MS CRM data access only. So, if you like to integrate web Crystal report into your application – you should resolve licensing issue and check with Microsoft on purchasing full version of Crystal Enterprise. The other way is to separate your Crystal web application and MS CRM by placing them on separate hardware

Good luck with integration! If you want us to do the job give us a call 16309615918 or 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM customization company, serving clients in Chicago, Houston, Atlanta, Phoenix, New York, Los Angeles, San Francisco, San Diego, Miami, Denver, UK, Australia, Canada, Europe and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is CMA, Great Plains Certified Master, Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer. You can contact Andrew: [email protected]

This article was posted on February 18

by Andrew Karasev

Oracle Integration with Microsoft CRM – overview f

Oracle Integration with Microsoft CRM – overview for developer

by: Boris Makushkin

Oracle has huge number of installation worldwide and Microsoft Business Solutions CRM is gaining its market share on the CRM market – mostly for midsize and small companies. Sometimes we see the situation when large company has Oraclebased business system – imaging you ship your goods worldwide and you do it in Oracle custom system for ten years. In this case even if you are looking for budget CRM solution – you might still need integration between Oracle and Microsoft CRM, regardless of the fact that these are coming from different software application worlds: Microsoft and Java realms.

If you are developer – you should probably be informed about two solutions:

Microsoft SQL Linked Server approach – using ODBC driver to Oracle – create linked server in MS SQL Server 2000/7.0 and then use OPENQUERY, OPENROWSET technique to access Oracle data. You can create crossplatform Transact SQL Stored procedures or views to facilitate your development

Oracle Transparent Gateways and Generic Connectivity – these two give you control from Oracle side. If you feel that you are more comfortable with Java/Oracle – this is the way to go. Generic Connectivity gives you common solution to access database via ODBC and OLE DB mechanisms to FoxPro, Microsoft Access, etc. More interesting is second product Oracle Transparent Gateways. Its components are created individually for each platform, resulting in more efficient and fast access and better performance.

Currently Oracle Transparent Gateways are available for the following platforms:

Oracle Transparent Gateway for Informix available on Solaris, HP/UX

Oracle Transparent Gateway for MS SQL Server available on NT.

Oracle Transparent Gateway for Sybase available on Solaris, HP/UX, NT, AIX, Tru64

Oracle Transparent Gateway for Ingres available on Solaris, HP/UX

Oracle Transparent Gateway for Teradata available on Solaris, NT, HP/UX

Oracle Transparent Gateway for RDB available on Alpha OpenVMS

Oracle Transparent Gateway for RMS available on Alpha OpenVMS

The disadvantage is the fact that these products are not available for all platforms and priced relatively high.

Happy developing and designing! If you would like us to do the job, give as a call 1630.961.5918 or 1866.528.0577 [email protected]

About The Author

Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies – USA nationwide Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, New York, Texas, Georgia, Florida, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.

[email protected]

This article was posted on March 15

by Boris Makushkin

Oracle Development: JDeveloper 10G – Java, J2EE, E

Oracle Development: JDeveloper 10G – Java, J2EE, EJB, MVC, XML overview for programmer

by: Boris Makushkin

In 2004 Oracle, Inc. made its new step toward J2EE application development simplification, releasing new RAD Oracle JDeveloper 10G. First of all JDeveloper 10G is targeted to rapid web application building, utilizing all the achievements of J2EE World: web service, EJB, MVC frameworks, XML, etc. Oracle JDeveloper 10G allows you to conduct all full development cycle for complex system – from UML diagrambased concept to debugging, profiling and deployment.

Let’s look at the product main features а:

1. Crossplatform (works under main Unix/Linux platforms and in Microsoft Windows environment) friendly development environment with high level of integration and third party plugins switching on. Syntax highlighting, refactoring, transparent work with RDBMS, bidirectional code generation between UML models, EJB models, required J2EE patterns generation with one click of the button, visual web application builders and other capabilities open the doors for developer, who had never before being dreaming to create industrialstrength J2EE application!

2. PL/SQL stored procedures development and testing, plus integration with major database platforms – Oracle, Sybase, MS SQL Server etc. via JDBC mechanism.

3. Deployment capability for all major application servers – Oracle Application Server, IBM WebSphere, BEA WebLogic, JBoss. OC4J – J2EE container comes with this environment, which perfectly fits for application development and testing.

4. Oracle JDeveloper 10G provides possibility to realize persistent layer for applications on the ADF component base, EJB components or O/R Mapper – TopLink, which is also included.

5. Team Development feature with interoperability with major VCS systems – CVS, Rational ClearCase, Oracle SCM

6. Oracle JDeveloper 10G unique feature is utilization of Oracle ADF (Application Developer Framework) – MVC realization, enabling rapid J2EE application development. View Layer makes it possible to build applications for data exposure as for thin clients, based on web browser viewing, as well as for rich client and even wireless. Controller Layer is built on Jakarta Apache project platform – popular Open Source framework Struts. Business Components Layer may be realized with various technologies Java POJO, Oracle ADF Components, WebServices, EJB or Oracle TopLink Objects

7. At this moment (December 2004) Oracle is testing new version Oracle JDeveloper 10g (10.1.3) Developer Preview. The new features of this new version will be completely redesigned user interface, additional refactoring capabilities, advanced UML diagrams features, web services simplified creation, support for the web application building on Java Server Faces base, complete support for J2EE 1.4 specification, ADF Faces – JSF components and others.

8. You can get production and developer preview for Oracle JDeveloper 10G here http://www.oracle.com/technology/products/jdev/index.html

Happy developing, customizing, implementing and modifying! If you want us to do the job give us a call 18665280577 or 16309615918! [email protected]

About The Author

Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies – USA nationwide Oracle, Navision, Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, Texas, Georgia, Florida, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Oracle, Unix, Microsoft CRM SDK, Navision, C#, VB.Net, SQL developer.

[email protected]

This article was posted on December 24, 2004

by Boris Makushkin