Archive for June, 2015

Microsoft Dynamics CRM 2015 Workflow Tools

June 16, 2015

Project Description

This project contains Tools created in WorkFlow Activities to be imported in Dynamics CRM, to use them
All the Source code is included and open.

Right now there are this tools:

  • Force Calculate Rollup Field

Since Dynamics CRM 2015, we can add Rollup fields. The Rollup fields calculation is an asynchronous process, and with this project, we are giving more possibilities to this calculation.
The idea is to use the Workflows (Sync & Async) with custom workflow Activity, to force this calculation when the user define.

  • Apply Routing Rules

This Action forces the execution of the active Routing Rules for the Case passed in the parameter

  • Query Values Step

This Action could be used to query to another entity with two filters fields, and get up to two fields. Very usefull for example to query a custom entity used with parameters.

  • Share Record With Team

This Action could be used to Share a record with a Team .

  • Share Record With User

This Action could be used to Share a record with a User.

  • Unshare Record With Team

This Action could be used to Unshare a record with a Team.

  • Unshare Record With User

This Action could be used to Unshare a record with a User.

workflowActivity4.gif

Advertisements

Creating a linked server with Microsoft SQL Server 2008 and HiT OLE DB providers

June 8, 2015

You can use HiT OLEDB Server/400 or HiT OLEDB Server/DB2 and Microsoft SQL Server 2008 to manage data stored on DB2 and integrate it with SQL Server. Using the Distributed Query features that Microsoft SQL Server 2008 offers, you can join data stored on native databases, with tables stored on DB2, MS Access files and almost any type of file. To use this functionality, you will need to create a Linked Server, and a connection to your remote database.

If you are trying to create a linked server to DB2/400, you will need HiT OLEDB Server/400. For DB2 running on other platforms, please use HiT OLEDB Server/DB2 instead.

Before creating the linked server, you need to set the “Allow InProcess” provider option. This is to allow Microsoft SQL Server 2008 to use the HiT OLEDB provider DLL by loading it directly into the Microsoft SQL Server 2008 address space.

Setting a Provider Option from SQL Server Management Studio

  1. From the menu Start\Programs\Microsoft SQL Server 2008, click on SQL Server Management Studio.
  2. Select Providers from the Server Objects/Linked Server section.
  3. Right-click on the HITOLEDB provider name, then choose the Properties option.
  4. Make sure that the the Allow inprocess option is checked.

Creating a Data Link File using the HiT Toolbox

The easiest way to create a linked server is to use an UDL file, also know as data link file. This will allow you to specify all connection parameters in one file and simply refer to the file for all future connections. A data link file can be created with the HiT Toolbox. For more information about creating and configuring a data link file, look under “Creating a Data Link in the Toolbox” in HiT OLEDB ToolBox Help.

Once you have created the data link file, here are a few things to note when creating a linked server

  • In the linked server property, data source field, type the name of the UDL file including the .UDL extension. For example, if you created a data link in the HiT OLEDB ToolBox and called it “MyAS400”, you should enter “MyAS400.udl” in the linked server property data source field.
  • In the linked server property, product name field, type the name of the OLE DB data source to add as a linked server. For example “OLEDB for AS400”.
  • When a data link file is created, by default, it is placed as follows:
    Windows XP: C:\Program Files\Common Files\System\Ole DB\Data Links Windows 2008, Vista or Windows 7: C:\ProgramData\HiT Software\Data Links
    In HiT OLEDB version 5.0, if you keep the UDL file in the default location, SQL Server is smart enough to know where to look for it and there is no need to specify the path. However, if you are using an older version of the HiT OLEDB provider or if you moved the file to a different location, you will need to specify the complete path to the file unless using the default location in Windows XP environment.
  • We recommend using the udl file for the connection. However, if you need to run INSERT, UPDATE or DELETE statements in addition to SELECT statements, please add the following into the linked server provider string:
    Port Number=8471; Rowset Locate=True;

Then just follow steps 1 through 6 below to finish.

If you choose not to use a data link file to create a linked server, use the steps below.

Creating a Linked Server using SQL Server Management Studio

  1. From the menu Start\Programs\Microsoft SQL Server 2008, click on SQL Server Management Studio.
  2. Select Linked Server from the Server Objects
  3. Right-click on Linked Server, New linked Server and insert data in the configuration window.
  4. In the General tab insert the required parameters:

Linked Server:   Insert the reference name for this linked server. It’s preferable to use a name without spaces in order to avoid problems when using it in an SQL statement.

Provider Name: Select HiT Software OLEDB Provider for DB2/400 if you are trying to connect to the IBM DB2 for i (iSeries/AS400). If you are trying to connect to DB2 on platforms other than iSeries/AS400, please choose HiT Software OLEDB Provider for DB2 instead.

Product Name:   Insert the name of OLEDB data source to add as linked server.

Data Source:      Insert the IP address of your DB2 server or the name of a data link (UDL) file.

Provider String: Insert the connection string with the necessary parameters.

Example:

                Libraries=My_libraries; Init Timeout=90; Port Number= 8471; Host Code Page=037;Rowset Locate=True;

Please note that the parameters for provider string are cases sensitive.

  1. Select the Security For information on setting security parameters, click on Help.
  2. Use the Server Options tab to view or specify the following options:
  • Set Collation Compatible as TRUE if you want the WHERE and ORDER BY clauses on character fields to be resolved by DB2 and not by SQL Server. For a detailed description of the Collation Compatible option, look up Collation Compatible in the HiT Software knowledge base.
  • Check RPC Out to call a DB2 stored procedure using Query Analyzer and T-SQL.

After this operation, the tables on the linked server are available for all features that are compatible with Microsoft SQL Server 2008.

You can use either Four Part Name Query or OpenQuery with linked server. For example, for a Linked Server called AS400, open a new query window in SQL Server Management Studio and run either of the following:

Four Part Name Queries:

Select * from AS400.ServerName.MyLib.Customers Update AS400.ServerName.MyLib.Customers Set FIRSTNAME = ‘John’ Where ID=5 Delete from AS400.ServerName.MyLib.Customers Where ID=10

where:
AS400 is the Linked Server Name ServerName is the IBM DB2 for i (AS/400) Server Name MyLib is the IBM DB2 for i Library Name Customers is the Table Name

OpenQueries:

Select * from OpenQuery (AS400 , ‘Select * from MyLib.Customers’ ) UPDATE Openquery (AS400 , ‘Select * from MyLib.Customers Where ID=5’ ) Set FIRSTNAME = ‘John’ Delete Openquery (AS400 , ‘Select * from MyLib.Customers Where ID=10’ )