Post reply

Problems using OPENQUERY() with MySQL

  • July 23, 2009 at 8:03 pm

    #137017

    I'm can't seem to get this particular query to run. I've tried casting the username as char, varchar and so on... I get the same error every time.

    DECLARE @table varchar(100), @sql varchar(2000)

    SET @table = 'sometable'+left(convert(varchar,dateadd(dd,-2,getdate()),112),6)

    SET @sql = '

    SELECT rid, username FROM OPENQUERY(linked_server,''SELECT rid, username FROM database.'+@table+'

    WHERE rid <> '''''''';'')

    '

    EXEC (@sql)

    Here's the error...

    OLE DB provider 'MSDASQL' for linked server '' returned data that does not match expected data length for column '[MSDASQL].username'. The (maximum) expected data length is 170, while the returned data length is 22.

    The datatype for the column it's complaining about is "username char(85)."

    Any ideas?

    Thanks!

  • July 23, 2009 at 8:17 pm

    #1029422

    [font="Courier New"]DECLARE @table varchar(100), @sql varchar(2000)

    SET @table = 'sometable'+left(convert(varchar,dateadd(dd,-2,getdate()),112),6)

    SET @sql = '

    SELECT rid, username FROM OPENQUERY(linked_server,''SELECT rid, username FROM database.[highlight]dbo.[/highlight]'+@table+'

    WHERE rid '''''''';'')

    '

    EXEC (@sql)[/font]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

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