December 8, 2006 at 1:58 am
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
Thanks!
December 11, 2006 at 8:00 am
This was removed by the editor as SPAM
December 11, 2006 at 11:19 am
May be your function is not support for multi row updates...
Because cursor updates one row at time and with w
MohammedU
Microsoft SQL Server MVP
December 14, 2006 at 9:26 am
Don't really know why you're getting the error, but it looks like the problem comes into play when you try to go against all 1.5 million rows in the table with a wide-open WHERE-clause. If that's the case, you might try breaking down your update up into more manageable "chunks"--i.e., something like:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId > 9900000 AND PersonId <= 10000000
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId > 10000000 AND PersonId <= 10100000
etc.
(presuming you have an index on "PersonId")
With a bit of programming you could write a proc to do the update for each "chunk" in succession, incrementing the counter by 1000000, or as appropriate. You could even print out a status line after each individual update.
By "breaking it down into chunks" you gain a degree of "recoverability", if something goes wrong--like if the tran log runs out of space or something. You might even be able to resume the operation where you left off, if you add a "@StartingPersonID" parameter to your stored procedure.
It's a bit of a compromise, but it should be faster than the CURSOR approach, which will only UPDATE a single row at a time.
- john
December 29, 2006 at 5:43 am
Thanks John,
That's the only solution I could think of too and it worked like a charm (except for being quite slow...). When getting back to work after the holidays I tried the query again, and guess what? It suddenly works (without executing it on chunks of data)! This is exactly what has happened before when receiving this error - it doesn't work for "all" the data, but after a while the error simply disappears.
The world of computers is a strange world...
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply