Month: October 2017

select isnumeric('20,000.000') returns 1

select convert(float, '20,000.000') raises an error Error converting data type varchar to float.

You can’t write a isfloat() function, because try-catch is a side effect.

Here’s a snippet to walk thru your data to find duff values

declare c cursor for
select chillirecipedetailid, amount_or from tblAbRecipes
open c
declare @id int, @val nvarchar(100)
fetch next from c into @id, @val
while @@FETCH_STATUS=0
begin
 begin try
 declare @f float
 set @f = convert(float,@val)
 end try
 begin catch
 print 'duff value at ' + convert(nvarchar(20),@id) + ': ' + @val
 end catch;
 fetch next from c into @id, @val
end;
close c
deallocate c

select isnumeric('20,000.000')
select convert(float, '20,000.000')

exec sp_configure 'show advanced options',1
go
reconfigure;
go
exec sp_configure 'xp_cmdshell', 1;
go
reconfigure;
go

drop function sp_FileExists

if object_id('sp_FileExists') is null
begin
 declare @sql nvarchar(2000)
 set @sql='
 create function dbo.sp_FileExists(@fn nvarchar(1000)) returns bit as
 begin
 declare @result int
 exec master.dbo.xp_fileexist @fn, @result output
 if @result = 1 return 1
 return 0 
 end
 '
 exec @sql
end

select DocumentId, 'd:\MyStaffFile_Files\media\' + DocumentPath 
,case when DocumentPath is null then 'no path'
 when dbo.sp_FileExists('d:\MyStaffFile_Files\media\' + DocumentPath) = 1 then 'exists'
 else 'does not exist'
 end
from Documents

--How to Use xp_dirtree to List All Files in a Folder http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
--Recursive Directory Listing in Transact-SQL: https://www.brad-smith.info/blog/archives/70
--enable extended procs http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html

--enable extended procs (if you have permission! if not, you're stuffed)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE



IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
 DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
 fileId int IDENTITY(1,1)
 ,fullPath nvarchar(2000)
 ,subdirectory nvarchar(max)
 ,depth int
 ,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'C:\Projects\MyStaffFile2\SomeTestData',0,1;

;with fileparents(fileid, subdirectory, parentid, isfile) as (
 select c.fileid, c.subdirectory, (select max(p.fileId) from #DirectoryTree p where p.depth=c.depth-1 and p.fileId<c.fileId) parentId , c.isfile
 from #DirectoryTree c
),DirListing (fileid,subdirectory, isfile) as(
 select fileid, subdirectory + '' subdirectory, isfile from fileparents where parentid is null
 union all
 select f.fileid, d.subdirectory +'\' + f.subdirectory subdirectory, f.isfile
 from DirListing d join fileparents f on f.parentid=d.fileid
)
select l.subdirectory, 'file not in documents table' from DirListing l
left outer join Documents d on l.subdirectory=d.DocumentPath where d.DocumentId is null
--do something useful with this listing

--turn extended procs back off
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE