Salesforce linked servers and sp_columns_ex

SQL Server provides the stored procedure sp_columns_ex to provide column information in the tables made available through the linked server mechanism. In theory, you can use sp_columns_ex with a linked server that uses ODBC to access a third-party data store (for example, Salesforce through Easysoft's Salesforce ODBC driver). In practice, you cannot: sp_columns_ex returns an empty result set when used with an ODBC linked server and although tables made available by the linked server are visible in SQL Server Management Studio, columns are not.

According to Microsoft:

"sp_columns_ex returns an empty result set if the OLE DB provider of the specified linked server does not support the COLUMNS rowset of theIDBSchemaRowset interface."

Although there is no definitive statement as to whether the Microsoft OLE DB Provider for ODBC drivers supports this particular interface, extensive testing both with Easysoft ODBC drivers and drivers from other vendors suggest it does not.

The issue therefore lies with the Microsoft's OLE DB interface rather than with a particular ODBC driver or linked server back end.

You can, however, create an empty SQL Server table based on the columns in a Salesforce table. You can use this empty table to find out how Salesforce column metadata translates to SQL Server column metadata. This example is for the Account table and assumes the linked server is named SALESFORCE:

select * into dbo.NewTable from openquery(SALESFORCE,'select * from Account')
where 1 = 0