June 4, 2007 at 11:00 am
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
June 4, 2007 at 11:04 am
Post your query please.
June 4, 2007 at 12:08 pm
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
June 5, 2007 at 4:56 am
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"
June 5, 2007 at 7:49 am
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