Camillia
asked on
Size of file is different after I save and want to read it back
LowFatSpread helped me with the solution here:
https://www.experts-exchange.com/questions/28020795/String-or-binary-is-truncated-varbinary-max-field.html?anchorAnswerId=38861456#a38861456
1. I save the file using the method in that solution and I have it below. I put a debug step and the datalength of the doc is 4804
This is how I read it back and I have debug steps with the datalength. When I read it back in code, the file is corrupt. Not sure what i need to change
If I need to do all this in code, i can go that route if this doesnt work
https://www.experts-exchange.com/questions/28020795/String-or-binary-is-truncated-varbinary-max-field.html?anchorAnswerId=38861456#a38861456
1. I save the file using the method in that solution and I have it below. I put a debug step and the datalength of the doc is 4804
declare @estring varbinary(max),@i bigint,@l bigint
select @i=1,@l=datalength(@doc)
SELECT 'size before loop'
SELECT DATALENGTH(@l)
OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptMyCert
declare @temp varbinary(8000)
set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(8000),'')
while @i<=@l
BEGIN
SELECT
@estring = @temp +
ENCRYPTBYKEY(KEY_GUID('MyTableKey'),
SUBSTRING(@doc, @i,
CASE WHEN @i + 7899 <= @l
THEN 7900
ELSE @l - @i +1
END))
set @i = @i + 7900
end
SELECT 'size after loop'
SELECT DATALENGTH(@estring) -- size is 4808
Insert into table....
select ....
@estring
This is how I read it back and I have debug steps with the datalength. When I read it back in code, the file is corrupt. Not sure what i need to change
OPEN SYMMETRIC KEY ARMSTableKey DECRYPTION
BY CERTIFICATE EncryptArmsCert
SET NOCOUNT ON;
declare @estring varbinary(max),@i bigint,@l bigint
select @i=1
SELECT @l= datalength(doc) FROM dbo.DocumentFiles WHERE Id = @Id
declare @temp varbinary(8000)
set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(8000),'')
SELECT 'before loop'
SELECT '@l'
SELECT @l ---***** size is 4804
while @i<=@l
BEGIN
SELECT
@estring = @temp +
SUBSTRING(CONVERT(VARBinary(MAX),DECRYPTBYKEY(Doc)), @i,
--SUBSTRING(Doc, @i,
CASE WHEN @i + 7899 <= @l
THEN 7900
ELSE @l - @i +1
END)
FROM dbo.DocumentFiles WHERE Id = @Id
set @i = @i + 7900
end
SELECT DATALENGTH(@estring) --**** size is 4761
If I need to do all this in code, i can go that route if this doesnt work
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
http://support.microsoft.com/kb/307010
maybe you're right...the size is too small...let me try