"MSDASQL" for linked server "xxx" reported an error. The provider reported an unexpected catastrophic failure. over linked server - Solution

• niquem1974

  Right there with Babe

  Points: 773

  More actions

  September 10, 2014 at 10:24 am

  #290511

  Hi Guys and girls

  I've just spent the last few weeks banging my head against a brick wal over this so I thought I'd post up my scenario.....just incase anyone else is having the same issues

  Scenario:

  SSMS 2012 SP2 (My dev/staging environment)

  MySQL (live Db server)

  I have linked servers in SSMS using the Oracle MyODBC connector 5.3.4

  Essentially I identify issues on SSMS and write any corrections to a log table. Any that require elevation to other systems are sent to a 'mirror' table on our live MySQL server

  I could select using a 4 part name fine

  SELECT TOP 10 *

  FROM [MySQL]...MyTbl

  I could push fine into the same table fine

  INSERT INTO [MySQL]...MyTbl

  (MyCol1, MyCol2, MyCol3)

  SELECT Col1, Col2, Col3

  FROM SSMSTbl

  until one day when it all stopped working and all I got was

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "MSDASQL" for linked server "MySQL" reported an error. The provider reported an unexpected catastrophic failure.

  Msg 7343, Level 16, State 2, Line 1

  The OLE DB provider "MSDASQL" for linked server "MySQL" could not INSERT INTO table "[MySQL]...[MyTbl]". Unknown provider error.

  This would also hang any further connections to the server........meaning that I'd need to restart the SQLServer service to kill the processes (KILL 123 would change the status to KILLED/ROLLBACK but not be able to cease it)

  :crying:

  I've been trawling sites for the last couple of weeks trying to get a solution.....tried many things, both the bloomin' obvious to the 'really dont know what I'm doing here but just following instructions hoping that I dont properly break something'....but to no avail

  Eventually by the process of elimination, I found that it came down to the casting on 2 fields in my MySQL table and their association

  SSMS = nvarchar(max)

  MySQL = Text

  I found I could push the other columns in if I ignored pushing to the MySQL text cols

  I also found that if I truncated the source data and amended the MySQL to varchar (500) this would also work

  :crazy:

  After a while longer I managed to strike gold

  I found that if I utilised the Openquery solution it would work fine (with the castings as intended)

  so to recap

  This broke stuff

  INSERT INTO [MySQL]...MyTbl

  (MyCol1, MyCol2, MyCol3)

  SELECT Col1, Col2, Col3

  FROM SSMSTbl

  whereas this worked

  INSERT INTO OPENQUERY ([MySQL], 'SELECT MyCol1, MyCol2, MyCol3 FROM MyTbl')

  SELECT Col1, Col2, Col3

  FROM SSMSTbl

  I hope this might help someone in future and save them from swearing and crying as much as Ive done

• mpaul0961

  Grasshopper

  Points: 19

  More actions

  September 12, 2018 at 11:31 am

  #2005473

  I know this is an old post but I ran into this issue and I figured out out in my case.

  I was connecting to a MySQL server through a linked server in SQL (v 2017). I tried:

  select * from server.table

  and I got the catastrophic error.

  Then I tried:

  select column1, column2, etc from server.table

  and I got the catastrophic error. I then tried using openquery and I got the same error. I couldn't find any solutions online so I went back to my query and I started removing fields from my query. Come to find out, that when I removed all the date fields from my query, I was able to run it without error. I then added the following to my query:

  select column1, column2, concat(date1, "") as column3 from server.table

  This worked. I checked the schema in MySQL and the date fields were set as timestamp. It appears they were not compatible and the SQL driver did not know how to convert them. Good luck.

  mp