The object name ‘x’ contains more than the maximum number of prefixes. The maximum is 2.

This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail:

[cc lang=”sql”]
INTO [remoteserver].RemoteDB.dbo.sysdtspackages
FROM MSDB.dbo.sysdtspackages

The trick is to create the table on the remote server, then perform an INSERT INTO.

[cc lang=”sql”]
INSERT INTO [remoteserver].RemoteDB.dbo.sysdtspackages
FROM MSDB.dbo.sysdtspackages

Inserting using “SELECT *” is generally bad practice. This is done for example purposes.

MAhendra kumhar 23 Jan 2013 at 9:18 am

Your answer is very helpful.

But I see your suggestion that we should not use “select *”.
I usual use it because it is faster then “Insert Into”.
Can you elaborate why we not use it.
Thanks again.

Darin 22 Jun 2011 at 7:57 am

Just curious, but why the “Never recommend doing an insert with select *”? When dealing with tables in certain situations, I find it much more maintainable to use a select * into construct than naming every individual field. For instance, when creating caching tables for intensive processing of table values before reintegrating those processed values back into the original table.

On the other hand, if I’m extracting specific data from a table into a temp table or somesuch, then a select * would just be a waste.

Am I missing some subtle SQL thing here?

Derek Dieter 22 Jun 2011 at 8:51 am

Hi Darin,

The problem comes in when another developer adds let’s say a varbinary(max) and populates it with a bunch of images. Now, every procedure you have that references the table will have performance issues. Another scenario may be if the number of columns grows very large. If that’s the case, you’ll be forced to go back into production code and specify the column names. In my opinion, the extra time to specify the columns is worth potential impact that could happen down the road.


Featured Articles

 Site Author

  • Thanks for visiting!