I have a fairly complex SQL query that I am trying to run through RODBC that involves defining variables. A simplified version looks like this:
DECLARE @VARX CHAR = 'X';
SELECT * FROM TABLE WHERE TYPE = @VARX;
Running this code works just fine. This fails:
library(RODBC)
q <- "DECLARE @VARX CHAR = 'X';\nSELECT * FROM TABLE WHERE TYPE = @VARX;"
sqlQuery(ch, q)
# returns character(0)
I have found through experimentation that the first statement before the semicolon is executed, but the rest is not. There is no error--it just seems that everything after the semicolon is ignored. Is there a way to execute the full query?
I'm using SQL server by the way.
NOTE: I asked this question before and it was marked as a duplicate of this question, but they are asking completely different things. In this question I would like to execute a script that contains multiple statements, and in the other the author is only trying to execute a single statement.