Here is another tip from our tame Dex developer Jon, which I have written up as an article for us mere mortals. The below article should help you if you need to bring back an image from the OLE notes folder in Dynamics GP.
To start with you need to know how OLE notes get their file names. Well the file name is the hexadecimal version of the note index held in the Dynamics GP notes master table (SY03900).
Therefore to reference the file you are going to need to convert decimals to hexidecimals. The below function will allow you to do this.
CREATE FUNCTION udfConvertBase10NumberToAnyBase
( @iNumber INT,
@bitUppercaseOnly BIT = 0,
@bitNoNumbers BIT = 0) --1 to use only alphas
DECLARE @vUppers VARCHAR(26)
DECLARE @vLowers VARCHAR(26)
DECLARE @vNumbers VARCHAR(10)
SELECT @vUppers = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @vLowers = 'abcdefghijklmnopqrstuvwxyz'
SELECT @vNumbers= '0123456789'
DECLARE @vCharactersInBase VARCHAR(62) --Max possible of 10 digits, 26 upper, 26 lower
SELECT @vCharactersInBase = @vUppers --all options allow this
IF @bitNoNumbers = 0
SELECT @vCharactersInBase = @vNumbers + @vCharactersInBase
IF @bitUppercaseOnly = 0
SELECT @vCharactersInBase = @vCharactersInBase + @vLowers
--If caller requests a "base" size > number of characters in conversion-characters-set, return error
IF LEN(@vCharactersInBase) < @iNewBase
RETURN 'udfConvertBase10NumberToAnyBase - ERROR: Requested Base-size greater than available characters in @vCharactersInBase'
DECLARE @vNewNumber VARCHAR(120)
SELECT @vNewNumber = ''
-- Algorithm for generating equivalant number in the new "base":
-- 1) The orignial (base-10) number is continually divided by the new base until the product
-- of the old number divided by the base is zero (meaning the number is finally smaller than the new base).
-- 2) On each cycle (loop iteration), the remainder is added to the number, which is each digit of the new base.
WHILE @iNumber <> 0
SELECT @vNewNumber = SUBSTRING(@vCharactersInBase, (@iNumber % @iNewBase) + 1, 1) + @vNewNumber
SELECT @iNumber = @iNumber / @iNewBase
So now we have a function that can convert the decimal to hexidecimal to obtain the file name.
To be able to use this we then need to include this function in a script
Select noteindx, right(‘00000000’ + dbo.udfConvertBase10NumberToAnyBase(noteindx, 16, 0,0), 8) as filename from SY03900
You can now bring back the OLE note file.
MSDN Blogs - MSDN Blogs
4 minutes ago