Invalid result: Zero rows returned when using odbc on mssql

#1

Hi there,

I am trying to test the odbc package on Microsoft SQL Server, however the problem I face is it only returns zero rows even though there is data in the table, say I execute the query with Visual Studio.

Could anybody please help, googling for the answers is difficult because it keeps finding results pertaining to RODBC not odbc.

Here is the code, I have masked the server name etc for security purposes. Apologies for not being able to produce a different reprex.

library(DBI)

con <- DBI::dbConnect(odbc::odbc(), 
	Driver = "SQL Server Native Client 11.0", 
	Server = "<server>", 
	Database = "<database>", 
	UID = "<username>", 
	PWD = "<password>", 
	Port = 00000)
dbGetQuery(con, "SELECT TOP 1000 * FROM [DATABASE].[dbo].[TABLE]")
DBI::dbDisconnect(con)


<0 rows> (or 0-length row.names)
#2

Hi, is that the actual port number used? Not sure if that's masked as well

#3

Hi, apologies yes the port is also masked.

#4

So I think you want to be a bit more explicit in your code with the connection variable:

con <- DBI::dbConnect(odbc::odbc(), 
    .connection_string =
	"Driver = 'SQL Server Native Client 11.0';
	Server = "<server>"; 
	Database = "<database>"; 
	UID = "<username>"; 
	PWD = "<password>"; 
	Port = 00000;")
query <- dbGetQuery(conn= con, statement = "SELECT TOP 1000 * FROM [DATABASE].[dbo].[TABLE]")

results <- dbFetch(query) 
DBI::dbDisconnect(con)

Try using the .connection_string argument in your dbConnect call:
https://www.rdocumentation.org/packages/odbc/versions/1.0.1

It's definitely not easy to build, but I've found that copying and pasting the contents from a .dsn file after creating the connection in Access works. Does this help?