SELECT...INTO to Linked Server

[Peter Hyssett]

Hi.
In Query Analyser, I tried to run the following:

SELECT * INTO [Linked.Server.05,1234].[destdb].[dbo].[tblname]
FROM [dbo].[sourcetable]

This gave the following error message:

Server: Msg 117, Level 15, State 1, Line 2
The object name 'Linked.Server.05,1234.destdb.dbo.' contains more than the
maximum number of prefixes. The maximum is 2.

Nothing I noticed in BOL suggests that SELECT...INTO does not work into a
linked server, or that the square brackets would be removed from the linked
server name.

Any suggestions how to get round this issue?


TIA.

[Tracy McKibben]

Post by Peter Hyssett
Hi.
SELECT * INTO [Linked.Server.05,1234].[destdb].[dbo].[tblname]
FROM [dbo].[sourcetable]
Server: Msg 117, Level 15, State 1, Line 2
The object name 'Linked.Server.05,1234.destdb.dbo.' contains more than the
maximum number of prefixes. The maximum is 2.
Nothing I noticed in BOL suggests that SELECT...INTO does not work into a
linked server, or that the square brackets would be removed from the linked
server name.
Any suggestions how to get round this issue?
TIA.

A couple of alternatives:

1. Create the table first, using CREATE TABLE, then INSERT INTO that
new table.

2. Create a linked server on the "remote" machine and turn the query
"around" - SELECT * INTO destdb.dbo.tblname FROM
newlinkedserver.sourcedb.dbo.sourcetable

[Peter Hyssett]

Thanks, Tracy. I did know those workarounds, but I am just lazy. I have to
copy the data to several linked servers, then run a Stored Procedure on each.
I was hoping to run a script in QA and edit it for each server rather than
going into all the servers.
--
Peter Hyssett

Post by Tracy McKibben

Post by Peter Hyssett
Hi.
SELECT * INTO [Linked.Server.05,1234].[destdb].[dbo].[tblname]
FROM [dbo].[sourcetable]
Server: Msg 117, Level 15, State 1, Line 2
The object name 'Linked.Server.05,1234.destdb.dbo.' contains more than the
maximum number of prefixes. The maximum is 2.
Nothing I noticed in BOL suggests that SELECT...INTO does not work into a
linked server, or that the square brackets would be removed from the linked
server name.
Any suggestions how to get round this issue?
TIA.

1. Create the table first, using CREATE TABLE, then INSERT INTO that
new table.
2. Create a linked server on the "remote" machine and turn the query
"around" - SELECT * INTO destdb.dbo.tblname FROM
newlinkedserver.sourcedb.dbo.sourcetable
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

[Razvan Socol]

Hi Peter,

The fact that the error message says that "the maximum number of
prefixes allowed in this context is 2", means that in a SELECT INTO
statement you can only specify the destination database, not the
destination server (which must be the current server).

Possible solutions:
a) create the table normally and use a "INSERT INTO ... SELECT ..."
b) if the linked server is also an SQL Server, run the query from the
other server, after making a linked server to the first (i.e. "SELECT *
INTO tblname FROM thefirstserver.db.dbo.sourcetable").

Razvan

Post by Peter Hyssett
Hi.
SELECT * INTO [Linked.Server.05,1234].[destdb].[dbo].[tblname]
FROM [dbo].[sourcetable]
Server: Msg 117, Level 15, State 1, Line 2
The object name 'Linked.Server.05,1234.destdb.dbo.' contains more than the
maximum number of prefixes. The maximum is 2.
Nothing I noticed in BOL suggests that SELECT...INTO does not work into a
linked server, or that the square brackets would be removed from the linked
server name.
Any suggestions how to get round this issue?
TIA.
--
Peter Hyssett

[AlterEgo]

My suspicion is that the periods in your server names are causing the parser
grief. Although SQL Server is very forgiving with naming restrictions, I
puke when I see special characters or spaces in object names, or reserved
words as object names.

-- Bill

Post by Peter Hyssett
Hi.
SELECT * INTO [Linked.Server.05,1234].[destdb].[dbo].[tblname]
FROM [dbo].[sourcetable]
Server: Msg 117, Level 15, State 1, Line 2
The object name 'Linked.Server.05,1234.destdb.dbo.' contains more than the
maximum number of prefixes. The maximum is 2.
Nothing I noticed in BOL suggests that SELECT...INTO does not work into a
linked server, or that the square brackets would be removed from the linked
server name.
Any suggestions how to get round this issue?
TIA.
--
Peter Hyssett