sqlQuery return 'character (0)' result

[Jebediah]

Hello everyone

I try to monitor my database with 'R' Script. I'm not very confortable with 'R' Language, I'm a selflearning beginer that need some advices.

Got a SQLquery request that returns 'character(0)' instead of the result of myquery when I launched my 'Rscript' on command line. The SQL code is OK, I tested it before encapsulating it on a 'R' script. I read some topic about sqlQuery syntax but can't find what is wrong with my query.

Here the code I'm using :

library(RODBC)
driver <- "ODBC Driver 17 for SQL Server"
db <- "MYBASE"
host_dev <- "XXXXXXXXXX"
port <-"XXXX"
user <-"XXXXXXXXXX"
pwd <- "XXXXXXXXXX"
conn_ODBC <- paste("DRIVER=",driver,
                  ";Database=",db,
                  ";Server=",host_dev,
                  ";Port=",port,
                  ";PROTOCOL=TCPIP",
                  ";UID=", user,
                  ";PWD=",pwd,
                  sep="")
conn_shrinker_ODBC <- odbcDriverConnect(conn_ODBC)

DECLARE_LOG_SIZE <- "

SET NOCOUNT ON;

DECLARE @LogSpace TABLE

(
 DatabaseName varchar(255),
 [Log Size (MB)] float,
 [Log Space Used (%)] dec(4,1),
 [Status] int
);
 
 INSERT INTO @LogSpace
 EXECUTE('dbcc sqlperf(''LogSpace'')');
 
 SELECT * FROM @LogSpace
 where DatabaseName = 'MYBASE';

SET NOCOUNT OFF; "

sqlQuery(conn_shrinker_ODBC,DECLARE_LOG_SIZE)
odbcCloseAll()

Thanks for you help