I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I’m populating a
FileProperties table (especially for non-transactional file copies) is for building relational data that will work with EF6. I’m hopeful exposure here may shed some light on my issue.
I’m using the
FileTables feature and have setup a trigger to populate a
FileProperties table whenever a file is copied to the SQL Server file share.
I’m trying to grab the file size (
cached_file_size), and I get a value of zero. I set up the trigger the same way a standard
FileTable does using
DATALENTGH(file_stream). The code below is using the inserted tables’
ALTER TRIGGER [dbo].[FileTable_Insert_Trigger] ON [dbo].[Files] AFTER INSERT AS BEGIN IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; IF NOT EXISTS (SELECT 1 FROM INSERTED) RETURN; DECLARE @s_id UNIQUEIDENTIFIER, @fs VARBINARY(MAX), @nm NVARCHAR(255), @fp NVARCHAR(MAX), @cfs BIGINT, @ft NVARCHAR(255); SELECT @s_id = ins.stream_id FROM inserted ins; SELECT @fs = ins.file_stream FROM inserted ins; SELECT @nm = ins.name FROM inserted ins; SELECT @fp = ins.file_stream.GetFileNamespacePath() FROM inserted ins; SELECT @ft = ins.file_type FROM inserted ins; SELECT @cfs = DATALENGTH(@fs); INSERT INTO [FileProperties] (stream_id, [name], filepath, file_type, cached_file_size, DateAdded, UserID) VALUES (@s_id, @nm, @fp, @ft, @cfs, CURRENT_TIMESTAMP, 1); END
I’ve also tried grabbing the computed value directly from the
SELECT @cfs = Files_1.cached_file_size FROM [dbo].Files AS Files_1 WHERE Files_1.stream_id = @s_id;
Still getting zero. What am I missing here? Thank you.
Note: I tried playing around with getting the
DATALENGTH of other values such as the
name (@nm) and
stream_id (@s_id) and I do get the values correctly.