CODEWITHSUNDEEP
sqlsql-server
Steve
Steve

Reputation: 655

Access another database from same server?

How do you access another database from same server? Got server name using SELECT @@SERVERNAME, and then did servername.dbo.mydatabasename.mytablename but query analyzer says Invalid object name. Any ideas? Am doing 

insert into Myservername.Mydatabasename.Mytablename
(Email,Username1)
Values
('h','h')

Using MS SQL Server 2008, on same server

Upvotes: 2

Views: 3068

Answers (3)

Simon Martin
Simon Martin

Reputation: 4231

Should be

insert into Myservername.Mydatabasename.MySchema.Mytablename
  (Email,Username1)
Values
  ('h','h')

Though as you're on the same server you don't need Myservername. In your example which was using three part notation it would have assumed that Mydatabasename was the schema; hence the error

Upvotes: 2

squillman
squillman

Reputation: 13641

Assuming you're using MS SQL Server, fully qualified references are in the form:

[servername].[databasename].[schema].[object]

On the same server you do not need the [servername] reference.

In your case, you reversed the databasename and schema. It should be:

servername.mydatabasename.dbo.mytablename

Your INSERT should look like:

insert into Mydatabasename.Schema.Mytablename
(Email,Username1)
Values ('h','h')

(probably your Schema here is dbo)

You would include the [servername] component when performing an operation across a linked server in which case [servername] would be the name of the linked server, which incidentally may not actually be the the same as the hostname/instance name of the remote server.

Upvotes: 5

Jonathan Hiben
Jonathan Hiben

Reputation: 553

Is it not rather mydatabasename.DBO.mytablename ? And if your database is on the same server, you normally don't have to use the servername.

Upvotes: 2

Related Questions