OPENQUERY returning funny data

  • I'm using the OpenQuery function to get data from a linked MySQL database and insert it into a SQL Server 2005 database. The problem I'm having is that it's inserting an x for empty strings that are stored in MySQL as text fields. It doesn't happen on any other data type. Does anyone know what is causing this and how I can fix it?

    Thanks!


    Wendy Schuman

  • Post your query please.

  • I'm actually doing an exec(@sqlstatement) to do the Insert Into/select * from Openquery, but it's also happening when I do a very simple select query. The two MySQL text fields are both returning an x and it should be an empty string.

    SELECT * FROM OPENQUERY(LinkedServer, 'SELECT fiel1, field2, field3, field4,field5 FROM tablename WHERE field 1 = 10' )


    Wendy Schuman

  • If there is supposed to be a space in the fieldname, use

     

    SELECT * FROM OPENQUERY(LinkedServer, 'SELECT fiel1, field2, field3, field4,field5 FROM tablename WHERE [field 1] = 10' )

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Sorry, I rewrote the query so as not to use the real column names. So there is not a space in the name.

    I tried changing some of the settings on the MySQL ODBC driver, but that didn't make a difference so I'm doing a work around. I created a table in SQL Server with two fields that identify the records from MySQL that are really supposed to have an 'x' as the value. I executed a query against the MySQL database to find those records and inserted them into the new table in SQL Server. Now when I execute the Openquery function, I'm inserting the results into a temp table. Once they are in the temp table, I'm checking the identity field from the temp table against the new table in SQL Server, if they do not exist in that table, when I do the insert into the other table, I'm changing it to an empty string. If they do exist, I leave the x.


    Wendy Schuman

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

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