ODBC Driver for Salesforce

Build 24.0.9060

Executing Queries

This section describes how to execute queries to the Salesforce linked server from any tool that can connect to SQL Server.

When executing queries from a linked server, use a two-part name, in the format:

[<Linked Server Name>].[<DSN Name>].[<Schema Name>].[<Table Name>] 
where
  • Linked Server Name is the name of the linked server that you created. This is normally the service name.
  • DSN Name is the name of the data source.
  • Schema Name is the name of the system schema or user-defined schema.
  • Table Name is the name of the table you are copying data from.

SELECT Statements

To create new records:
SELECT * FROM [Linked Server Name].[CData Salesforce Sys].[Salesforce].[Account] WHERE Industry = 'Floppy Disks'
Note: If the data source supports case-sensitive identifiers, it is not possible to query tables of the same name that only vary by casing (ex: Account and ACCOUNT in the same schema). This is due to the fact that SQL Server treats identifiers as case-insensitive, so multiple tables of the same name in a single schema are incompatible with the SQL Server protocols.

INSERT, UPDATE, and DELETE Statements

To insert, update, or delete a single row of data against a writeable database, use the four-part name. For example:

INSERT INTO [Linked Server Name].[CData Salesforce Sys].[Salesforce].[Account] (Industry) VALUES ('Floppy Disks');

Bulk Operations

To perform bulk operations against data sources that permit writes, use syntax similar to the following:

To run a bulk INSERT using a SELECT query in place of a VALUES list:

INSERT INTO [Linked Server Name].[CData Salesforce Sys].[Salesforce].[Account] ([Industry])
SELECT [Industry] from [CData].[dbo].[Local_Account]
To run a bulk UPDATE using a JOIN with a local table:
UPDATE [Linked Server Name].[CData Salesforce Sys].[Salesforce].[Account] SET [Account].[Industry] = CData.dbo.[Local_Account].[Local_Industry]

FROM CData.dbo.[Local_Account] INNER JOIN [Linked Server Name].[CData Salesforce Sys].[Salesforce].[Account] ON CData.dbo.[Local_Account].[Id] = [Account].[Id]
To run a bulk DELETE using the IN filter with sub-SELECT query.
DELETE FROM [Linked Server Name].[CData Salesforce Sys].[Salesforce].[Account] WHERE [Id] IN (SELECT [Id] FROM CData.dbo.Local_Account)

Execute Stored Procedures

Before you can execute stored procedures, you must first enable RPC and RPC Out for the linked server. To do this, right-click the linked server, navigate to Properties > Server Options , and set RPC and RPC Out to True.

After RPC and RPC Out are enabled, use these commands to run the stored procedure:

DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC CreateJob Action=''Insert''';
EXEC (@RunStoredProcSQL) AT [Linked Server Name];

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060