Bulk Insert Into Views That Select From Table On Remote Server

Hi all,

We have an application through which we are bulk inserting rows into a
view. The definition of the view is such that it selects columns from
a table on a remote server. I have added the servers using
sp_addlinkedserver on both database servers.

When I call the Commit API of oledb I get the following error:

Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:
SqlDumpExceptio
nHandler: Process 66 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATI
ON. SQL Server is terminating this process.

I would like to know if we can bulk insert rows into a view that
accesses a table on the remote server using the "bulk insert" or bcp
command. I tried a small test through SQL Query Analyser to use "bulk
insert" on a such a view.

The test that I performed was the following:

On database server 1 :

create table iqbal (var1 int, var2 int)

On database server 2 (remote server):

create view iqbal as select var1,var2 from
[DBServer1].[SomeDB].[dbo].[iqbal]

set xact_abort on
bulk insert iqbal from '\MachineIqbaliqbaldata.txt'

The bulk insert operation failed with the following error message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

The file iqbaldata.txt contents were :
11
22
33


If the table that the view references is on the same server then we
are able to bulk insert successfully.


Is there a way by which I should be able to bulk insert rows into a
view that selects from a table on a remote server. If not then could
anyone suggest a workaround. I would actually like to know some
workaround to get the code working using OLEDB. Due to unavoidable
reasons I cannot output the records to the file and then use bcp to
bulk insert the records in the remote table. I need to have some way
of doing it using OLEDB.

Thanks in advance
Iqbal