--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
Blog
Delete all tables from a SQL database using Transact SQL Cursors
If you have a SQL2016 database but only SQL 2008R2 SSMS then you can’t delete tables with extreme prejudice using the DEL key, you get an error something like “index out of range”.
However this SQL will do the same job.
Just remember, it’s your foot…
From an idea from Richard…
declare @sql nvarchar(2048)
declare @tn nvarchar(200)
declare tablecursor cursor
for
select name from sys.tables
open tablecursor
fetch next from tablecursor into @tn
while @@FETCH_STATUS=0
begin
print @tn
declare fkcursor cursor
for
SELECT
‘ALTER TABLE [‘ + OBJECT_SCHEMA_NAME(parent_object_id) +
‘].[‘ + OBJECT_NAME(parent_object_id) +
‘] DROP CONSTRAINT [‘ + name + ‘]’
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(@tn)
open fkcursor
fetch next from fkcursor into @sql
while @@fetch_status = 0
begin
print @sql
exec(@sql)
fetch next from fkcursor into @sql
end
close fkcursor;
deallocate fkcursor
set @sql = ‘drop table ‘ + @tn
exec(@sql)
fetch next from tablecursor into @tn
end
Visual Studio 2017: Program in Invalid State: disable javascript debugging
With VS2017 and an asp.net project, you may get “Cannot start program, it’s in an invalid state”. This may be related to “enable javascript debugging for asp.net (chrome and ie)”.
Try restarting Chrome, IE or Edge.
Try turning off javascript debugging: in the quick launch bar type “javascript debugging” and enter. This takes you to Tools-Options-Debugging-General. Find “enable javascript debugging for asp.net (chrome and ie)” and untick it.
References:
https://www.johanbostrom.se/blog/how-to-disable-the-built-in-chrome-from-starting-when-debugging-in-visual-studio-2017/
Can’t find ionic, cordova scripts: npm on shared workstation
We have a shared workstation (don’t ask!) and after users do
npm install ionic cordova
the command “ionic” doesn’t work.
This is because you need to add the path to npm scripts to your PATH variable.
Your user account path should be
%PATH%;C:\Users\fred\AppData\Roaming\npm;
SQL rows to concatenated string
How to turn a list of (e.g.) countries into a concatenated string (e.g. France, New Zealand, California)
There’s a comprehensive post about different methods at https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485 : the method I’ve used is “Scalar UDF with t-SQL update extension”
ALTER function [dbo].[GetCountriesOfOrigin](@productId int) returns varchar(max) as begin declare @result varchar(max) set @result = '' select @result = @result + c.CountryOfOrigin + ', ' from tblProductCountry pc inner join tblCountryOfOrigin c on pc.CountryOfOriginID=c.CountryOfOriginID where pc.ProductID=@productId order by c.CountryOfOrigin set @result = ltrim(rtrim(@result)) if right(@result,1)=',' set @result=left(@result, len(@result)-1) --damn LEN! ignores trailing spaces if len(@result)=0 set @result=null return @result end
Enable data audit and tracking
This sql script will add system versioning to all your tables in your existing SQL 2016 database.
For example table [Stuff] will have a [StuffHistory] table where all old values are logged.
As we have existing data, we have to do each table in four SQL statements and I don’t think we can make the columns HIDDEN.
/* Set all tables in this database as SYSTEM VERSIONED TEMPORAL TABLES Version 1.1 Nick AjderianEnable data audit and trackingReferences: https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-tableConvert existing table to temporal table*/ /* create table stuff6(stuffid int identity primary key, body nvarchar(200)) update stuff6 set body=body+'x' insert into stuff6(body) values('hello ') select * from stuff6 select * from stuff6history */ declare @sql1 nvarchar(1024),@sql3 nvarchar(1024),@sql4 nvarchar(1024), @sql2 nvarchar(1024) declare @sqlx nvarchar(1024) set @sql1='ALTER TABLE TABLE_NAME ADD SysStartTime datetime2(0) not null DEFAULT SYSUTCDATETIME() , SysEndTime datetime2(0) not null DEFAULT CONVERT(datetime2 (0), ''9999-12-31 23:59:59.99999999'') ' set @sql2='UPDATE TABLE_NAME SET SysStartTime = ''19000101 00:00:00.0000000'', SysEndTime = ''99991231 23:59:59.9999999'' ' set @sql3='ALTER TABLE TABLE_NAME ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);' set @sql4='ALTER TABLE TABLE_NAME SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TABLE_NAMEHistory))' --GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStart --GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEnd declare @tn nvarchar(200) declare c cursor for select name from sys.tables where not ( name like '%History' ) and not exists( select * from sys.tables tt where tt.name=sys.tables.name+'history' ) and name not in ('temp_NHSPatients','Diagnosis','EinsteinData','tbl_DiagnosisImport','tbl_OverwriteCCGs_KH','tbl_OverwriteCCGs_KH2') order by name open c fetch next from c into @tn while @@FETCH_STATUS=0 begin print 'Adding temporal fields to ' + @tn set @sqlx = replace(@sql1,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql2,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql3,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql4,'TABLE_NAME', @tn) exec (@sqlx) fetch next from c into @tn end close c deallocate c
Can’t see my woffs
Bootstrap fonts don’t appear in your published MVC app?
<staticContent>
<mimeMap fileExtension=”woff” mimeType=”application/font-woff” />
<mimeMap fileExtension=”woff2″ mimeType=”application/font-woff” />
</staticContent>