Function in SQL Query only yields 256 data points, other rows have NULL

• Chris Gehringer

  SSC Rookie

  Points: 35

  More actions

  March 22, 2012 at 9:56 pm

  #270541

  I have a function built by Chilkat software (that runs from a DLL) to HASH a value sent to the function.

  Using that function in some sql code such as the following, will return 258 rows, but only the first 256 have a value returned by the function -- the other two rows have NULL in the 'Crypt2' column.

  declare @counter int

  set @counter = 0

  while @counter < 258

  begin

  set @counter = @counter + 1

  print @counter

  select [dbo].[ChilkatSample2](@counter) as Crypt2

  end

  Furthermore, if I change the query to call the function twice -- select [dbo].[ChilkatSample2](@counter) as Crypt2, [dbo].[ChilkatSample2](@counter) as Crypt2again

  258 rows are returned but only the first 128 (half of 256) have values in the two columns from the SELECT statement.

  Any ideas why I can't get all the rows to have a value from the function?

  A copy of the function:

  USE [database_name_here]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER Function [dbo].[ChilkatSample2] (@s nVarchar(255))

  returns varchar(255)

  AS

  BEGIN

  DECLARE @hr int

  DECLARE @crypt int

  EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

  DECLARE @success int

  EXEC sp_OAMethod @crypt, 'UnlockComponent', @success OUT, '30-day trial'

  EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex';

  DECLARE @hash nvarchar(4000);

  -- Hash using SHA-256

  EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

  EXEC sp_OAMethod @crypt, 'HashStringENC', @hash OUT, @s-2

  return (Lower(@hash));

  END