Bulk Insert to Linked Server Error

Question

Bulk Insert to Linked Server Error

pat.baggett

SSC-Addicted

Points: 442
More actions
April 30, 2010 at 10:02 am

#144473

When trying to use BULK INSERT to a linked server I discovered there is apparently a limitation that you can't BULK INSERT to a four-part object name (e.g. to a remote or linked server).
Msg 208, Level 16, State 82, Line 1
Invalid object name 'foo'.
The error message is a little misleading as I can easily insert/update/delete to or from the same object name. I have tried synonyms with no luck. Any suggestions or is this just not possible in SQL 2005? How about SQL 2008?
example code:
bulk insert [myserver].[mydb].dbo.[mytable] from '\\myfilepath\myfilename' with (fieldterminator = '|', rowterminator = '|', maxerrors = 1)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Here's the syntax definition from 2005. It does not support the 4 part naming convention. This came from BOL you could check on line for the 2008 version on MSDN. I'm guessing that it has not changed with 2008.

BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH ..............

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

pat.baggett SSC-Addicted Points: 442 More actions · Answer 2

Thanks John - not exactly the answer I was looking for - but I guess I'll have to approach this from a different way. Any idea why SQL would not support this?

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Most likely because BULK INSERT in the transact SQL method to invoke a BCP bulk copy so it is intended to run against the local instance. The bcp tool will allow you to bulk copy to a remote server. You can't run it against a linked server, but you can run it against a remote server. You may consider using bcp.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

helenambelo SSC Rookie Points: 27 More actions · Answer 4

You can do this with this command:

-- Local Server:

if OBJECT_ID('tempdb..#tmp1') IS NOT NULL drop table #tmp1

CREATE TABLE #tmp1 ( codigo BIGINT, descricao VARCHAR(100) )

-- Insert in a Temporary Table

BULK INSERT #tmp1 FROM 'C:\Documents and Settings\hb.VANTYX\Desktop\ExemplosFicheiros\hb_teste.txt'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR =';',

CODEPAGE = 'ACP'

);

-- Insert into the intended table:

INSERT INTO BERNOULLI.ACP_DB_03_8081.dbo.hb_teste

SELECT * FROM #tmp1