Child pages
  • Only integer values returned from SQL Server repository
Skip to end of metadata
Go to start of metadata

Problem

Data returned from a Microsoft SQL Server based repository only contains integer values.

For example:

Applies to:

  • Version 3.4.1 and older
  • Version 4.0.1 and older

Solution

Update the SiteMaestro Repository SQL Server Stored Functions.  This may be done while the database is online.  No restart is required.

Cut and paste the following query into SQL Server Management Studio as whatever SiteMaestro user you created while creating your repository schema:

ALTER function dpNumericValue(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
declare @fieldstart integer
declare @datastart integer
declare @fieldsep integer
declare @fieldval varchar(1023)
declare @fieldNumericVal float
declare @seps integer
/* Find item */
set @fieldstart = charindex(char(1) + @itemName + '=', @src)
if (@fieldstart = 0)
begin
    set @fieldstart = charindex(@itemName + '=', @src)
    set @seps = 1
end
else
begin
   set @seps = 2
end
if (@fieldstart != 0)
begin
    set @datastart = @fieldstart + len(@itemName) + @seps;
    set @fieldsep = charindex(char(1), @src, @datastart)
    set @fieldval = substring(@src, @datastart, @fieldsep - @datastart)
    if (isNumeric(@fieldval) = 1)
    begin
        set @fieldNumericVal = CAST(@fieldval as float)
    end
    else
    begin
        if (upper(@fieldval) in ('Y','YES','TRUE','UP','OPEN'))
           set @fieldNumericVal = 1
        else
           set @fieldNumericVal = 0
    end
    
    if ((@fieldNumericVal < 0) and (@ignoreNeg='Y')) return NULL
end
return @fieldNumericVal
end
go

ALTER function dpNumericValue0(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue1(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue2(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue3(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue4(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue5(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue6(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue7(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue8(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue9(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue10(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue11(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue12(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue13(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue14(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue15(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue16(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue17(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue18(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go
ALTER function dpNumericValue19(@src varchar(1023), @itemName varchar(60), @ignoreNeg char(1))
returns float
as
begin
return dbo.dpNumericValue(@src, @itemName, @ignoreNeg)
end
go

You can also just open this query as a file if you have installed v3.4.2 (or higher) as "mssql_upgrade342.sql", or v4.0.2 (or higher) as "mssql_upgrade402.sql". See the schema/mssql directory of your installation.