posts - 51 , comments - 2 , trackbacks - 0

Tuesday, June 16, 2015

Connect to and Query Eloqua Data in QlikView over ODBC

Create data visualizations with Eloqua data in QlikView.

The CData ODBC drivers expand your ability to work with data from a wide variety of data sources. This article outlines simple steps to connect to Eloqua data and create data visualizations in QlikView.

Populate a Chart with Eloqua Data

The steps below supply the results of an SQL query to a visualization in QlikView. In this article, you will create a bar chart with the query below:

SELECT Name, ActualCost FROM Campaign
  1. If you have not already, specify connection properties in a DSN (data source name). You can use the Microsoft OBC Data Source Administrator to create and configure ODBC DSNs. Typical connection properties are the following:

    • User
    • Password
    • Company

    See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.

  2. Click Edit Scripts and add the connection. You can select the DSN on the Data tab in the Edit Scripts wizard.
  3. After selecting the DSN, click Select to build the query in the SELECT statement wizard. Or, you can enter the query directly into the script.
    A script that connects and executes an SQL query. (Salesforce is shown.)
  4. Create a new bar chart with Name and ActualCost as the x- and y-axes using the Create Chart wizard.
  5. Finish the wizard to generate the chart.

    A chart populated with the results of a query. (Salesforce is shown.)

Posted On Tuesday, June 16, 2015 3:52 AM | Filed Under [ cdata ODBC Eloqua QlikView ]

Wednesday, June 17, 2015

Lightning Connect to NetSuite External Objects

Salesforce Lightning Connect enables Salesforce users to connect to data from OData sources from Salesforce in the same way that users interact with standard Salesforce objects. Through Lightning Connect, Salesforce customers can build real-time analysis from within their Salesforce dashboard and other Salesforce1 apps. In this article we are going to show you how you can enable bi-directional connectivity between NetSuite CRM and ERP data from Salesforce using the Cloud Drivers from CData Software to create OData feeds of live NetSuite data.

The Cloud Drivers from CData Software are lightweight web applications that proxy live data through standards-based interfaces like ODATA, SOAP, REST, HTML, RSS, ATOM, JSON, XLS, and CSV, making it easily accessible across platforms and devices. The NetSuite Cloud Driver enables you to securely feed NetSuite Leads, Contacts, and Opportunities to Salesforce Lightning Connect.

In this article, we will consume NetSuite objects in Salesforce filter lists. We will also create related lists that show related NetSuite and Salesforce objects. You can follow the same general procedure for connecting to data from any other available Cloud Drivers, including — Microsoft Dynamics, QuickBooks, Oracle, Google services, etc.

The NetSuite Cloud Driver makes it easy to work with nontraditional data sources like NetSuite as tables. The tables are intuitive and the underlying OData protocol is transparent. However, the benefits are immediately apparent: OData enables you to see changes to your data in real time.

See NetSuite as Salesforce Objects

After you have configured Salesforce to connect to the Cloud Driver, you can use NetSuite external objects just as you would standard Salesforce objects. For example, you can create a filter list to search NetSuite invoices from your dashboard:

You can also create a related list to display NetSuite accounts alongside your Salesforce accounts:

Set Up the Cloud Driver

The same setup process can be followed for all cloud drivers: deploy, connect, and authorize.

Deploy

The cloud driver runs on your own server. On Windows, you can deploy the stand-alone server. On a Java servlet container, drop in the cloud driver WAR file. You can also host the cloud driver in IIS.

Connect

After you deploy, provide authentication values and other connection properties. The cloud driver uses authtoken-based authentication and supports the major authentication schemes. The cloud driver also supports SSL.

Authorize

After you connect, allow the cloud driver access to each NetSuite table you want to work with.

Create a NetSuite Lightning Connect Data Source

After the cloud driver is running, follow the steps below to set up Lightning Connect to consume the cloud driver's NetSuite feed.

  1. Log into Salesforce and click Setup -> Develop -> External Data Sources.
  2. Click New External Data Source.
  3. Enter values for the following properties:
    • Label: Enter a label to be used in list views and reports.
    • Name: Enter a unique identifier.
    • Type: Select the option "Lightning Connect: OData 2.0".
    • Server URL: Enter the URL to the cloud driver's OData endpoint. The format of the OData URL is https://your-server:8032/api.rsc.
  4. In the Authentication section, set the following properties:
    • Identity Type: If all members of your organization will use the same credentials to access the cloud driver, select "Named Principal". If the members of your organization will connect with their own credentials, select "Per User".
    • Authentication Protocol: Select Password Authentication to use HTTP Basic authentication.
    • Certificate: Enter or browse to the certificate to be used to encrypt and authenticate communications from Salesforce to your server.
    • Username: Enter the username for a user known to the cloud driver.
    • Password: Enter the user's authtoken.

Synchronize NetSuite Objects

After you have created the external data source, follow the steps below to create NetSuite external objects that reflect any changes in the data source. You will synchronize the definitions for the NetSuite external objects with the definitions for NetSuite tables.

  1. Click the link for the external data source you created.
  2. Click Validate and Sync.
  3. Select the NetSuite tables you want to work with as external objects.

See NetSuite as a Database

The NetSuite Cloud Driver enables you to use data access standards like OData to work with NetSuite data as a database, without loading the data into a database. Using the NetSuite Cloud Driver in Salesforce is almost the same as using the SQL Server, Oracle, or MySQL Cloud Drivers. The Cloud Driver abstracts NetSuite entities into normalized tables. This relational view provides data integrity: You can use the cloud driver to provide trusted OData feeds of NetSuite data.

Working with NetSuite transactions and lists is straightforward. To expand the lists of a table into separate tables, simply set the IncludeChildTables connection property to true and reconnect. When you connect, the NetSuite Cloud Driver dynamically obtains the table definitions. In Lightning Connect, you can sync these definitions with external objects.

Posted On Wednesday, June 17, 2015 1:16 AM | Filed Under [ salesforce odata cdata cloud drivers Lightning Connect ]

Tuesday, July 7, 2015

Use the CData ODBC Driver for NetSuite in SAS JMP

You can use the CData ODBC Driver to integrate NetSuite data into the statistical analysis tools available in SAS JMP. This article shows how to use NetSuite data in the Graph Builder and Query Builder.

You can use the CData ODBC Driver for NetSuite to integrate live data into your statistical analysis with SAS JMP. The driver proxies your queries directly to the NetSuite API, ensuring that your analysis reflects any changes to the data. The CData ODBC Driver supports the standard SQL used by JMP in the background as you design reports.

The NetSuite API supports bidirectional access. This article shows how to access NetSuite data into a report and create data visualization. It also shows how to use SQL to query and manipulate NetSuite data from the JMP Query Builder.

Access NetSuite Data as an ODBC Data Source

If you have not already done so, specify connection properties defined in the data source name (DSN).

Below is a typical connection string:

Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;

You can configure the DSN in the built-in Microsoft ODBC Data Source Administrator. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to use the Microsoft ODBC Data Source Administrator to create and configure a DSN.

Import NetSuite Data with the Query Builder

After you have created the NetSuite DSN, you can use SQL to invoke the capabilities of the NetSuite API. Follow the steps below to execute some supported queries in the Query Builder:

  1. In SAP JMP, click File -> Database -> Query Builder. The Select Database Connection dialog is displayed.
  2. Click New Connection.
  3. On the Machine Data Source tab, select the DSN. In the next step, the Select Tables for Query dialog is displayed.
  4. In the Available Tables section, select a table and click Primary.
  5. As you drag Available Columns to the Included Columns tab, the underlying SQL query is updated.
  6. Click Run Query to display the data.
  7. To refresh the results with the current data, right-click Update from Database and click Run Script.

Manipulate NetSuite Data

You can execute data manipulation queries from JSL scripts such as the one below. To execute a script, click New Script in the toolbar. To connect, specify the DSN. You can then use the standard SQL syntax:

Open Database( "DSN=CData NetSuite Source;",
"INSERT INTO SalesOrder
(Class_Name)
VALUES (''Furniture : Office'');");

Vizualize NetSuite Data

After importing, you can use the Graph Builder to create graphs visually. To open the Graph Builder, click the Graph Builder button in the toolbar.

  1. Drag a dimension column onto the x axis. For example, CustomerName.
  2. Drag a measure column onto the y axis. For example, SalesOrderTotal.
  3. Select a chart type. For example, a bar chart.

Posted On Tuesday, July 7, 2015 3:47 AM | Filed Under [ cdata ODBC data netsuite SAS JMP ]

Publish Reports with Salesforce Data in Crystal Reports

Use the Report Wizard to design a report based on up-to-date Salesforce data.

Crystal Reports provides built-in support for the JDBC standard. The CData JDBC Driver for Salesforce enables you to use the Report Wizard and other tools to access Salesforce data in Crystal Reports. This article shows how to create a simple report that features Salesforce data.

Connect To Salesforce Data

Follow the procedure below to use the Report Wizard to create the Salesforce connection.

  1. If you have not already done so, specify connection properties in a DSN (data source name). You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. Typical connection properties are the following:

    • User
    • Password
    • SecurityToken

    See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.

  2. In a new report, click Create New Connection -> ODBC.

  3. In the resulting wizard, click Select Data Source and select the DSN in the Data Source Name menu.

Design a Report

After adding a JDBC connection to Salesforce, you can then use the Report Wizard to add Salesforce data to your report.

  1. Configure the data source by selecting the tables and fields needed in the report. This example uses the Name and AnnualRevenue columns from the Account table.

  2. Configure the chart type. For example, create a Bar Chart that aggregates the values in the Name column. After naming the chart, select Account.Name from the 'On change of' menu. In the 'Show summary' menu, select the SUM function and Account.AnnualRevenue. As you complete the wizard, Crystal Reports builds the SQL query to be executed to Salesforce data. The driver executes the query against the live Salesforce data.

  3. Configure other filters and the report template, as needed.

Preview the finished report to view the chart, populated with your data. If you want to filter out null values, use a SelectionFormula.

 

Posted On Tuesday, July 7, 2015 3:36 AM | Filed Under [ salesforce cdata jdbc reports Crystal Reports ]

Connect to Dynamics CRM Data in Jaspersoft Studio

Create reports based on live data in Jaspersoft Studio.

This article shows how to use the CData JDBC Driver for Dynamics CRM to create a basic Jaspersoft Studio report that features Dynamics CRM data in a table and a chart. Each time you run the report, the chart and table will display the live data. You will use wizards in JasperSoft to build several SQL queries that will populate the report elements. The driver enables you to use standard SQL while skipping the process of copying the data into a relational database. Instead, queries are executed directly to the underlying Dynamics CRM API.

Connect to Dynamics CRM Data as a JDBC Data Source

You can use the Data Adapter wizard to connect to JDBC data sources in Jaspersoft Studio. Follow the steps below to connect to Dynamics CRM data from your project. You will a data adapter for Dynamics CRM and add it to the workspace.

  1. In the Repository Explorer, right-click the Data Adapters node and click Create Data Adapter.
  2. Select Database JDBC Connection.
  3. Enter a user-friendly name for the driver.
  4. On the Driver Classpath tab, click Add. In the resulting dialog, navigate to the lib subfolder of the installation directory. Select the driver JAR.
  5. On the Database Location tab, the following information is required:
    • JDBC Driver: Enter the class name of the JDBC driver, cdata.jdbc.dynamicscrm.DynamicsCRMDriver
    • JDBC URL: Enter the required connection properties in the JDBC URL. You must enter connection properties in name-value pairs separated by semicolons. Below is a typical JDBC URL for Dynamics CRM:

      jdbc:dynamicscrm:User=myuseraccount;Password=mypassword;URL=https://myOrg.crm.dynamics.com/;CRM Version=CRM Online;

Create Reports with Dynamics CRM Data

After you create a data adapter for Dynamics CRM, you can add Dynamics CRM data to JasperReports. This section shows how to populate one of the included templates with Dynamics CRM data.

  1. Click File -> New Jasper Report. Select a template, select the parent project, and enter a name for the report.
  2. In the Data Adapter wizard, select the data adapter you created in the previous section.
  3. In the Diagram tab, you can build the query visually: Drag tables into the box and click the columns you want. You can also enter a custom query. For example:
    SELECT * FROM Account
  4. Select the fields you want to include in the dataset.

In the Preview tab, you can see the report as it would look with the current Dynamics CRM data.

Add a Chart

Follow the steps below to add a chart of Dynamics CRM data to an existing report. You will add a bar chart to the end of the report template you created in the previous section.

  1. Click the Design tab.
  2. In the Outline view, right-click the root node for the report and click Create Dataset.
  3. Enter a name for the dataset and click the option to create a new dataset from a connection or data source.
  4. In the Data Adapter menu, select the Data Adapter you created in the first section.
  5. Enter the following query:

    SELECT Contact.FirstName, SUM(Account.NumberOfEmployees) FROM Contact, Account GROUP BY Contact.FirstName
  6. Select the fields you want in the dataset. This example uses all fields.
  7. If you already used the GROUP BY clause when you entered the query, skip the Group By step. In this example, the driver's SQL engine aggregates the specified columns. So, no fields are selected in this step.

After adding the dataset, follow the steps below to create the chart:

  1. Click the Summary node in the Outline view. In the Properties view, set the height to 400 pixels. The summary band is printed at the end of the report.
  2. Drag a chart from the Palette onto the summary. The chart wizard is displayed.
  3. Select the chart type. This example uses a bar chart.
  4. In the Dataset menu, select the dataset you created for the chart.
  5. In the Dataset tab, select the option to use the same connection used by the master report. The chart wizard will enter the parameter for the connection and change the menu selection to Use Another Connection.
  6. Click the button next to the Series menu and delete the default series. Click Add.
  7. In the Expression Editor that is displayed, double-click a column to create a new series for each column value. For example, FirstName. When the series is set to FirstName, a new bar will be created for each FirstName.
  8. Click the button next to the Value box to open the Expression Editor for the measures of the chart. Double-click a column to add it to the y-axis, for example, NumberOfEmployees.
  9. Click the button next to the Label box to open the Expression Editor for the dimensions of the chart. Double-click a column to add it to the x-axis, for example, FirstName.
  10. In the Category box, enter "" for an empty string, as this example does not use a category.

After you create the chart, do some basic formatting to seamlessly add the subreport to the report, without any unused space.

  1. Right-click the chart and click Size to Container -> Fit Both.
  2. In the main report, right-click the subreport and click Size to Container -> Fit Both.

Save any changes to the report before previewing. The chart is displayed on the last page of the report.

Posted On Tuesday, July 7, 2015 3:30 AM | Comments (0) | Filed Under [ dynamicscrm cdata jdbc dynamics Jaspersoft ]

Tuesday, June 16, 2015

Connect SharePoint to SQL Server through SSIS

Using SQL Server as a backup for critical business data provides an essential safety net against loss. In addition, backing up data to SQL Server enables business users to more easily connect that data with features like reporting, full-text search, analytics, and more.

This example demonstrates how to use the SharePoint ADO.NET Data Provider inside of a SQL Server SSIS workflow to transfer data directly from SharePoint and/or SharePoint Online into a Microsoft SQL Server database. The exact same procedure outlined below can be used with any CData ADO.NET Data Providers to connect SQL Server directly with remote data via SSIS.

  1. Open Visual Studio and create a new Integration Services Project.
  2. Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
  3. In the Data Flow screen, add a DataReader Source and an OLE DB Destination from the Toolbox.
  4. Add a new Data Connection, and select your provider as .NET Providers\CData SharePoint Data Provider.
  5. In the connection manager, enter the connection details. This example uses a SharePoint blog page.
  6. Open the DataReader editor and set the following information:
    • ADO.NET connection manager: In the Connection Managers menu, select the Data Connection you just created.
    • Data access mode: Select 'SQL command'.
    • SQL command text: In the DataReader Source editor, open the Component Properties tab and enter a SELECT command, such as the one below:
      SELECT * FROM Posts
  7. Close the DataReader editor and drag the arrow below the DataReader Source to connect it to the OLE DB Destination.
  8. Open the OLE DB Destination and enter the following information in the Destination Component Editor.

    • Connection manager: Add a new connection. Enter your server and database information here. In this example, SQLExpress is running on a separate machine.
    • Data access mode: Set your data access mode to "table or view" and select the table or view to populate in your database.
  9. Configure any properties you wish to on the Mappings screen.
  10. Close the OLE DB Destination Editor and run the project. After the SSIS Task has finished executing, your database will be populated with data obtained from SharePoint.

SSIS Sample Project

To get started using the SharePoint Data Provider within SQL Server SSIS, download the fully functional sample project.

Note: Before running the demo, you will need to change your connection details to fit your environment.

Posted On Tuesday, June 16, 2015 4:10 AM | Filed Under [ ado.net sharepoint cdata ssis SQL server ]

Replicate PreEmptive Analytics Data from PowerShell

Write a quick PowerShell script to query PreEmptive Analytics data. Use connectivity to the live data to replicate PreEmptive Analytics data to SQL Server.

The CData ODBC Driver for PreEmptive Analytics enables out-of-the-box integration with Microsoft's built-in support for ODBC. The ODBC driver instantly integrates connectivity to the real PreEmptive Analytics data with PowerShell.

You can use the .NET Framework Provider for ODBC built into PowerShell to quickly automate integration tasks like replicating PreEmptive Analytics data to other databases. This article shows how to replicate PreEmptive Analytics data to SQL Server in 5 lines of code.

You can also write PowerShell code to execute create, read, update, and delete (CRUD) operations. See the examples below.

Create a DSN

If you have not already done so, specify connection properties defined in the data source name (DSN). Typical connection properties are the following:

  • User
  • Password
  • AuthScheme
  • URL

You can configure the DSN in the built-in Microsoft ODBC Data Source Administrator. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to use the Microsoft ODBC Data Source Administrator to create and configure a DSN.

Connect to PreEmptive Analytics

The code below shows how to use the DSN to initialize the connection to PreEmptive Analytics data in PowerShell:

$conn New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DSN=CData PA Source x64"

Back Up PreEmptive Analytics Data to SQL Server

After you enable caching, you can use the code below to replicate data to SQL Server.

Set the following connection properties to configure the caching database:

  • CacheProvider: The name of the ADO.NET provider. This can be found in the Machine.config for your version of .NET. For example, to configure SQL Server, enter System.Data.SqlClient.

  • CacheConnection: The connection string of properties required to connect to the database. Below is an example for SQL Server:

    Server=localhost;Database=RSB;User Id=sqltest;Password=sqltest;

The SQL query in the example can be used to refresh the entire cached table, including its schema. Any already existing cache is deleted.

$conn.Open()
# Create and execute the SQL Query
$SQL "CACHE DROP EXISTING SELECT * FROM " $FeaturesSummary
$cmd New-Object System.Data.Odbc.OdbcCommand($sql,$conn)
$count $cmd.ExecuteNonQuery()
$conn.Close()

The driver gives you complete control over the caching functionality. See the help documentation for more caching commands and usage examples. See the help documentation for steps to replicate to other databases.

Other Operations

To execute any SQL command to an ODBC data source in PowerShell, initialize the command and then call ExecuteNonQuery. Below are some more example CRUD commands to PreEmptive Analytics through the .NET Framework Provider for ODBC:

Retrieve PreEmptive Analytics Data

$sql="SELECT Count, FeatureName from FeaturesSummary"
 
$daNew-Object System.Data.Odbc.OdbcDataAdapter($sql$conn)
$dtNew-Object System.Data.DataTable
$da.Fill($dt)
 
$dt.Rows | foreach {
$dt.Columns | foreach ($col in dt{
Write-Host $1[$_]
}
}
$sql="SELECT Count, FeatureName from FeaturesSummary"
 
$daNew-Object System.Data.Odbc.OdbcDataAdapter($sql$conn)
$dtNew-Object System.Data.DataTable
$da.Fill($dt)
 
$dt.Rows | foreach {
Write-Host $_.count $_.featurename
}

Update PreEmptive Analytics Data

$cmd New-Object System.Data.Odbc.OdbcCommand("UPDATE FeaturesSummary SET Time='01/01/2015' WHERE Id = @myId"$conn)
$cmd.Parameters.Add(new System.Data.Odbc.OdbcParameter("myId","001d000000YBRseAAH")
$cmd.ExecuteNonQuery()

Insert PreEmptive Analytics Data

$cmd New-Object System.Data.Odbc.OdbcCommand("INSERT INTO FeaturesSummary SET Time='01/01/2015' WHERE Id = @myId"$conn)
$cmd.Parameters.Add(new System.Data.Odbc.OdbcParameter("myId","001d000000YBRseAAH")
$cmd.ExecuteNonQuery()

Delete PreEmptive Analytics Data

$cmd New-Object System.Data.Odbc.OdbcCommand("DELETE FROM FeaturesSummary WHERE Id = @myid"$conn)
$cmd.Parameters.Add(new System.Data.Odbc.OdbcParameter("myId","001d000000YBRseAAH")
$cmd.ExecuteNonQuery()

Posted On Tuesday, June 16, 2015 4:02 AM | Filed Under [ cdata ODBC powershell PreEmptive Analytics ]

Use Crystal Reports to create reports with Salesforce data

Crystal Reports provides built-in support for the JDBC standard. The CData JDBC Driver for Salesforce enables you to use the Report Wizard and other tools to access Salesforce data in Crystal Reports. This article shows how to create a simple report that features Salesforce data.

Install the JDBC Driver

Install the CData JDBC Driver for Salesforce by including the driver JAR in the Crystal Reports classpath: Set the DataDriverCommon element in the CRConfig.xml file to the path to the JAR.

The CRConfig.xml is usually located at C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java — the path might be slightly different based on your installation. The driver JAR is located in the lib subfolder of the installation directory.

Connect To Salesforce Data

Follow the procedure below to use the JDBC connection wizard to connect.

  1. In a new, blank report, click Create New Connection->JDBC (JNDI).
  2. In the wizard, select the JDBC connection URL:

    jdbc:salesforce:User=username;Password=password;AccessToken=Your_Access_Token;
  3. Set the driver class name:

    cdata.jdbc.salesforce.SalesforceDriver

Design a Report

After connection a JDBC connection to Salesforce, you can then use the Report Wizard to add Salesforce data to your report.

  1. Configure the data source by selecting the tables and fields needed in the report.

  2. Configure the chart type. For example, create a Bar Chart that aggregates the values in the Name column. After naming the chart, select Account.Name from the 'On change of' menu. In the 'Show summary' menu, select the SUM function and Account.AnnualRevenue.

  3. Configure other filters and the report template, as needed.

Preview the finished report to view the chart, populated with your data. If you want to filter out null values, use a SelectionFormula.

Posted On Tuesday, June 16, 2015 2:09 AM | Filed Under [ salesforce cdata data reports Crystal Reports ]

Tuesday, June 30, 2015

Connect to Salesforce Data as a Linked Server

Use the TDS Remoting feature of the ODBC Driver to set up a linked server for Salesforce data.

You can use the TDS Remoting feature to set up a linked server for Salesforce data. After you have started the daemon, you can use the UI in SQL Server Management Studio or call stored procedures to create the linked server. You can then work with Salesforce data just as you would a a linked SQL Server instance.

Configure the DSN

If you have not already done so, specify connection properties in a DSN (data source name). You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.

Configure the TDS Daemon

The TDS Remoting feature of the ODBC driver enables you to create a linked server for Salesforce. The ODBC driver runs a daemon as a service that listens for TDS requests from clients. The daemon can be configured in a configuration settings file and through the CLI (command-line interface). Follow the steps below to use the configuration settings file to configure the DSN, SSL, access control, and other settings:

  1. Open the CData.ODBC.Salesforce.Remoting.ini file, located in the remoting subfolder in the installation directory.
  2. In the tdsd section, configure the settings for the TDS server:

    [tdsd]
    port = 1434
    maxc = 20
    session-timeout = 20
    logfile = SalesforceRemotingLog.txt
    verbosity = 2
    ssl-cert = "CData.ODBC.Salesforce.Remoting.pfx"
    ssl-subject = "*"
    ssl-password = "test"

    Note: By default, the daemon runs on port 1433, the default SQL Server port. If you already have SQL Server running on port 1433, change the default value for the port.

  3. In the databases section, define the catalog name and set it to the DSN:

    [databases]
    ;The database settings, default to installed system DSN name, odbc connection string is acceptable also.
    CDataSalesforce = DSN=CData Salesforce Source
  4. In the acl section, add users that are allowed to connect to the linked server:

    [acl]
    CDataSalesforce = admin
  5. In the users section, define passwords for authorized users. Below are the default values:

    [users]
    ;Passwords
    admin = test
  6. Start the service the daemon is running under. You can start the service from the Services Snap-In: Click Start -> Run and enter services.msc. Right-click the CData Salesforce TDS Remoting service and click Start.

Create a Linked Server for Salesforce Data

After you have configured and started the daemon, create the linked server and connect. You can use the UI in SQL Server Management Studio or call stored procedures.

Create a Linked Server from the UI

Follow the steps below to create a linked server from the Object Explorer.

  1. Open SQL Server Management Studio and connect to an instance of SQL Server.
  2. In the Object Explorer, expand the node for the SQL Server database. In the Server Objects node, right-click Linked Servers and click New Linked Server. The New Linked Server dialog is displayed.
  3. In the General section, click the Other Data Source option and enter the following information after naming the linked server:
    • Provider: Select SQL Server Native Client 10.0 in the menu.
    • Product Name: Enter a name for the data source.
    • Data Source: Enter the host and port the daemon is running on.
    • Provider String: Enter the following connection string:
      Network Library=DBMSSOCN;
    • Catalog: Enter the catalog you defined in the databases section of the configuration settings file.
  4. In the Security section, select the Be Made Using this Security Context option and enter the username and password of a user you authorized in the acl section of the configuration settings file.

Create a Linked Server Programmatically

In addition to using the SQL Server Management Studio UI to create a linked server, you can use stored procedures:

  1. Call sp_addlinkedserver to create the linked server:

    EXEC sp_addlinkedserver @server='Salesforce',
    @srvproduct='CData.Salesforce.ODBC.Driver',
    @provider='SQLNCLI10',
    @datasrc='localhost,1434',
    @provstr='Network Library=DBMSSOCN;',
    @catalog='CDataSalesforce';
    GO
  2. Call sp_droplinkedsvrlogin to remove the default mappings created by sp_addlinkedserver. By default, sp_addlinkedserver maps all local logins to the linked server.

    EXEC sp_droplinkedsrvlogin @rmtsrvname='Salesforce',
    @locallogin=NULL;
    GO
  3. Call the sp_addlinkedsrvlogin stored procedure to allow SQL Server users to connect with the credentials of an authorized user of the daemon. Note that the credentials you use to connect to the daemon must exist in the daemon's configuration settings file.

    EXEC sp_addlinkedsrvlogin @rmtsrvname='Salesforce',
    @rmtuser='admin',
    @rmtpassword='test',
    @useself='FALSE',
    @locallogin='YOUR-DOMAIN\your-user';
    GO

Connect from SQL Server Management Studio

SQL Server Management Studio uses the SQL Server Client OLE DB provider which requires the ODBC driver to be used inprocess. You must enable the 'Allow inprocess' option for the SQL Server Client Provider in Management Studio to query the linked server from SQL Server Management Studio. To do this, open the properties for the provider you are using under Server Objects -> Linked Servers -> Providers. Check the 'allow inprocess' option and save the changes.

Execute Queries

You can now execute queries to the Salesforce linked server from any tool that can connect to SQL Server. Set the table name accordingly:

SELECT * FROM [linked server name].[CDataSalesforce].[Salesforce].[Account]

Posted On Tuesday, June 30, 2015 2:15 AM | Comments (0) | Filed Under [ salesforce cdata ODBC Linked Server ]

Friday, June 26, 2015

How to Access Data from a SharePoint List Based on a Custom View

The CData ADO.NET Providers for SharePoint you to integrate live SharePoint data with other applications. For example, Visual Studio provides built-in support for ADO.NET data sources. This article shows how to use Server Explorer in Visual Studio to access the data screened by a SharePoint custom view.

Implementing Access Control with a Database Query

The CData ADO.NET provider's data model exposes each SharePoint list as a separate table; all custom views are available by querying the Views table. You can implement the settings for the custom view that you defined in SharePoint by selecting data with the ID of the custom view:

  1. If you have not already done so, establish a connection to SharePoint: In Server Explorer, right-click the Data Sources node and click Add Connection.

    See the "Getting Started" guide chapter in the help documentation for a guide to the required connection properties and how to set them in Server Explorer.

  2. Retrieve the unique identifier for the custom view: Query the Views table and specify the custom list for the view. For example, the query below retrieves all custom views for the custom list MyCustomList:
    SELECT * FROM Views WHERE List='MyCustomList'
  3. Query the custom list table to retrieve the custom view. Specify the ViewId in the WHERE clause. For example, to retrieve a custom view from MyCustomList, use the following statement:
    SELECT * FROM MyCustomList WHERE ViewId='your-ViewId'

Posted On Friday, June 26, 2015 3:21 AM | Comments (0) | Filed Under [ ado.net sharepoint cdata ]

Powered by: