October 10, 2007 at 3:59 pm
Hello,
I have a linked server and I can do select queries fine:
For example
Select * from linkedserver.mydatabase.dbo.mytable where column1 IS NULL
However the inserts don't work, but I may not be using the right syntax:
Example:
connecting to the local db, and issuing this query
Select *
INTo linkedserver.mydatabase.dbo.testtable
From
(Select top 10 *
From dbo.localtable)
generates the following error: Msg 117, Level 15, State 1, Line 3
The object name 'linkedserver.mydatabase.dbo.testtable contains more than the maximum number of prefixes. The maximum is 2.
Help is greatly appreciated
Thanks
KR
October 10, 2007 at 5:25 pm
I think you're running into a red herring - try this:
Select s.*
INTo linkedserver.mydatabase.dbo.testtable
From
(Select top 10 *
From dbo.localtable) as s
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 8:42 pm
Select s.*
INTo linkedserver.mydatabase.dbo.testtable
From
(Select top 10 *
From dbo.localtable) as s
this may help you
I will give another example use cte,becase we discuse sql2005
with cte as (
Select top 10 *
From dbo.localtable)
INTo linkedserver.mydatabase.dbo.testtable
from cte
October 11, 2007 at 8:39 am
I tried that and still got the same error.
October 11, 2007 at 8:48 am
I tried the cte version too and got this error:
Incorrect syntax near the keyword 'INTo'
I had posted this on another forum - lazydba and was told that you could not create tables through a linked server using Select Into, and that I would need to do this from the linked server with something like this
Select * into dbo.testtable from (select top 10 * from
your_local_server.local_db.dbo.local_table) - this I have used before successfully in other situations.
But this situation is such that it would be preferable to do a push, so if it is really the case that you cannot create a table on a linked server, then I have to do it another way.
Thanks
KR
October 11, 2007 at 8:58 am
i'm pretty sure you can't use select into on linked servers.
same as create proc/table etc -
you have to create the table with a direct connection on the target server first then use the insert command rather than select into
MVDBA
October 12, 2007 at 3:52 am
sorry wrong typing ,
try this.
with cte as (
Select top 10 *
From dbo.localtable)
select * from cte
INTo linkedserver.mydatabase.dbo.testtable
October 12, 2007 at 4:04 am
regardless of whether you use CTE or not, it's the INTO clause that is the problem.
i'm 99% positive that SELECT INTO does not allow 4 part naming
you have to Use INSERT INTO
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply