why does table variable sql never return values in r markdown?

  1. Your statement “does not work with non-numeric data” is quite wrong, DBI (with SQL Server) supports SQL Server’s bit, integer, float variants, datetimeoffset variants, char/varchar/nchar/nvarchar variants, etc.

  2. Suggested here, add SET NOCOUNT ON.

Demonstration:

---
title: Why table variable SQL doesn't return values in R Markdown?
---

```{r setup}
library(DBI)
con <- DBI::dbConnect(odbc::odbc(), ...) # redacted
```

```{sql connection=con}
set nocount on
declare @CountriesTable as TABLE (countries varchar(2))
insert into @CountriesTable values ('us'), ('gb'), ('de'), ('fr')
select * from @CountriesTable
```

rendered rmarkdown

Ultimately, I believe the issue is that DBI does not deal with with multiple result-sets. While I had expected that odbc‘s PR 345 would have addressed this in odbc-1.3.0, it does not seem to deal correctly with the first resultset (4, number of rows inserted) and the second (four rows of data).

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top