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