Linked Server issue- query is inserting nulls but inserts text values

Question

Post reply

Linked Server issue- query is inserting nulls but inserts text values

GaffneyJ

SSC Eights!

Points: 856
More actions
December 11, 2015 at 9:04 am

#399455

Hello,
This is my first post I hope you can all help.
My insert query is having trouble inserting text into a Mysql server.
It inserts numeric values perfectly. however when I try to insert text it gives me NULL or an empty string(depending if I have allow nulls or not). This is with the customer feild inserting into the account2 feild.
Selecting the data from the local sql server is working.
Some more info.
account2 datatype is text and collation utf8_general_ci
customer datatype is text collation is SQL_Latin1_General_CP1_CI_AS.
I have tried changing the datatypes to pretty much everything. it may be a collation issue? would these different collations cause issues between SQLserver2008r2 and Mysql Server version: 5.0.96-log?
Insert into openquery ( GODADDY , 'SELECT `account2` , `YTD` , `LY` , `VARIANCE`
FROM `turnovertest`' )
Select [customer]
,YTD
,LY
,Variance
FROM [TestDB].[dbo].[TurnoverAll]
Here is the results of this insert inside the Mysql database.
-Thanks Jamie.

Viewing 9 posts - 1 through 8 (of 8 total)

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

GaffneyJ SSC Eights! Points: 856 More actions · Answer 1

GaffneyJ

SSC Eights!

Points: 856

February 7, 2016 at 4:42 am

#1857024

Bump, still no resolution to this

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

GaffneyJ (2/7/2016)
Bump, still no resolution to this

probably because you are using MySQL....this forum is MS SQL orientated.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Please provide the driver version being used for GODADDY Linked Server.

PS going from ascii-255 to UTF-8 may be the issue. Try casting customer to NTEXT or NVARCHAR(MAX) in your SELECT.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Orlando Colamatteo (2/7/2016)
PS going from ascii-255 to UTF-8 may be the issue. Try casting customer to NTEXT or NVARCHAR(MAX) in your SELECT.

I tend to agree with Orlando here. It sounds like the conversion is the problem here. I know you have to convert to an appropriate type when populating between SQL Server and Oracle, but I have no experience with MySql. In the documentation, you should have a table of data type conversion requirements with other database platforms. If conversion information isn't included in the documentation, then you'll probably get more responses by posting it on a MySql forums.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

GaffneyJ SSC Eights! Points: 856 More actions · Answer 5

Hello,

Thanks for the replys. Tried casting as NTEXT/NVARCHAR(MAX) with no luck. The collations probably are the issue, i'll see if I can find some way to get them to match. Here is a picture of my ODBC, It's connecting through an ODBC (MySQL Connector)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Check out my blog post on this topic.

I had the best luck with SSIS (Attempt 4):

https://thesqldev.wordpress.com/2014/07/02/importing-data-in-a-mysql-database-into-a-sql-server-database/

For your case, if you have to stay in T-SQL, see attempt 3. Items 7-10 detail the issue you are facing and how to work around it.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

GaffneyJ SSC Eights! Points: 856 More actions · Answer 7

Hello,

Thanks you for the replies. I managed to get this sorted with some hardcore googling! Hopefully someone with a similar issue with stubble upon this thread.

http://stackoverflow.com/questions/4381625/first-column-of-insert-into-over-linked-server-ms-sql-mysql-always-empty-stri

This user was having the same issue, I thought My issue was always with text but when I added a new column in both tables the query worked fine!

Insert into openquery ( GODADDY , 'SELECT `account2` , `account3` , `YTD` , `LY` , `VARIANCE`

FROM `turnovertest`' )

Select cast([customer] as NVARCHAR(MAX))

,[customer2]

,YTD

,LY

,cast(YTD-LY as NVARCHAR(MAX))

FROM [TestDB].[dbo].[TurnoverAll]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

Seems my reference was not applicable since it was for splitting up a string longer than the allowable 8000 SQL Server supports but still not unbounded like a MAX type.

Clearly you ran into a flaw in your Linked Server stack, maybe the driver misreports the MySQL metadata or the SQL Server Linked Server is interpreting the query incorrectly or some other strange integration issue in between.

This only strengthens my views against using Linked Servers for anything more than throwaway integration tasks, and only as a last resort at that.

Happy you got something working. Thanks for posting back.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato