Bulk Insert to Linked Server Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Bulk Insert to Linked Server Error

Bulk Insert to Linked Server Error

Rate Topic

Display Mode

Topic Options

Author

Message

pat.baggett

Posted Friday, April 30, 2010 10:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 6:59 AM
Points: 4, Visits: 12

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)

Post #913857

John Rowan

Posted Friday, April 30, 2010 10:06 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

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 - by Jeff Moden

Post #913861

pat.baggett

Posted Friday, April 30, 2010 10:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 6:59 AM
Points: 4, Visits: 12

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?

Post #913903

John Rowan

Posted Friday, April 30, 2010 10:59 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

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 - by Jeff Moden

Post #913910

helenambelo

Posted Thursday, February 14, 2013 6:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 6:25 AM
Points: 1, Visits: 0

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

Post #1420022

« Prev Topic | Next Topic »

Permissions