Post reply

Linked server - mySql

  • February 26, 2018 at 3:21 am

    #410268

    Hi,

    we have just set up a linked server connnecting to a mySql (maria) database and the connection tests fine but when trying to run any query or even just expand the table list in managment studio we get the following error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Now the links are useless and take us to the default microsoft landing page and after some googling everyone just says check the lenghts of fields match but we cant even see what table or field its failing on. is there any way to get more detail or if anyone has any ideas on how to fix this it would be very helpful. 

    I would presume that a linked db would create a temporary table (but i could be wrong) but i have no control over the sizes of the fields it creates.

    thanks in advance for any help

    Ste

  • February 26, 2018 at 10:04 am

    #1981138

    What provider are you using, ex. OLEDB, ODBC...

  • February 27, 2018 at 1:18 am

    #1981241

    ste.cox - Monday, February 26, 2018 3:21 AM

    Hi,

    we have just set up a linked server connnecting to a mySql (maria) database and the connection tests fine but when trying to run any query or even just expand the table list in managment studio we get the following error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Now the links are useless and take us to the default microsoft landing page and after some googling everyone just says check the lenghts of fields match but we cant even see what table or field its failing on. is there any way to get more detail or if anyone has any ideas on how to fix this it would be very helpful. 

    I would presume that a linked db would create a temporary table (but i could be wrong) but i have no control over the sizes of the fields it creates.

    thanks in advance for any help

    Ste

    Why can't you go thru the same post ? It has some suggestions.

    https://www.sqlservercentral.com/Forums/Topic648963-338-1.aspx

  • February 27, 2018 at 1:43 am

    #1981244

    Joe Torre - Monday, February 26, 2018 10:04 AM

    What provider are you using, ex. OLEDB, ODBC...

    thanks for the reply. we have tried the mysql ODBC 5.3 drivers, both unicode and ansi. 

    its actually a maria db that we are trying to link to so have also tried the mariadb odbc 3.0 driver as well with no luck.

    as for the other reply and the link to another question. as far as i know i dont have control over the table it tries to create so i cant check column sizes. (i could be wrong about that though but from what i can see i cant)

    thanks

  • February 27, 2018 at 11:45 am

    #1981336

    You can create the table in SQL Server before the import matching the data types and lengths from the source. The import wizard reads the first few hundred rows to determine the data types so it can underestimate the length of columns if the data it scans doesn't have the longest data for a column.

  • February 28, 2018 at 1:49 am

    #1981418

    Joe Torre - Tuesday, February 27, 2018 11:45 AM

    You can create the table in SQL Server before the import matching the data types and lengths from the source. The import wizard reads the first few hundred rows to determine the data types so it can underestimate the length of columns if the data it scans doesn't have the longest data for a column.

    Thanks, is there anyway to order the import so it gets the biggest first or at least see in a log which table its failing on, there are 100+ tables in there so wouldnt really want to do them all manually

    thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply