sql server invalid object name - but tables are listed in SSMS tables list
Try:
Edit -> IntelliSense -> Refresh Local Cache
This should refresh the data cached by Intellisense to provide typeahead support and pre-execution error detection.
NOTE: Your cursor must be in the query editor for the IntelliSense menu to be visible.
Invalid object name in SQL Management Studio
First refresh the Tables
in your database to check whether that table existed or not.
If it does exist, check your SQL connection
on your current session, make sure you are connecting to the correct server instance.
Invalid object name' using Dapper
I figured out that Access uses [dbo_tbl_SmileData].[ProcessFlow_ID] if a table/column is used multiple times, but SQL can't handle the [ ]. Also Access uses dbo_tbl_SmileData, but SQL needs dbo.tbl_SmileData.
So [dbo_tbl_SmileData].[ProcessFlow_ID] becomes dbo.tbl_SmileData.ProcessFlow_ID.
tbl with in_schema returns Invalid object name error
The difference in speed is because tbl(con, ...)
is creating an access point to a remote table, while dbReadTable(con, ...)
is reading/copying the table from SQL into R.
The approach you were using has been the standard work-around for specifying both database and schema. I would guess there has been an update to the dbplyr package that means this work-around now requires an additional step.
Taking a close look at the SQL from the error message reveals the cause:
SELECT * FROM "DB01WEA84103.dbo"."Ad10Min1_Average"
Note the double quotes around "DB01WEA84103.dbo"
. The double quotes tell SQL to treat this as a single object: a schema with name DB01WEA84103.dbo
, instead of two objects: a database with name DB01WEA84103
and a schema with name dbo
.
Ideally this query would read:
SELECT * FROM "DB01WEA84103"."dbo"."Ad10Min1_Average"
Now the full stop is not included in the double quotes.
Reading the dbplyr documentation (link) for in_schema
is specifies that the names of schema and table "... will be automatically quoted; use sql()
to pass a raw name that won’t get quoted."
Hence I recommend you try:
tbl(con, in_schema(sql("DB01WEA84103.dbo"),"Ad10Min1_Average"))
Notes:
- Double quotes in SQL are used to indicate a single object, ignoring special characters. Square brackets are often used in SQL for the same purpose.
- Whether you use single or double quotes in R does not affect whether or not the SQL code will contain double quotes. This is controlled by dbplyr's translation methods.
- If your database name contains special characters then try enclosing them in square brackets instead: For example
[my!odd@database#name].[my%unusual&schema*name]
.
Related Topics
Sync Between SQL Server and MySQL Server
Convert Exponential to Number in SQL
SQL Server Insert into with Where Clause
Get the Id of Last Inserted Records
Does SQLite Support Scope_Identity
SQL Server 'In' or 'Or' - Which Is Fastest
SQL How to Update Sum of Column Over Group in Same Table
Postgres Trigger-Based Insert Redirection Without Breaking Returning
Deferrable Check Constraint in Postgresql
Row_Number() Over Not Fast Enough with Large Result Set, Any Good Solution
SQL Query to Count() Multiple Tables
Limiting the Number of Records in a SQLite Db
Create SQL Server Table Based on a User Defined Type
How to Create a Postgres Table with Unique Combined Primary Key