There is a lot of background to this, but the short story is that a consultant needed to update the SOPHDRFL field in the SOP10100 table. But it turns out that the SOPHDRFL field is binary--not your average field type.
I haven't done any research yet, so I'm not familiar with the values that GP stores in the binary fields, but there are a few:
So if you need to update one of these binary fields, how do you do it?
Well, I think I've had to work with SQL binary fields maybe once in my life, so I can never remember how to deal with them. If you do some searches, you'll likely find some elaborate and complex techniques that are needed for certain types of binary field updates.
But in this case, in SOP10100, the SOPHDRFL field has a default value of 0x00000000, but it gets updated during certain SOP processes. The consultant needed to set it back to the default value of zero so that a custom process could be implemented in SOP.
Poking around at some examples, I found a technique that appears to be pretty simple. Maybe this is obvious or elementary to the poor folks who work with binary fields regularly, but I thought it was pretty clever.
Rather than attempting to directly update the field value, you can just declare a binary variable and then use that in an update statement.
DECLARE @bin binary(4) = 0x00000000
UPDATE SOP10100 SET SOPHDRFL = @bin WHERE SOPNUMBE = 'STDINV6161'
It seems to work like a charm. Just make sure to check the binary field length that you are updating, as that appears to be important in the case of GP's binary fields. Granted, this is a very limited, simple situation, but it seems like it could come in handy for other cases.
And that is your very obscure SQL tip of the day.
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
No comments:
Post a Comment