error in linked server in sql server 2008 r2

Question

i have a table tmpDTTransfer in my database demo:-

 GO
 /****** Object:  Table [dbo].[tmpDTTransfer]   
    Script Date: 08/10/2015  12:41:21 ******/
        SET ANSI_NULLS ON
          GO
            SET QUOTED_IDENTIFIER ON
          GO
            SET ANSI_PADDING ON
         GO

       CREATE TABLE [dbo].[tmpDTTransfer](
          [id] [int] IDENTITY(1,1) NOT NULL,
          [Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Transfer] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 CONSTRAINT [DF_tmpDTTransfer_Transfer]  DEFAULT ('N')
) ON [PRIMARY]

 GO
 SET ANSI_PADDING OFF

now i insert data into this table:-

         id Code           Transfer
         -----------------------------
         1  L-57           y
         2  BP _162        y
         3  RS_400         n
         4  jhony_12000    n
         5  enti_525       y

i have also a linked server.i

i am trying to copy this table in another database Audit which are present on another server,then i try this query for this task:-

   select * into [192.168.1.6].[Audit].[dbo].[tmpDTTransfer]
    from [tmpDTTransfer]

error is as follows:-

Msg 117, Level 15, State 1, Line 2 The object name '192.168.1.6.Audit.dbo.tmpDTTransfer' contains more than the maximum number of prefixes. The maximum is 2.

Please check this [ink](https://msdn.microsoft.com/en-IN/library/ff772782.aspx) about to permission on the server. — Rahul Dubey, Aug 10 '15 at 07:31
Okay, *now* your question is [a duplicate of this one](http://stackoverflow.com/questions/26119343/the-object-name-contains-more-than-the-maximum-number-of-prefixes-the-maximum-i). — AStopher, Aug 10 '15 at 07:31
This may not solve your issue, but I noticed your sample query uses brackets around each piece of the fully qualified object name: `[192.168.1.6].[Audit].[dbo].[tmpDTTransfer]` However, the error message does not show the brackets. Are you sure your production code is using brackets? — Dave Mason, Aug 12 '15 at 19:33

score 2 · Answer 1 · answered Aug 12 '15 at 18:59

The error you are getting is what you see if the linked server is not set up.

First ensure your linked server is added:

EXEC sp_addlinkedserver   
   @server=N'RemoteServer', 
   @srvproduct=N'',
   @provider=N'SQLNCLI', 
   @datasrc=N'192.168.1.6';

Set up your security with sp_addlinkedsrvlogin or with management studio on your linked server object found under "Server Objects" -> "Linked Servers".

Then you should be able to use:

select * 
into [RemoteServer].[Audit].[dbo].[tmpDTTransfer]
from [tmpDTTransfer]

This would be correct. A SQL server cannot just connect to another instance simply by an IP. You need to setup a linked server for this. — Iztoksson, Aug 15 '15 at 17:55

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

Rewrite your query as below :

SELECT * INTO tmpDTTransfer
FROM [sourceserver].[sourcedatabase].[dbo].[tmpDTTransfer]

References :

add Linked server

The object name contains more than the maximum number of prefixes. The maximum is 3

Select into statement where source is other database

edited May 23 '17 at 12:31

Community

1
1

answered Aug 12 '15 at 09:04

Tharif

13,794
9
55
77

ohh..kk will post some other options then – Tharif Aug 12 '15 at 09:08

score 0 · Answer 3 · answered Aug 19 '15 at 05:00

there are two method to get database table from one database to another one.
1.generate scipt of old database and execure it to with new database.
2.Get backup of your old database and and restrore it to new database

From the SQL Server Management Studio you can right click on your database and select:

Tasks -> Generate Scripts you may be choise to get some table from list of it.if you want some table then click on select special database object

Then simply proceed through the wizard. Make sure to set 'Script Data' to TRUE when prompted to choose the script options.

then open sql file that is generated and replace belove line USE [your old database name ] to USE [your new database name ] then execute this script file

error in linked server in sql server 2008 r2

3 Answers3