0

INSERT data From IBM i-series database (Created Linked server connection -SOFT) table into SNOWFLAKE database table (Created Linked server connection -SF_64)

Linked server connection(Created in One of the SQL server) SOFT - linked server connection with IBM Iseries Database SF_64 - linked server connection with IBM Iseries Database

INSERT INTO [SF_64].[PROD].[REPORTING].[DPMPPHY_SSIS_TESTING]
SELECT * FROM OPENQUERY(SOFT, 'SELECT * FROM ABC.DPMPPHY')

Getting below error wile executing above query in SQL server

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SF_64" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "SF_64". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jeyavel
  • 2,974
  • 10
  • 38
  • 48
  • 1
    Why? You are executing a query in one database engine that retrieves data from another (remote) engine in order to insert it into yet another (remote) engine. This makes little sense. I suggest you revisit the decisions that lead to this approach - linked servers are notoriously fragile, error-prone, and difficult to debug. – SMor Feb 13 '20 at 12:57
  • Already one of the process running and loading from ISeries DB to MS SQL server database. I want to use same process to load with cloud database(Snowflake). – Jeyavel Feb 13 '20 at 13:25

1 Answers1

0
INSERT INTO OPENQUERY(SF_64,'SELECT * FROM REPORTING.DPMPPHY_SSIS_TESTING') SELECT *FROM OPENQUERY(SOFT,'SELECT * FROM ABC.DPMPPHY')
Jeyavel
  • 2,974
  • 10
  • 38
  • 48