XML in a text variable and dynamic query for MySQL

Question

Post reply

XML in a text variable and dynamic query for MySQL

lucazav

SSC Eights!

Points: 981
More actions
September 9, 2009 at 2:47 pm

#204506

Hi all!
I have a MySQL linked server (MYSQLLNKD) in my SQL Server 2005. I need to get the xml output of a SQL Server view, convert it in a text variable (because it is more than 8000 chars), and than execute a procedure in MySQL having it as parameter.
In order to execute a procedure in MySQL, I have to launch this query in SQL Server:
EXECUTE('CALL mysql_proc(''' + @xmlText + ''')') AT MYSQLLNKD
but I know I can't concatenate text and strings. I also can't convert text to varchar, because I have more than 8000 chars...
So, how can I solve this issue??
Thanks a lot in advantage for your help!

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

AFAIK you can use VARCHAR(MAX).

Straight from BOL:

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes.

I just tried the following (in SS2K5) and succeeded:

declare @xml xml

select @xml = '

50

'

SELEC 'this is a test ' + cast(@xml AS varchar(max)) + ' to check xml concatenation'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

lucazav SSC Eights! Points: 981 More actions · Answer 2

Thank you for you answer Lutz! 🙂

I tried this solution and it works right, but I often have to import xml text that has more than 8000 chars! Here I find the issue :crying:

Matthew Fellows SSC Enthusiast Points: 129 More actions · Answer 3

Can you not put the content of the MySQL stproc into an MSSQL stproc and do the required actions on MSSQL to you MySQL server? Sorry if this is not possible I've never tried linking an MSSQL server and MySQL so I'm not sure of the capabilities.

lucazav SSC Eights! Points: 981 More actions · Answer 4

Thank you Matthew 🙂

The xml text has a lot of rows I have to import in a MySQL table every night. A solution can be that I call an INSERT in the linked MySQL for each row I have in MSSQL. But I thought a xml solution was more efficient: I'd pass a xml text to my linked MySQL procedure, and this procedure do a bulk insert in the table... The only problem is the 8000+ chars text!! :pinch:

I'm asking here for a possible solution because I think this issue is quite common: one can't hope to work with xml texts less than 8000 chars... I know there is the XML data type in SQL Server, but this isn't a standard for data integration between different systems.

LutzM SSC Guru Points: 107049 More actions · Answer 5

I'm just going to repeat what I posted before, just a little more focussed:

Straight from BOL again:

varchar [ ( n | max) ]
max indicates that the maximum storage size is 2^31-1 bytes.

What exactly is the reason for you to insist in the 8k limit of a varchar value?

Is the problem -from your point of view- on the SQL Server side or is MySQL refusing to accept larger values?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

lucazav SSC Eights! Points: 981 More actions · Answer 6

Thanks Lutz, you are right. It was a my misunderstanding, VARCHAR(MAX) can hold up to 2 GB! :w00t:

I'll try it tomorrow and I'll let you know.

Thank you again for your specification!

lucazav SSC Eights! Points: 981 More actions · Answer 7

Hi Lutz, it was a MySQL problem: I used the Text data type instead of LongText!!

Thank you!