September 19, 2014 at 4:26 am
Hello
I have a question. In SQL server, we can access a table or view in a different database by using the dot notation in the table name, so I believe that in SQL server, if we say just <tablename> it will refer to the table name in the current active DB, but if we say <DBname>.<Tablename> you can access the table in a different DB on the same server, and if you say <serverName>.<DBName>.<tableName> we can access a table in a remote DB whose DBName is defined in a connection setting. I believe we can define a connection setting between two SQL server to access a different database. So, instead of copying the data from one DB to other and duplicate the same, it might be better to simply define a view.
Can anyone please help me if we can do so and how ?
Any help would be appreciated !!
Thanks in Advance!
Thanks,
Kanishka
September 19, 2014 at 5:55 am
Your referring to the four-part notation. The correct notation is [instance_name].[database_name].[schema_name]..
To be able to use this accross different instances a linked server connection is required from the querying instance to the queried instance. Also appropriate linked server settings and permissions must be set.
You have to test which solution will be the best option for your situation:
- databases on different instances and servers and query over linked server connection
- databases on different instances (same server) and query over linked server connection
- databases on the same instance and query over across databases
- all data in the same databases (perhaps different schemas) and query within the database scope
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply