Bulk insert into views that select from table on remote server

iqbal

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 '\\Machine\Iqbal\iqbaldata.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 :
1 1
2 2
3 3


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

Jul 20 '05 #1

Erland Sommarskog

iqbal (iqbalk99@hotmail.com) writes:[color=blue]
> 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.[/color]

My initial reaction would be that "this is not possible", since it
just sounds far out. Then again, would that be a case, you should
get a civilized error message.

What I can to, is to use my contacts at Microsoft, to inquire the
status. But I would not really expect any positive answer.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

Igor Raytsin

iqbalk99@hotmail.com (iqbal) wrote in message news:<3b17279d.0408170541.543a3609@posting.google. com>...[color=blue]
> 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.[/color]
[cut][color=blue]
>
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.[/color]


Iqbal,
Do you have your server enabled for data access?
You can run

EXEC sp_serveroption 'DBServer1', 'data access', 'ON'


HTH,
Igor

Jul 20 '05 #3

Igor Raytsin

igorray@yahoo.com (Igor Raytsin) wrote in message news:<a72f945c.0408171938.76d91a08@posting.google. com>...[color=blue]
> iqbalk99@hotmail.com (iqbal) wrote in message news:<3b17279d.0408170541.543a3609@posting.google. com>...[color=green]
> > 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.[/color]
> [cut][color=green]
> >
> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> > (CheckforData()).
> > Server: Msg 11, Level 16, State 1, Line 0
> > General network error. Check your network documentation.[/color]
>
>
> Iqbal,
> Do you have your server enabled for data access?
> You can run
>
> EXEC sp_serveroption 'DBServer1', 'data access', 'ON'
>
>
> HTH,
> Igor[/color]

Didn't work :(


But what you can do is

SELECT * INTO local_iqbal
FROM iqbal
where 1=2
go

bulk insert local_iqbal from '\\Machine\Iqbal\iqbaldata.txt'
go

INSERT iqbal
SELECT *
FROM local_iqbal
go

HTH,
Igor

Jul 20 '05 #4

iqbal

igorray@yahoo.com (Igor Raytsin) wrote in message news:<a72f945c.0408171938.76d91a08@posting.google. com>...[color=blue]
> iqbalk99@hotmail.com (iqbal) wrote in message news:<3b17279d.0408170541.543a3609@posting.google. com>...[color=green]
> > 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.[/color]
> [cut][color=green]
> >
> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> > (CheckforData()).
> > Server: Msg 11, Level 16, State 1, Line 0
> > General network error. Check your network documentation.[/color]
>
>
> Iqbal,
> Do you have your server enabled for data access?
> You can run
>
> EXEC sp_serveroption 'DBServer1', 'data access', 'ON'
>
>
> HTH,
> Igor[/color]

Hi Igor,

I executed the "EXEC sp_serveroption 'ServerName', 'data access',
'ON'" command but I still the same error message.

As a work-around for running "bulk insert" command successfully I
created an "instead of insert" trigger on the view that inserts the
data into the table on the remote server. The trigger was:

create trigger iqbal_trig on iqbal instead of insert as
begin
insert into [ps0070].[bl001].[dbo].iqbal select * from inserted
end


I then executed the "bulk insert" command giving it the hint as
follows:

set xact_abort on
bulk insert iqbal from '\\ps0070\RevenueScience\iqbaldata.txt' with
(FIRE_TRIGGERS)

It worked. The remote table was populated with the correct data.

Is there a way by which I can provide hints while bulk inserting using
oledb? I want to achieve this behaviour in the application that uses
oledb. Does oledb by default execute triggers on the table/view by
default? I'm sure "bulk insert" command does not do it by default.

Thanks,
Iqbal

Jul 20 '05 #5

Erland Sommarskog

iqbal (iqbalk99@hotmail.com) writes:[color=blue]
> As a work-around for running "bulk insert" command successfully I
> created an "instead of insert" trigger on the view that inserts the
> data into the table on the remote server. The trigger was:
>
> create trigger iqbal_trig on iqbal instead of insert as
> begin
> insert into [ps0070].[bl001].[dbo].iqbal select * from inserted
> end
>
>
> I then executed the "bulk insert" command giving it the hint as
> follows:
>
> set xact_abort on
> bulk insert iqbal from '\\ps0070\RevenueScience\iqbaldata.txt' with
> (FIRE_TRIGGERS)
>
> It worked. The remote table was populated with the correct data.[/color]

Great to hear that got it working. Now, it does not sound really
performant, but if it was performance you were after, you should
probably bulk insert into the remote table directly.
[color=blue]
> Is there a way by which I can provide hints while bulk inserting using
> oledb? I want to achieve this behaviour in the application that uses
> oledb. Does oledb by default execute triggers on the table/view by
> default? I'm sure "bulk insert" command does not do it by default.[/color]

The property SSPROP_FASTLOADOPTIONS permits you specify various options
for BULK INSERT. Books Online starts the description with saying:

Description: This property is the same as the -h "hint[,...n]" option
of the bcp utility. The following string(s) can be used as option(s) in
the bulk copying of data into a table.

So far, so good, because FIRE_TRIGGERS is among those hints. But! On
the page IRowsetFastLoad Rowsets where I found SSPROP_FASTLOADOPTIONS,
all hints but FIRE_TRIGGERS are there. My guess is that this is a
documentation error, so I would recommend you to try to set
SSPROP_FASTLOADOPTIONS to FIRE_TRIGGERS and set what happens.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #6

Igor Raytsin

It seems like a bug in MS SQL 2000 (or MS DTC?).

I was able to that (bulk insert into view which points to a table on
linked server) from SQL 7 to SQL 2000 without any problem.


Igor

iqbalk99@hotmail.com (iqbal) wrote in message news:<3b17279d.0408170541.543a3609@posting.google. com>...[color=blue]
> 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 '\\Machine\Iqbal\iqbaldata.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 :
> 1 1
> 2 2
> 3 3
>
>
> 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[/color]

Jul 20 '05 #7

iqbal

To resolve the issue I have bulk inserted rows into a temporary table
on the server. I then insert these rows into the view (that selects
from a remote table) as was also suggested by Igor.

This workaround is working fine. Though it is not a highly efficient
solution.

igorray@yahoo.com (Igor Raytsin) wrote in message news:<a72f945c.0408191422.6317f81c@posting.google. com>...[color=blue]
> It seems like a bug in MS SQL 2000 (or MS DTC?).
>
> I was able to that (bulk insert into view which points to a table on
> linked server) from SQL 7 to SQL 2000 without any problem.
>
>
> Igor
>
> iqbalk99@hotmail.com (iqbal) wrote in message news:<3b17279d.0408170541.543a3609@posting.google. com>...[color=green]
> > 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 '\\Machine\Iqbal\iqbaldata.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 :
> > 1 1
> > 2 2
> > 3 3
> >
> >
> > 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[/color][/color]

Jul 20 '05 #8