create linked server to vertica

Question

I'm attempting to create a linked server from SQL Server to Vertica to pull over some data. I've attempted using the following ODBC found here.

The code I'm currently trying is:

EXEC dbo.sp_addlinkedserver 
@server = N'VERTICA', 
@srvproduct=N'', 
-- SQL Server driver
@provider=N'MSDASQL',
-- Connection string
@provstr=N'Driver=Vertica ODBC Driver 4.1;
 Servername=Vertica;
 Port=5433;
 Database=Vertica;
 UserName=user;
 Password=pass'

The error message I get is:

OLE DB provider "MSDASQL" for linked server "VERTICA" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

• billinkc: Any chance you've installed a 32 bit driver and SQL Server expects a 64 bit?
• Kermit: @billinkc Verified that the 64-bit ODBC drivers are installed.

Answer 1

The driver in the connection string needs to be Vertica, not Vertica ODBC Driver 4.1.

EXEC dbo.sp_addlinkedserver 
 @server = N'VERTICA', 
 @srvproduct=N'', 
 -- SQL Server driver
 @provider=N'MSDASQL',
 -- Connection string
 @provstr=N'Driver=Vertica;
 Servername=ServerName;
 Port=5433;
 Database=DatabaseName;
 UserName=user;
 Password=pass'