Conversion from VARBINARY(max) to VARCHAR(max) truncated SQL- with R

Question

I need to convert from a varbinary(max) datatype to a varchar(max).

This is the statement I use:

SELECT file = convert(varchar(max), [colFile]) FROM [myTable]  where key = 1

This returns a truncated text.

If I understood it correctly, it is because the output of the SELECT truncates on 8000 characters.

I checked also the following links and they explain the problem but I don't get the point how to apply it for my case.

Convert Binary Id Field to Text

How to store a string var greater than varchar(max)?

So... how could I get the full text?

EDIT.:

I just checked as suggested in comments and obviously the text is not truncated. It was just a SSMS thing. Therefore, the problem seems to be related to the service that is running the SQL Statement. In this case a R script.

Does R have a restriction for output coming from a SQL-Statement?

SqlCommand commandInsert = new SqlCommand();
commandInsert.Connection = con;
//
SqlCommand commandSelect= new SqlCommand();
commandSelect.Connection = con;

string sqlSelect = String.Format("select myfile = convert(varchar(max), {0})  from {1} where {2} = @fileId",
    "FileData", "FileSample", "FileId");
commandSelect.CommandText = sqlSelect;
commandSelect.Parameters.Add(new SqlParameter("@fileId", 1));
var result = commandSelect.ExecuteReader();

if(result.HasRows)
{
    StringBuilder builder = new StringBuilder();
    while(result.Read())
    {
        builder.Append(result.GetString(0));
    }
    string output = @"./Data/Result.csv";
    File.WriteAllText(output, builder.ToString());
}

//
con.Close();

R Code

> install.packages("RODBC")
> library("RODBC")
> odbcChannel <- odbcConnect("TestDB")
> sqlFetch(odbcChannel, "FileSample")
Error in odbcQuery(channel, query, rows_at_time) : 
'Calloc' could not allocate memory (214748364800 of 1 bytes)
Además: Warning messages:
1: In odbcQuery(channel, query, rows_at_time) :
Reached total allocation of 8075Mb: see help(memory.size)
2: In odbcQuery(channel, query, rows_at_time) :
Reached total allocation of 8075Mb: see help(memory.size)
> dataFrame <- sqlQuery(odbcChannel, "select myfile = convert(varchar(max), FileData)  from FileSample where FileId = 1")
> write.csv(dataFrame, file = "MyData.csv")

Well, I read a little bit about R(I wasn't involve in that part of the project, but for question I wanted to provide all code...)

This is a very basic sample code where the data is fetched from the SQL-Server using the RODBC library and the output file is generated by the previous-known SQL-Statement.

And that file is truncated.

It should have 1000 rows(144183 characters). It throws only 442 (64233 characters)

The file: in pastebin

Why do you say it is truncated? Are you looking at this in SSMS because you are correct that it will get truncated in SSMS. I am curious why you are storing data as varbinary if it is just text? — Sean Lange, Oct 16 '15 at 14:50
@SeanLange yes, I get it truncated in SSMS. I will check if this also happens by an external call. I am storing data as varbinary because the table will store files of diverse type, not only text. Then these files will be retrieve by another service. — blfuentes, Oct 16 '15 at 14:59
@SeanLange you are right. Just checked. Then the problem would be related to one of the services that are trying to fetch data. One of them is a R script. Any idea about R running SQL scripts and truncate data? — blfuentes, Oct 17 '15 at 08:13

Conversion from VARBINARY(max) to VARCHAR(max) truncated SQL- with R

0 Answers0