SQL Server Linked Server to Exasol MSDASQL Provider Errors

When I execute SQL queries on Exasol database from SQL Server using Linked Server with MSDASQL OLE DB provider, I experienced different types of SQL errors which I want to show developers how they can resolve them. SQL Server database programmers can refer to SQL tutorial Create Linked Server on SQL Server to Connect Exasol Database for creation of a linked server connection from SQL Server to Exasol database.

The provider did not give any information about the error.

If you experience an error message regarding to Linked Server connection saying that the OLE DB provider MSDASQL did not give any information about the error, the first thing you can check is the validity of the SQL Server Linked Server connection.

For example, if database developer executes following SQL query:


declare @sql nvarchar(max)

set @sql = N'select * from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;'

exec (@sql) AT [EXASOL]

Code

And experiences below error message:

Msg 7399, Level 16, State 1, Line 14
The OLE DB provider "MSDASQL" for linked server "EXASOL" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 14
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "EXASOL".

OLE DB provider MSDASQL for linked server reported an error

First, test the Linked Server connection on SQL Server Management Studio as follows:

test linked server to Exasol on SQL Server Management Studio

If the test is not successfull then first check whether the Linked Server definition has a problem or not. If SQL administrator is sure about the connection parameters, then you can assume that there is not a valid network connection between two data platforms.

OLE DB provider "STREAM" for linked server reported unexpected catastrophic failure

Although it is not a best practise for database developers, in general SQL programmers are used to query a database table for all of its fields. Even the database developer queries table data only for a limited number of rows, as seen below SQL query, some queries can cause errors between SQL Server and Exasol databases.


declare @sql nvarchar(max)

set @sql = N'select * from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY limit 1;'

exec (@sql) AT [EXASOL]

Code

It is possible that you experience below error message.
The OLE DB provider "STREAM" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.

l 16, State 1, Line 3
The OLE DB provider "STREAM" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "STREAM" for linked server "(null)".

Unfortunately the error is not giving a useful information.

The provider reported an unexpected catastrophic failure

If the count query runs successfully on remote data source, in this case, our target Exasol database, we can assume the error is related with columns of the database table.


declare @sql nvarchar(max)

set @sql = N'select count(*) from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;'

exec (@sql) AT [EXASOL]

Code

Exasol database table rows count query on SQL Server

On Exasol database, let's check the database table "EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY" and its all table fields at a glimpse.

On DbVisualizer Database Management Tool, if database developer connects to Exasol database and checks table column details, it can be seen that the REMARKS and SQL_TEXT columns sizes are 2000000 which is not a valid data type and valid size for SQL Server data platform.

Exasol database table column data types and metadata

Unfortunately, there is not a internal data type conversion between Exasol varchar(2000000) data type and SQL Server's varchar(max) data type.
In order to solve this SQL error, SQL conversion function CAST() can be used as follows:


declare @sql nvarchar(max)

set @sql = N'select

cast(REMARKS as nvarchar(8000)) as REMARKS,

cast(SQL_TEXT as varchar(1000)) as SQL_TEXT,

SESSION_ID, STMT_ID, COMMAND_NAME, COMMAND_CLASS,

PART_ID, PART_NAME, PART_INFO, OBJECT_SCHEMA,

OBJECT_NAME, OBJECT_ROWS, OUT_ROWS, DURATION, CPU,

TEMP_DB_RAM_PEAK, PERSISTENT_DB_RAM_PEAK,

HDD_READ, HDD_WRITE, NET

from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;'

exec (@sql) AT [EXASOL]

Code

As SQL programmers can guess, it may be boring and time consuming to test every table column for a possible data type conversion error, but mostly the case is related with string data types and geospatial data types.

using CAST function in SQL query on Exasol from SQL Server database

I think it is not a so catastrophic failure, is it?

OLE DB provider "STREAM" for linked server "(null)" returned message "Requested conversion is not supported

Although the error message has some differences from previous case the reason behind is the same.
If you execute your SQL Select query only for one of the fields with data type is not supported on SQL Server, in this case SQL_TEXT for example, the error message is different.


declare @sql nvarchar(max)

set @sql = N'select SQL_TEXT from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;'

exec (@sql) AT [EXASOL]

Code

Execution of above SQL script on remote Exasol Data Warehouse database using SQL Server Linked Server caused an error but the error message did not provide too detailed information.

OLE DB provider "STREAM" for linked server "(null)" returned message "Requested conversion is not supported.".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Requested conversion is not supported

Although the error message is not pointing to the exact point that caused the error, SQL database developer should suspect from a data type conversion error.

Error message "Requested conversion is not supported" can help the SQL programmers about data type mismatch between remote data source Exasol database and SQL Server from where the Linked Server remote SQL query is executed.

On Exasol database, if SQL developers check the database table "EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY" and table field name "SQL_TEXT" as we did in previous section, we can guess the reason of the error is the data type mismatch between Exasol data warehouse and SQL Server database.

Could not execute statement on remote server 'EXASOL'.

There are some other types of errors that might occur when a SQL Server database developer wants to query remote Exasol Linked Server database. There errors are caused by the inadvertency of the SQL programmer.

For example, there is not a data type varchar(max) in Exasol, so string CAST() function will fail on Exasol.


declare @sql nvarchar(max)

set @sql = N'

select

cast(SQL_TEXT as varchar(max)) as SQL_TEXT

from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;

'

exec (@sql) AT [EXASOL]

Code

Msg 7215, Level 17, State 1, Line 15
Could not execute statement on remote server 'EXASOL'.

The same error will occur if the SQL command string is build wrong. For example, a typo can cause the same error on remote data source Exasol database.


declare @sql nvarchar(max)

set @sql = N'select coun(*) from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;'

exec (@sql) AT [EXASOL]

Code

Msg 7215, Level 17, State 1, Line 13
Could not execute statement on remote server 'EXASOL'.

One last error that SQL Server developers can experience is the Remote procedure time out of 600 seconds exceeded errors, and the referenced SQL tutorial shows how to configure time our period on a SQL Server database instance.

I hope, this guide will help SQL Server database programmers to troubleshoot the errors they experience on their Linked Server connection queries with a Exasol database.