# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Linked Servers: SELECT * INTO doesn't work

## James May

I administer several servers.  My login is a SysAdmin on all servers with not only the same Name and Password, but also the same SID.  In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error:

The object name &#39;DB1.ogSerial.dbo.&#39; contains more than the maximum number of prefixes. The maximum is 2.

Here are the queries I&#39;ve tried (using DB3 as the host, DB1 as the linked server).  

SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB1.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM     MyDB.dbo.TestTable

----------


## Carl Federl

You are attempting to create the table in the linked server ?
It appears that SQL Server does not allow DDL versus the linked server.

Try this but do know if the statement will work:
SELECT * FROM
OPENQUERY ( &#39;DB1&#39;
, &#39;SELECT * INTO MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable&#39; ) 





------------
James May at 9/21/01 4:33:32 PM

I administer several servers.  My login is a SysAdmin on all servers with not only the same Name and Password, but also the same SID.  In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error:

The object name &#39;DB1.ogSerial.dbo.&#39; contains more than the maximum number of prefixes. The maximum is 2.

Here are the queries I&#39;ve tried (using DB3 as the host, DB1 as the linked server).  

SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB1.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM     MyDB.dbo.TestTable

----------


## Ivan

If select into does not, try select insert.

Ivan

------------
Carl Federl at 9/22/01 7:36:26 PM

You are attempting to create the table in the linked server ?
It appears that SQL Server does not allow DDL versus the linked server.

Try this but do know if the statement will work:
SELECT * FROM
OPENQUERY ( &#39;DB1&#39;
, &#39;SELECT * INTO MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable&#39; ) 





------------
James May at 9/21/01 4:33:32 PM

I administer several servers.  My login is a SysAdmin on all servers with not only the same Name and Password, but also the same SID.  In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error:

The object name &#39;DB1.ogSerial.dbo.&#39; contains more than the maximum number of prefixes. The maximum is 2.

Here are the queries I&#39;ve tried (using DB3 as the host, DB1 as the linked server).  

SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB1.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM     MyDB.dbo.TestTable

----------


## Mohammed.

If you are copying the data to db1 ....
run the following query on DB1 not on DB3...
SELECT * INTO MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable

Mohammed.
------------
Ivan at 9/24/01 12:16:27 PM


If select into does not, try select insert.

Ivan

------------
Carl Federl at 9/22/01 7:36:26 PM

You are attempting to create the table in the linked server ?
It appears that SQL Server does not allow DDL versus the linked server.

Try this but do know if the statement will work:
SELECT * FROM
OPENQUERY ( &#39;DB1&#39;
, &#39;SELECT * INTO MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable&#39; ) 





------------
James May at 9/21/01 4:33:32 PM

I administer several servers.  My login is a SysAdmin on all servers with not only the same Name and Password, but also the same SID.  In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error:

The object name &#39;DB1.ogSerial.dbo.&#39; contains more than the maximum number of prefixes. The maximum is 2.

Here are the queries I&#39;ve tried (using DB3 as the host, DB1 as the linked server).  

SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB1.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM     MyDB.dbo.TestTable

----------

