Sql-server – create linked server to vertica

sql-server-2012vertica

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".

Best Answer

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'
Related Question