Invalid Object Name SQL

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



Leave a reply



Submit