How to reference a linked server in code

Question

I have a link established between to SQL server one is an SQL 2008 R2 and the other is SQL 2014. I've established a link between the two and on the 2008 can see the link connected and the database on the 2014 server.

I now want to pull data from 2008 and insert it into the 2014 server.  The code is below:

Use [LinkedServer-1\ServerInstanceName].[CR]
Go

Insert into [CR].[dbo].[CRS](Field-1, Field-2, Field-3, Field-4, Field-5, Field-6,
                             Field-7, Field-8, Field-9, Field-10, Field-11, Field-12)

SELECT [Field1]
      ,[Field2]
      ,[Field3]
      ,[Field4]
      ,[Field5]
      ,[Field6]
      ,[Field7]
      ,[Field8]
      ,[Field9]
      ,[Field10]
      ,[Field11]
      ,[Field12]
  FROM [Database-1].[dbo].[Table-1]

The select statement is fine no issues.  however I'm having problem with use statement that points to the linked server.  In the user statement the linked server is connected with following name "ServerName\InstanceName".  So am using the name that shows as the linked server name.  In the Linked Server Tree I can see the server and all of the database and tables which tells me that the link is connected. However, when I try to reference the linked server in the use statement I get the following message:  " Can not locate entry in sysdatabases for database linked LinkedServer-1\ServerInstanceName. 

How do I reference the linked server name and database instance in my code above? 

Answer 1

If you are on the server where the source data resides, you would say

INSERT [LinkedServer-1\ServerInstanceName].[CR].[dbo].[CRS]
   (Field-1, Field-2, Field-3, Field-4, Field-5, Field-6,    Field-7, Field-8, Field-9, Field-10, Field-11, Field-12)
   SELECT ...

If you are on the target server, you would instead put the name of the linked server in the FROM clause of the SELECT part.

Answer 2

I'm still unable to resolve my issue:  the code below is what I have in place:

/* Destination Database */


Use[ServerName\SQLInstanceName]
Go
/* Destination Database */
Insert into [ServerName\SQLInstanceName].[DatabaseName].[dbo].[Table-1](Field-1, Field-2, Field-3, Field-4, Field-5
, Field-6, Field-7, Field-8, Field-9, Field10, Field-11, Field-12)

I have the destination server connected as a linked database.  I can view it in the tree in the left pane, however it is not recognized in the query window.  Shouldn't this work? Especially since it is a linked server. 

Thanks

 

Answer 3

You cannot "USE" a linked server. You can only USE a database that exists in the server to which your session is connected. If you connect to your 2008 server, then you simply insert into the linked server using:

insert linkedserver.database.schema.table (col1, col2 ...)
select col1, col2 ... from schema.mytable where ...

where the linkedserver = your 2014 server instance. And this is the name you defined when you created your linked server. As a test, you can simply try the following:

select top 10 * from linkedserver.database.schema.table

And just to be clear:

database = your actual database name
schema = the schema that contains your table
table = the name of the table you want to access

Answer 4

I reversed the source and destination SQL server.  Now the source server is not accessible in the query window and the destination is fine.  The source is a linked server that is accessible in "Server Objects.Linked Server".  I can expand the linked database and list all of the Views and Table.  So why can't I gain access to what is viewable in the left pane of SQL Studio Manager. 

I'm getting the following message in the from statement below:

"Invalid Object Name from [ServerName.DatabaseName].[dbo].[Table-1]"

The server name and SQL instance are the same.

Thanks 

 

Answer 5

ServerName is the name of the server

dbo is the schema name

table-1 is the table that I'm trying to access.

Thanks

Answer 6

The name [ServerName.DatabaseName].[dbo].[Table-1] has three components. There is a table called Table-1 and a schema dbo. And there is a database mysteriouisly called ServerName.Databasename. To wit, the brackets enclose identifiers. You should only use the brackets when needed, that is when the name is not a legal identifier. [Table-1] is such a case. But you are better off not using it all over the place. If nothing else, it litters the code.

In this case use simply ServerName.DatabaseName.dbo.[Table-1]. (But if the servername includes an instance part, you need to quote the servername [ServerName\Instance].DatabaseName.dbo.[Table-1].

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

I've removed the "[]" from the SQL query and my results are the same, I'm unable to reference the server and database that connected via a linked server.   The code is below:

SELECT FieldA
      ,FieldB
      ,FieldC
      ,FieldD
      ,FieldE
      ,FieldF
      ,FieldG
      ,FieldH
      ,FieldI
      ,FieldJ
      ,FieldK
      ,FieldL
  FROM SQLServerName.DatabaseName.dbo.TableA

The above statement is underscored in RED and the following message is displayed:

Invalid Object Name: "SQLServerName.DatabaseName.dbo.TableA"

The SQLServerName is connected via a "Linked Server" SQL Studio Manager when I expand the linked server folder in the left pane I can see all of the Databases on the Linked Server an the for the database that I'm trying to select from all of the tables and views.  But when I reference the database and views in my SQL I get the message "Invalid Object" as stated above.  Is there a requirement to identify the server as a linked server?  If so what is it?

Thanks

Answer 8

Well after several post between us it turns out that the message "invalid Object Name" is an error and the red highlighting of the SQL code is also.  I just decided to run the code since I found nothing wrong and based on your post I was doing the right thing.  The code is running now and will let you know if it works, so far it is still executing.  Can you please have the developers of SQL Server to investigate and correct the erroneous error regarding the use of a linked server in SQL code. 

Thanks

Answer 9

The intellisense in SSMS is not the same thing as SQL Server. It can be helpful at times, but it can be lured if you create objects and start to use them directly, as works from a cache of data. As you have found, there can be good reasons to disbelieve it.