Child pages
  • Only integer values returned from SQL Server repository

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

Panel
bgColor#fff

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

Code Block
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.

...