Development

Creating a Linked Server Between SQL Server

6

A topic on Vertica forums discussed that it’s not possible to create a linked server from SQL Server to HP’s Vertica. However, I was able to put together a connection string that worked with Vertica 6.1.2.

I first found a connection string. The difficulty I had was with the driver name in the provider connection string. Originally, I was attempting:

When attempting to query Vertica, the following error would be thrown:

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

All that was needed in the driver value was Vertica:

My final linked server statement looked like:

To query the linked server, the qualifier looks like:

The installation of Vertica ODBC drivers may not be required, however I did download and install the Vertica client package (registration may be required). This package includes ODBC and JDBC drivers.

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

6 Comments

  1. Alex June 17, 2015 at 8:47 AM -  Reply

    Hi, thank for your reply at vertica-forums.
    I installed the Vertical client package, which created the ODBC driver, then created the linked server as per above. Now I’m getting the following error:
    OLE DB provider “MSDASQL” for linked server “Vertica” returned message “FATAL 5273: Unsupported frontend protocol 3.5: server supports 3.0 to 3.4”.
    Msg 7303, Level 16, State 1, Line 72
    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “Vertica”

    I’m running SQL Server 2014 and Vertica v7.0.0-1

    • Norbert Krupa June 17, 2015 at 9:01 AM -  Reply

      Alex, what driver version did you download and install? The Vertica 7.1 drivers are not backwards compatible.

      • Alex June 18, 2015 at 10:10 AM -  Reply

        Yep, installed 7.01.01.00 – that’s the only one they had available on the site. The v6 version was supposed to be made available again in Feb 2015, but is still nowhere to be found. Do you by any chance happen to have it saved somewhere?

        • Norbert Krupa June 18, 2015 at 10:12 AM -  Reply

          Reach out to Vertica support and they’ll be able to point to you the drivers.

  2. Alex June 20, 2015 at 4:50 PM -  Reply

    Hey, From your experience: how long does it usually take? Wrote to Support on 6/18 in the morning – still haven’t heard back… I also sent you invitation on LinkedIn, I think it would be a good idea if we connected – I might have business for you. Also, if you could point me to someone you know with good expertise in both Vertica and SQL Server, and resides in the NY area – that would also be great.
    Thank you,
    Alex

  3. Carvilson Vilanio January 9, 2017 at 8:29 PM -  Reply

    Hi Sir I tried your code
    EXEC dbo.sp_addlinkedserver
    @server = N’VERTICA’,
    @srvproduct=N”,
    — SQL Server driver
    @provider=N’MSDASQL’,
    — Connection string
    @provstr=N’Driver=Vertica;
    Servername=server;
    Port=22;
    Database=pm_2g;
    Username=dbadmin;
    Password=password’

    but im still getting this error:

    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “VERTICA”.
    OLE DB provider “MSDASQL” for linked server “VERTICA” returned message “[Microsoft][ODBC Driver Manager] Data

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: