Strange result from R query to SQL Server database

Question

Background: I'm working remotely to make R-shiny dashboard. I have been given permissions to a SQL Server database. I don't have any real experience with SQL Server. I have MySQL on my own machine, but I'm logging in to a virtual machine which has R-studio and access to the database.

My connection seems fine

con <- dbConnect(odbc::odbc(), Driver = "ODBC Driver 17 for SQL Server", Server = DBServer, Database = "thedb", UID = DBUser, PWD = DBPassword)

I can run this query and get the right result:

mydates <- dbGetQuery(con, paste0("SELECT var1, datevar FROM table1 WHERE datevar IS NOT NULL"))

but when I run this query:

mynames <- dbGetQuery(con, paste0("SELECT names FROM table2"))

the result is:

1 #NAME?
2 #NAME?
3 #NAME?
4 #NAME?
5 #NAME?
6 #NAME?

I checked with the data warehouser and there are real names in the database - nothing looks like #NAME?.

Also, I do have permissions for both tables.

Can anyone suggest how to fix this?

Update:

SELECT * FROM table2 

works, as does

SELECT rowID, names FROM table2

but it still doesn't work if I only try to pull the names

question from:https://stackoverflow.com/questions/66055192/strange-result-from-r-query-to-sql-server-database

Answer 1

LeanneB,

Sometimes when I'm not sure I have a column name correct, I'll try using either SELECT * FROM table2 for smaller tables or if the table is massive SELECT TOP 10 * FROM table2 to see the specific column names available in that table. Once I know what column names I need, I adjust my query and remove the SELECT * or the SELECT TOP 10 * part and replace with the specific columns I want the query to return.