Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert to Linked Server Error Expand / Collapse
Author
Message
Posted Friday, April 30, 2010 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, April 30, 2010 10:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:04 PM
Points: 3,840, Visits: 3,843
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
Posted Friday, April 30, 2010 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, April 30, 2010 10:59 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:04 PM
Points: 3,840, Visits: 3,843
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
Posted Thursday, February 14, 2013 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse