SQL Azure - Unable to query External Table from SSMS - Error Invalid object name 'dbo.AuditLogS-CodePudding

Is there someone who can help me figure out why I cannot query an external table that I created using my SQL Server Mgt Studio. I can see the external table if I expand External Tables but if I Right click and Select Top 1000 Rows I get an error that Invalid object name 'dbo.AuditLogSource'.

I am trying to copy a certain amount of data from an audit log table in DB1.AuditLog into ArchiveDB.AuditLog. I've followed the tutorials on how to use Elastic Queries to archive this simple task but I am now stuck at this point where I should query from the external table created locally in my ArchiveDB. Here's the process I followed maybe I made a mistake somewhere please help me:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '2019MoxvE!';

--DROP MASTER KEY;

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential  
WITH IDENTITY = 'myusername',
SECRET = '2019MoxvE!';

--DROP DATABASE SCOPED CREDENTIAL SQL_Credential;

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='ourserver.database.windows.net',
    DATABASE_NAME='DB1',
    CREDENTIAL= SQL_Credential
);

--DROP EXTERNAL DATA SOURCE RemoteReferenceData;

CREATE EXTERNAL TABLE [dbo].[AuditLogSource]
(
    [Id] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [ObjectId] [int] NULL,
    [CreatedOn] [datetime] NOT NULL,
    [ModifiedOn] [datetime] NOT NULL,
    [ModifiedBy] [varchar](150) NOT NULL,
    [Type] [int] NOT NULL,
    [ActionTable] [varchar](50) NOT NULL,
    [IsAjaxRequest] [bit] NOT NULL,
    [Parameters] [varchar](max) NOT NULL,
    [Controller] [varchar](50) NOT NULL,
    [Action] [varchar](50) NOT NULL,
    [Comments] [varchar](max) NULL,
    [BeforeImage] [varchar](max) NULL,
    [AfterImage] [varchar](max) NULL,
    [Browser] [varchar](max) NULL
)
WITH (DATA_SOURCE = [RemoteReferenceData]);

--DROP EXTERNAL TABLE [dbo].[AuditLogSource];

INSERT INTO [dbo].[AuditLog]
SELECT al.* FROM [dbo].[AuditLogSource] al WHERE al.[CreatedOn] <= '2020/12/31' AND
NOT EXISTS(SELECT 1 FROM [dbo].[AuditLog] al1 WHERE al1.Id=al.Id);

If you see on below screenshot, you can see that there are no errors being highlighted on this query which means that the query window does recognise that the table AuditLogSource does exists but if I run the query it complains that it does not exists. I can also confirm that the user I am logged into the database with is the admin user and own of both DB1 and ArchiveDB What can I do to make this work?

Thanks in advance.

CodePudding user response：

Make sure you're using the correct database also if you create a new SQL Server object, your newly created object does not get updated in the IntelliSense Local Cache and due to this, it shows an Invalid object name: dbo.AuditLogSource.Please follow below reference.

Ex: [DatabaseName].[Schema].[TableName]

Try:

Edit -> IntelliSense -> Refresh Local Cache or Ctrl shift R

Reference:

Sql server invalid object name - but tables are listed in SSMS tables list

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated

CodePudding user response：

Ok so I will post an answer to this question in case another person comes across the same/similar problem. So I only made 1 mistake in creating the External Table and this is because of the tutorials and other answers I saw on this very platform.

CREATE EXTERNAL TABLE [dbo].[AuditLogSource]
(
    [Id] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [ObjectId] [int] NULL,
    [CreatedOn] [datetime] NOT NULL,
    [ModifiedOn] [datetime] NOT NULL,
    [ModifiedBy] [varchar](150) NOT NULL,
    [Type] [int] NOT NULL,
    [ActionTable] [varchar](50) NOT NULL,
    [IsAjaxRequest] [bit] NOT NULL,
    [Parameters] [varchar](max) NOT NULL,
    [Controller] [varchar](50) NOT NULL,
    [Action] [varchar](50) NOT NULL,
    [Comments] [varchar](max) NULL,
    [BeforeImage] [varchar](max) NULL,
    [AfterImage] [varchar](max) NULL,
    [Browser] [varchar](max) NULL
)
WITH
(
    DATA_SOURCE = [RemoteReferenceData],
    SCHEMA_NAME = 'dbo', -- I missed this part
    OBJECT_NAME = 'AuditLog' -- I missed this part
);

So my problem was that I had omitted the SCHEMA_NAME = 'dbo' and OBJECT_NAME = 'AuditLog' which makes a reference to the AuditLog table in DB1. With my OP, Azure was making a reference to AuditLogSource in DB1 which obviously doesn't exist hence I get the error I was getting. BUT, it would help if the query failed in the first place coz that would've highlighted that there was something wrong somewhere. Anyway, I hope this helps someone.