SQLDataReader limitation?

G

guest2013-1

guest
Joined
Aug 22, 2003
Messages
19,800
Reaction score
13
Hi guys,

Wrote a little application that will do a data transformation from custom XML files into SQL Server or mySQL and the ability to transform already written data in SQL Server to mySQL as well.

I'm just playing around with code so don't suggest other programs I could use.

What I found though is SQLDataReader cuts off a very big string (varchar(MAX) in SQL 2005)

Is there a limitation on this? I read a few threads saying something about 8k size limit but wtf? I split up the data in 2 parts and combine them "on the other side" before pushing to mySQL but even that gets cut off (so essentially bigger than 16k which means I have to divide it into three)

Anyone experienced this or know a work around?
 
I hade the same problem.. but i remember you hade to change the way SQLDataReader "reads".

Let me fire up my vpc...

Edit:

oSQLConn.Open();

SqlDataReader dataReader = sqlCom.ExecuteReader();

while (dataReader.Read())
{
ViewXML = dataReader["XML"].ToString();
}

This is how I remember it works... not sure if there is a better way. But I think this is it.
 
Last edited:
Thats how I did it in the first place but it keeps on cutting off at 8000 characters. Even tried GetChar and read that into a strinbuilder/stringwriter combination with no luck. Even changing the database type of the field I read from varchar(max) to text didn't help. Next thing I'm going to do is write the output of the query to a file using bcp and then reading it line by line. Definately NOT my choice in doing it though.

Must admit this will keep me up all night trying to figure out WTF a datareader can't handle more than 8000 characters
 
SQLDataReader was obviously developed before the introduction of VARCHAR(MAX), prior to which 8000 was the limit.
 
true. But how did they expect us to work with text data type then? That existed way before .net
 
Do the job in AWK and BASH.
Works a heap load better than any .NET app.
 
AcidRaZor said:
true. But how did they expect us to work with text data type then? That existed way before .net
Click to expand...

True. An irritating (accepted, documented) bug that I always run into is on the Excel odbc driver.

If the first 100 odd rows in a column are blank and the column contains numeric data (even if formatted text), the rest of the values are assumed null, too.

Drives me insane.
 
Moederloos said:
Do the job in AWK and BASH.
Works a heap load better than any .NET app.
Click to expand...

Like I said, playing around with code etc. When/If this gets used by my team here we'd be able to convert any and most data into any database. I need to then hand off the code to some n00b that doesn't know what they're doing so they can then break the **** but yea... basically, M$ is my pimp
 
AcidRaZor said:
Like I said, playing around with code etc. When/If this gets used by my team here we'd be able to convert any and most data into any database. I need to then hand off the code to some n00b that doesn't know what they're doing so they can then break the **** but yea... basically, M$ is my pimp
Click to expand...

Which brings us full circle :p
M$-only devs ain't worth the money. Forcing a tool to do a job because it is the only tool the labourers understand is never going to end well.
 
Moederloos said:
Which brings us full circle :p
M$-only devs ain't worth the money. Forcing a tool to do a job because it is the only tool the labourers understand is never going to end well.
Click to expand...

lol, I know that, but tell that to the tool that wants it done... er I mean client :D
 
Ok - was a bit lazy to read the whole thread, but if I understand you correctly, you are returning an XML "string" from the query, right? If you want to select XML output from SQL Server 2005, you should rather use the "for xml" clause at the end (using "raw", "auto" or "explicit", based on your requirements) and then use SqlCommand.ExecuteXmlReader instead of SqlCommand.ExecuteReader. ExecuteXmlReader will return an XmlReader object, which you can use to instantiate a new XmlDocument object. This gets around the string length limitation of SQL Server 2005 and it's worked wonders for me so far. :)
 
Last edited:
FarligOpptreden said:
Ok - was a bit lazy to read the whole thread, but if I understand you correctly, you are returning an XML "string" from the query, right? If you want to select XML output from SQL Server 2005, you should rather use the "for xml" clause at the end (using "raw", "auto" or "explicit", based on your requirements) and then use SqlCommand.ExecuteXmlReader instead of SqlCommand.ExecuteReader. ExecuteXmlReader will return an XmlReader object, which you can use to instantiate a new XmlDocument object. This gets around the string length limitation of SQL Server 2005 and it's worked wonders for me so far. :)
Click to expand...

FYI, you can just convert it to XML data type ;)

But no it's not the problem, I already do that. Basically:

XML -> MSSQL (works)
XML -> MySQL (works)
MSSQL -> MySQL (doesn't work)

Currently, after the data is transformed from its XML file into a table into MSSQL, I'm building the set of data and returning it to the reader (not XML, but I might as well try it I guess), so that in turns gets pushed into mySQL so that you don't need to the original XML files to import the same data to mysql

Think I might be forced to pull the data out and build XML valid thing from MSSQL and then push that into mysql.

Just annoying as hell dealing with this stupid limitation. Even with TEXT data type M$ didn't consider you might want to read that **** out of a database...
 
Well, looks like I misunderstood you query then...

FYI, "FYI" has a whooooole different meaning in the company I work at... :D
 
FUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUCK

You forget ONE varchar declaration in your T-SQL and you wonder for a ****ing day why you're limited to only 8000 chars!

I'm going to go drown my stupidity in booze now. kthnxbye
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X