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' file_stream
:
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 FileTable
:
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.
@nm
,@fp
etc, that just makes it harder to read. Use full words.cached_file_size
- why not use that?