Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Varchar(max) isn't (truncating error)
Message
From
19/01/2023 13:45:12
 
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Miscellaneous
Thread ID:
01685864
Message ID:
01685866
Views:
27
Field in Table is a varbinary(max)

And I'm calling it with a .ExecuteNonQuery

>What i sthe type of FileData field?
>Also how you call this SP from C#?
>
>
>>Hi all
>>
>>I've got a process (in c)#) that takes a text file and reads it into a string:
>>
>>
>>                string fnrdata = "<pre>";
>>                string fnrLine = "";
>>                using (StreamReader fnr = File.OpenText(nd[3]))
>>                {
>>                  while ((fnrLine = fnr.ReadLine()) != null)
>>                    fnrdata = fnrdata + fnrLine + "<br>";
>>                  fnrdata = fnrdata + "
";
>> }
>>
>>it then passes fnrdata as a parameter to a stored proc. Everything is peachy-keen until we hit a file that creates a string > 8000 characters. then it horks up with the error ""'String or binary data would be truncated. The statement has been terminated.'"
>>
>>The stored proc is as follows:
>>
>>procedure [dbo].[InsertNewFile]
>>  @SID varchar(8),
>>  @County varchar(3),
>>  @TimeRecvd datetime,
>>  @FileName int,
>>  @FileData varchar(max) -- parameter for file string
>>as
>>begin
>>  declare @FData varbinary(max)
>>  set @FData = convert(varbinary(max), @Filedata)
>>-- the following statements were a suggested solution, which doesn't error, but also doesn't insert the @FileData.  A record IS inserted, but --- the FileData field contains only 52 characters
>>  --declare @FD varchar(1) = ''
>>  --set @FD = cast('' as varchar(max)) + @FileData
>>  --set @FData = convert(varbinary(max), @FD)
>>
>>  open symmetric key FNSymKey
>>  Decryption by Certificate FNCert
>>
>>  if not exists(Select 1 from FNFiles where FileName = @FileName)
>>    Insert into FNFiles(SID, County, TimeRecvd, FileName, FileData, InsertDate)
>>    values (@SID, @County, @TimeRecvd, @FileName, 
>>    EncryptByKey(Key_GUID('FNSymKey'),@FData), getdate())
>>
>>  close symmetric key FNSymKey
>>end
>>
>>
>>Suggestions?
"You don't manage people. You manage things - people you lead" Adm. Grace Hopper
Pflugerville, between a Rock and a Weird Place
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform