I am getting below error when I tried to create a LinkServer in SQL management Studio 2012. I was able to create and tested the connection with the Teradata ODBC system DSN. I can use Teradata SQL assistance to access Teradata from the server. Any suggestion?
TITLE: Microsoft SQL Server Management Studio
The linked server has been created but failed a connection test. Do you want to keep the linked server?
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TD".
OLE DB provider "MSDASQL" for linked server "TD" returned message "The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80".
OLE DB provider "MSDASQL" for linked server "TD" returned message "". (Microsoft SQL Server, Error: 7303)
My SQL Server knowledge is limited so I hope this procedure may help you.
My SQL Server 2014 (possible the same for SQL 2012) is a 32-bit version, so the ODBC Driver
DSN that I used is from 32-bit ODBC Administrator. It's the same DSN name I used with 32-bit SQLA.
I created my Linked Server and named it "sql_td". Under Server Type is clicked Other Data Source.
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: Teradata
Data Source: td_1510 (DSN Name from the 32-bit ODBC Administrator I created for SQLA)
Provider String & Location I left them Blanks.
Click "Security" on the left pane to bring up the next screen.
I only selected the button "Be made using this Security Context"
and entered Teradata username under:
Remote login = systemfe
With Password = <systemfe password>
I did this test twice because it initially failed with a similar error you reported when I entered
"sa" and checked the "Impersonate". I deleted the newly created Linked Server and start
a new none. This time, the Test Connection works.
The Linked Server may not work if your SQL Server is 64-bit version and the
ODBC Driver is 32-bit due to imcompatibility.
I haven't tested the 64-bit SQL Server 2014 with 64-bit ODBC Driver as I don't
have a 64-bit SQL Server.