I have created a linked server in MS SQL 2017 that selects a file on an AS400. When I test the connection, it comes back and tells me that test connection is successful. In linked server I am using a user and password to the AS400 that has all object authority. When I execute the query, I get the field names only in the results pane and the following message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "METRO_AS1" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "METRO_AS1".
The query look like this:
declare @sql_str varchar (1000)
SET @sql_str = 'SELECT * FROM WM01FA.WHLOCX '
SET @sql_str = N'select * from OPENQUERY(METRO_AS1, ''' + REPLACE(@sql_str, '''', '''''') + ''')'