R中的dbReadTable错误:无效的对象名称 [英] dbReadTable error in R: invalid object name

问题描述

我一直在搜索stackoverflow和Google一个小时，试图弄清为什么我的表无法显示.到目前为止，对其他人有用的解决方案对我不起作用.

I've been scouring stackoverflow and google for an hour trying to figure out why my table won't show up. So far solutions that have worked for others, don't work for me.

我正在连接到这样的数据库:

I'm connecting to a database like this:

library(DBI)
library(dplyr)
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server};", 
                 user = rstudioapi::askForPassword("Your computer usename e.g. SmithJo"), 
                 password = rstudioapi::askForPassword("Your computer password (e.g. to login to Windows)"), 
                 port = 1433, 
                server = "myserver", 
                dbname = "MYDB")

连接到某些表有效:

traptable <- dbReadTable(con, "tblTrap")

其他人则不，即使表存在！

And others don't, even though the table exists!

> dbExistsTable(con, "tlkpSampleType")
[1] TRUE

我提供了一些我尝试过的解决方案...

I've included some of the solutions I've tried...

> dbReadTable(con, "tlkpSampleType")
Error: <SQL> 'SELECT * FROM "tlkpSampleType"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleType'. 
> dbReadTable(con, SQL("tlkpSampleType"))
Error: <SQL> 'SELECT * FROM tlkpSampleType'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleType'. 
> dbGetQuery(con, "SELECT * FROM tlkpSampleStatus")
Error: <SQL> 'SELECT * FROM tlkpSampleStatus'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus'. 
> dbReadTable(con,"MYDB.tlkpSampleStatus")
Error: <SQL> 'SELECT * FROM "MYDB.tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDB.tlkpSampleStatus'. 
> queryResults <- dbGetQuery(con, "SELECT * FROM [MYDB].[abc].[tlkpSampleStatus]")
Error: <SQL> 'SELECT * FROM [MYDB].[abc].[tlkpSampleStatus]'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDB.abc.tlkpSampleStatus'. 
> dbReadTable(con, Id(schema = "tlkpSampleStatus"))
Error: <SQL> 'SELECT * FROM "tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus'. 
> dbReadTable(con, Id(schema = "MYDB", table = "tlkpSampleStatus"))
Error: <SQL> 'SELECT * FROM "MYDB"."tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDB.tlkpSampleStatus'. 
> dbGetQuery(con, paste('SELECT * FROM', 'tlkpSampleStatus'))
Error: <SQL> 'SELECT * FROM tlkpSampleStatus'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus'. 
> dbReadTable(con, Id(schema = "tlkpSampleStatus", table = "tlkpSampleStatus"))
Error: <SQL> 'SELECT * FROM "tlkpSampleStatus"."tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus.tlkpSampleStatus'. 

推荐答案

将查找表保存在与存储表不同的架构中并不少见.无论哪种方式，都应在可能时用表名称限制表名.例如，

It's not uncommon for lookup tables to be kept in a different schema than storage tables. Either way, you should qualify your table names with their schema names when you can. E.g.,

>dbReadTable(con, SQL("dbo.tlkpSampleType"))

要查看表及其模式的完整列表，可以执行以下操作:

To see the complete list of tables and their schemas, you could do something like:

1  rs <- dbSendQuery(con, "SELECT SCHEMA_NAME(schema_id) As SchemaName, name As TableName FROM sys.tables;")
2  dbFetch(rs)

从那里，您可以适当地限定表以供将来查询.

From there, you can qualify the tables appropriately for future queries.

这篇关于R中的dbReadTable错误:无效的对象名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！