The object name ‘x’ contains more than the maximum number of prefixes. The maximum is 2.
-
Posted on March 15, 2011 by Derek Dieter
-
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”]
SELECT *
INTO [remoteserver].RemoteDB.dbo.sysdtspackages
FROM MSDB.dbo.sysdtspackages
[/cc]
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
SELECT *
FROM MSDB.dbo.sysdtspackages
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Your answer is very helpful.
Thanks.
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.
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?
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.
Derek
