question

JamesHackney-9611 avatar image
0 Votes"
JamesHackney-9611 asked MelissaMa-msft commented

Invalid Object Name when attempting to insert data from a table in one DB to a table in another DB on the same server

I am attempting to perform an insert from a table in one database to a table in another database on the same server I have tried this:

 INSERT INTO SDCS_AUDIT.BMA_FORM_DATA (.....)

and this:

 INSERT INTO [SDCS_AUDIT].[BMA_FORM_DATA] (...)

as well as this:

 INSERT INTO DATABASE_Name.SDCS_AUDIT.BMA_FORM_DATA (...)

and this:

 INSERT INTO [DATABASE_Name].[SDCS_AUDIT].[BMA_FORM_DATA] (...)

I have also attempted to add the server name in front of the database name but it doesn't like that either.

I know that you need to use sp_linkedserver to create a linked server if the dbs are on two separate servers but from my understanding I shouldn't have to do that if they are on the same server.

Intellisense doesn't show any of the other databases on this server. Is there an option I have to turn on/off somewhere or am I making a bonehead mistake somewhere? This is inside of a trigger by the way, in case that matters.





sql-server-transact-sqlazure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hopefully, Andrei's answer gives you something to work from. But else you will need to post the full statements together with the error messages, and without anonymising anything. If we don't see what you are doing, we can only make wild guesses about what you are doing.

0 Votes 0 ·

I think I am having problems other than code although I am not sure what it is. Thanks for your help!

0 Votes 0 ·
AndreiFomitchev avatar image
0 Votes"
AndreiFomitchev answered JamesHackney-9611 commented
  • For Azure:
    CREATE MASTER KEY; -- create master key
    GO


CREATE DATABASE SCOPED CREDENTIAL CrossDbCred
WITH IDENTITY = 'CrossDb', -- login user name
SECRET = 'P@ssword'; -- password
GO


CREATE EXTERNAL DATA SOURCE table2
WITH
(
TYPE=RDBMS,
LOCATION='server.database.windows.net',
DATABASE_NAME='database2',
CREDENTIAL=CrossDbCred
);
GO

try also another LOCATION format:
LOCATION = 'sqlserver://WINSQL2019'
See here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated

  • in database1
    CREATE EXTERNAL TABLE [dbo].[table2]
    ( -- in database1.table2 the same columns as in database2.table2
    colimn1 INT,
    ...
    )
    WITH (DATA_SOURCE = [database2],
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'table2'
    );
    GO

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, thanks for the example Andrei! Between this and the docs I should have everything up and running in no time.

0 Votes 0 ·
AndreiFomitchev avatar image
0 Votes"
AndreiFomitchev answered JamesHackney-9611 commented

Working Example:
CREATE DATABASE testDB1;
CREATE DATABASE testDB2;
USE testDB1
GO
CREATE TABLE TestTable1(id1 Int)
GO
USE testDB2
GO
CREATE TABLE TestTable2(id2 Int);
GO
INSERT INTO testDB1.dbo.TestTable1(id1) VALUES (1234);

INSERT INTO testDB2.dbo.TestTable2(id2)
SELECT id1 FROM testDB1.dbo.TestTable1;

SELECT * FROM testDB2.dbo.TestTable2
-- It returns 1234.
1. Use <DB Name>.<Schema Name>.<Table Name>
2. Check that your account has access for SELECT / INSERT in the databases/tables.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Both databases are already created and have data in them.

Use <DB Name>.<Schema Name>.<Table Name>

Yes, this is what I did. Both with [ ] and without.

Check that your account has access for SELECT / INSERT in the databases/tables.

I am the creator and database administrator of both databases. I am able to perform select/insert functions in both databases as well as create stored procedures, functions and triggers.

I will add the complete code. Also, this is Azure SQL Database instead of SQL Server if that makes a difference.






0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered JamesHackney-9611 commented

Hi @JamesHackney-9611

Welcome to Microsoft Q&A!

You could refer Andrei's example and check whether any is different or missing.

Besides, it could be recommended to post the DDL and sample data together with the error message.

You could confirm existance of a table using following query:

 USE DATABASE_Name
 SELECT OBJECT_ID('SDCS_AUDIT.BMA_FORM_DATA','U')

OR

 SELECT OBJECT_ID('DATABASE_Name.SDCS_AUDIT.BMA_FORM_DATA','U')

If output is not NULL then your table already exists.

In addition,regarding the errror message 'invalid object name', you can make sure below points:

  • Check you are connectiong to the correct database.

  • In SSMS,go to Edit -> IntelliSense ->Refresh Local Cache or right click the database, and click "refresh".

  • Check you have all permission on that table including view definition.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am the creator of both databases and am a member of the admin group. Both databases have data in them. I can perform select/inserts, create stored procedure, functions and triggers.

SELECT OBJECT_ID('DATABASE_Name.SDCS_AUDIT.BMA_FORM_DATA','U')

I tried the above command in all three databases and received NULL in all of them, one of which is been up and running and has been used with no issues for more than a year.

One thing I notice that is different than our on-prem SQL Server dbs though is when I click on the "Available Databases" drop down menu in SSMS, only the master and the db I clicked on in the dropdown menu appears. If I want to access the other dbs, I have to first click master for all of the dbs to show. In our on-prem sql server dbs, all databases on the server appear regardless of which one I am clicked on.









0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JamesHackney-9611 commented

Also, this is Azure SQL Database instead of SQL Server if that makes a difference.

Yes, that makes a difference. You can't make cross-database references in Azure SQL DB. In Managed Instance you can, but not DB.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well that explains that. All of our databases work in conjunction with audit databases so the thought that two databases couldn't interact with each other never occurred to me. Thank you!.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered JamesHackney-9611 commented

HI @JamesHackney-9611,

Thanks for your update.

Actually I am not familar with Azure SQL Database.

I did some research and found one useful article which introduces some detailed steps of cross-database query in Azure SQL Database.

After creating two tables in different Azure SQL database, you also need to create database scoped master key and credentials ,External data sources and External tables.

Please refer more details from below and check whether it is helpful:
Cross-database Query in Azure SQL Database

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for that information! I will check it out.

0 Votes 0 ·
ApplebyNick-0083 avatar image
0 Votes"
ApplebyNick-0083 answered MelissaMa-msft commented

@MelissaMa-msft -- I have just stumbled across this and am having the same issue:
Now from my understanding, the solution is to use External data source & External Tables as the doc you direct to states however, is it possibly to do this with an Azure Managed identity rather than SQL Auth?

As Per the original post, both the db's in question are within the same subscription and resource group on the same Server instance, both are serverless.

This is my thinking so far:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<xxxxxxxxxxxx>';
CREATE DATABASE SCOPED CREDENTIAL <credentialName>
WITH IDENTITY = '<managedIdentityName'
;

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ApplebyNick-0083,

Welcome to Microsoft Q&A!

I am mainly responsible for transact-sql (SQL Server on-premise) and not very familar with Azure SQL database.

You could refer above accepted answer or documents I provided and check whether there is any missing.

If you still have any issue about this, I recommend that you could post a new question here in Microsoft Q&A and add only 'azure-sql-database' tag and you will get more professional help from many experts.

Thank you for understanding!

Best regards
Melissa

1 Vote 1 ·