CODEWITHSUNDEEP
sqlssissql-server-2008-r2
Divi
Divi

Reputation: 7691

SQL does not read more than 65536 characters from a text column

I have a text field in a table with other spatial types. As part of a SSIS package, I am using this text field to run an update (as a SQL task) on the same table and update the geometry and geography columns. I was able to successfully run the following query yesterday 

UPDATE Location 
SET [Geometry] = geometry::STGeomFromText(ShapeText, 4326).MakeValid()
FROM Location 
WHERE ShapeText IS NOT NULL

But, today it has been throwing the following exception:

Msg 6522, Level 16, State 1, Line 10 A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24141: A number is expected at position 65536 of the input.

When I looked at the shape, its definitely a valid shape. So the problem is not in the shape but that SQL is only reading upto 65536 characters from the column. And it works when the shapetext is less than 65536 characters.

It would be great if someone coud help me with this and let me know how to read more than 65536 characters from a text field.

Upvotes: 0

Views: 3756

Answers (1)

Divi
Divi

Reputation: 7691

So, finally I found the problem and the solution for it.Even though the problem seems a bit too discrete, I hope it helps someone else.

PROBLEM: I started searching for this 65536 character error message and it led me to blogs that mentioned the SQL memory issues. I then checked my SQL logs and it did have error messages reporting:

Failed to reserve contiguous memory of Size= 65536

The confusion was that it was working one day and then the next day, it started throwing me the error messages, so I definitely had a memory eating operation somewhere. On careful examination, I found that there was a derived column on my SSIS package that was the root of all memory issues. The derived column was checking the length of a text field and setting it to null if the length was zero.

SOLUTION: I removed the derived column operation and replaced it with a SQL task instead and its working beautifully once again.

Upvotes: 1

Related Questions