question

CharlesC avatar image
CharlesC asked

Problem with linked server - SQL 2017

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, '''', '''''') + ''')'

exec (@sql_str)

sqlerror
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

You need to use the DB2 connector not MSDASQL

This link will help with what you need to do.

https://docs.microsoft.com/en-us/host-integration-server/db2oledbv/microsoft-ole-db-provider-for-db2

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.