如何在R中执行SQL查询？

我想在RStudio中执行SQL查询以获取数据。 我不太了解这些查询，但是当我在PowerBI中尝试使用该查询时，该查询可以返回我想要的数据。

-- *** Set datum variables
declare @from as datetime = dateadd(day, -7,getdate())
declare @to as datetime = getdate()

-- *** Drop temporary table if exists
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp

-- *** Query QI analist database for data in AWZ workspaces 
SELECT CONVERT(VARCHAR(19),a.[CT],101) + ' ' + CONVERT(VARCHAR(8),a.[CT],114) as 'DateTime'
    , (E.NAME + '_' + C.NAME) as Tag
    , (case when (ISNUMERIC(a.MVAL)=1 and (a.MVAL)<>0.0) then str(a.MVAL, 7, cast(round(6 - log(a.MVAL,10),0) as int)) end) as 'Value'
    , a.version
into #tmp -- *** Query to temporary table. Afterwards only the highest version of entered data must be selected. 
FROM [QI_PDATA] A 
  join [QI_DICTIONARY] C ON (C.ID =A.PARENTCOLUMNID)
  join [QI_VARIABLEINFO] D ON (D.ID =A.PARENTCOLUMNID)
  join [QI_DATATABLES] E on ( a.DATATABLEID=E.ID) 
WHERE e.[NAME] like 'AWZ%'
 AND a.[CT] between @from and @to
 and not ISNULL(a.MVAL,-10000000.0)=-10000000.0 

-- *** To get most recent data filter data per sample with highest version from the temporary table.
 select t.* 
 from ( select [DateTime]
                ,[Tag]
                ,MAX([version]) as maxV     
    from #tmp
    group by [DateTime], [Tag] ) as m
    inner join #tmp as t
        on  t.[DateTime]=m.[DateTime]
        and t.[Tag]= m.[Tag]
        and t.[version] = m.maxV
    order by DateTime asc

 -- Drop temporary table
 drop table #tmp

当我尝试在R中运行查询时，它不返回任何内容。 这是我尝试运行的R代码。

db_InSQL <- DBI::dbConnect(
  odbc::odbc(),
  driver = "SQL Server",
  server = "",
  database = "",
  uid = "",
  pwd = ""
)
query <- "-- *** Set datum variables
declare @from as datetime = dateadd(day, -7,getdate())
declare @to as datetime = getdate()

-- *** Drop temporary table if exists
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp

-- *** Query QI analist database for data in AWZ workspaces 
SELECT CONVERT(VARCHAR(19),a.[CT],101) + ' ' + CONVERT(VARCHAR(8),a.[CT],114) as 'DateTime'
    , (E.NAME + '_' + C.NAME) as Tag
    , (case when (ISNUMERIC(a.MVAL)=1 and (a.MVAL)<>0.0) then str(a.MVAL, 7, cast(round(6 - log(a.MVAL,10),0) as int)) end) as 'Value'
    , a.version
into #tmp -- *** Query to temporary table. Afterwards only the highest version of entered data must be selected. 
FROM [QI_PDATA] A 
  join [QI_DICTIONARY] C ON (C.ID =A.PARENTCOLUMNID)
  join [QI_VARIABLEINFO] D ON (D.ID =A.PARENTCOLUMNID)
  join [QI_DATATABLES] E on ( a.DATATABLEID=E.ID) 
WHERE e.[NAME] like 'AWZ%'
 AND a.[CT] between @from and @to
 and not ISNULL(a.MVAL,-10000000.0)=-10000000.0 

-- *** To get most recent data filter data per sample with highest version from the temporary table.
 select t.* 
 from ( select [DateTime]
                ,[Tag]
                ,MAX([version]) as maxV     
    from #tmp
    group by [DateTime], [Tag] ) as m
    inner join #tmp as t
        on  t.[DateTime]=m.[DateTime]
        and t.[Tag]= m.[Tag]
        and t.[version] = m.maxV
    order by DateTime asc

 -- Drop temporary table
 drop table #tmp"

############################## Send SQL query
# Defind Query Object
rs <- dbSendQuery(db_InSQL,query)
# Get Data
QI_data <- dbFetch(rs,n = -1)
# Clear Query Object
dbClearResult(rs)
# Disconnect Data connection
dbDisconnect(db_InSQL)

我可以很好地连接到数据库（我故意在此处保留空白以获取数据库连接信息）。

我已经尝试过另一个查询，它可以正确返回数据。 我的R脚本有什么问题吗？还是因为查询？ 我对R还是陌生的，因为我是从朋友那里得到的，所以我对查询一无所知。