473,737 Members | 1,913 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_addlinkedser ver 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_ACCES S_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\Iqba l\iqbaldata.txt '

The bulk insert operation failed with the following error message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheck ForData
(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
7 12128
iqbal (iq******@hotma il.com) writes:
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_addlinkedser ver 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_ACCES S_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.


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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
iq******@hotmai l.com (iqbal) wrote in message news:<3b******* *************** ****@posting.go ogle.com>...
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_addlinkedser ver on both database servers. [cut]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheck ForData
(CheckforData() ).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

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
ig*****@yahoo.c om (Igor Raytsin) wrote in message news:<a7******* *************** ****@posting.go ogle.com>...
iq******@hotmai l.com (iqbal) wrote in message news:<3b******* *************** ****@posting.go ogle.com>...
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_addlinkedser ver on both database servers.

[cut]

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

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

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


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\Iqba l\iqbaldata.txt '
go

INSERT iqbal
SELECT *
FROM local_iqbal
go

HTH,
Igor
Jul 20 '05 #4
ig*****@yahoo.c om (Igor Raytsin) wrote in message news:<a7******* *************** ****@posting.go ogle.com>...
iq******@hotmai l.com (iqbal) wrote in message news:<3b******* *************** ****@posting.go ogle.com>...
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_addlinkedser ver on both database servers.

[cut]

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

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

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


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\Reven ueScience\iqbal data.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
iqbal (iq******@hotma il.com) writes:
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\Reven ueScience\iqbal data.txt' with
(FIRE_TRIGGERS)

It worked. The remote table was populated with the correct data.
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.
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.


The property SSPROP_FASTLOAD OPTIONS 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_FASTLOAD OPTIONS,
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_FASTLOAD OPTIONS to FIRE_TRIGGERS and set what happens.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
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

iq******@hotmai l.com (iqbal) wrote in message news:<3b******* *************** ****@posting.go ogle.com>...
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_addlinkedser ver 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_ACCES S_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\Iqba l\iqbaldata.txt '

The bulk insert operation failed with the following error message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheck ForData
(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 #7
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.

ig*****@yahoo.c om (Igor Raytsin) wrote in message news:<a7******* *************** ****@posting.go ogle.com>...
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

iq******@hotmai l.com (iqbal) wrote in message news:<3b******* *************** ****@posting.go ogle.com>...
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_addlinkedser ver 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_ACCES S_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\Iqba l\iqbaldata.txt '

The bulk insert operation failed with the following error message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheck ForData
(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 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
12356
by: pk | last post by:
Sorry for the piece-by-piece nature of this post, I moved it from a dormant group to this one and it was 3 separate posts in the other group. Anyway... I'm trying to bulk insert a text file of 10 columns into a table with 12. How can I specify which columns to insert to? I think format files are what I'm supposed to use, but I can't figure them out. I've also tried using a view, as was suggested on one of the many websites I've...
2
31766
by: Mike Husler | last post by:
We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp to our SQL Server machine file store to load large amounts for data using the BULK INSERT command. This is the command: BULK INSERT db..table FROM 'S:\path\filename.csv' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
9
4242
by: adi | last post by:
Hi all, Hope there is a quick fix for this: I am inserting data from one table to another on the same DB. The insert is pretty simple as in: insert into datatable(field1, field2, field3) select a1, a2, a3 from temptable...
16
17015
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then IF FOUND UPDATE <TABLE> SET .... <Values entered here> ELSE INSERT INTO <TABLE> VALUES <Values...
2
8605
by: Zarrin | last post by:
Hello, I read several articles of newsgroup about the bulk delete, and I found one way is to: -create a temporary table with all constraints of original table -insert rows to be retained into that temp table -drop constraints on original table -drop the original table -rename the temporary table
2
14656
by: Ted | last post by:
I have BULK INSERT T-SQL statements that work for all of my basic data tables except for one. Here is the problem statement (with obvious replacement of irrelevant path info): BULK INSERT igbconts_tmp FROM 'C:\\my_code_path\\IGBCONTS.txt' WITH (KEEPNULLS, FORMATFILE = 'C:\\my_data_path\\contacts.fmt');
2
15123
by: nano | last post by:
Does sql server have a way to handle errors in a sproc which would allow one to insert rows, ignoring rows which would create a duplicate key violation? I know if one loops one can handle the error on a row by row basis. But is there a way to skip the loop and do it as a bulk insert? It's easy to do in Access, but I'm curious to know if SQL Server proper can handle like this. I am guessing that a looping operation would be slower to...
0
2087
by: diane | last post by:
Just trying to upsize using VFP 9 with SQL 2005 using VFP remote views. One in particular keeps coming up and saying Cannot insert the value NULL into column, when I really don't think I am inserting a NULL value. Oddly sometimes it works but I don't know why. Can anyone suggest anything else I can try? Perhaps this is the wrong group I'm posting to - it was just the one that came up with other errors giving the same message, but none of...
3
3031
by: akdemirc | last post by:
i have a problem with large data import to a db in sql server.. Actually i have an application that collects data from an environment and dispatches this data to different csv files for sql server to bulk insert.. Each csv file corresponds a table in db.. After generating csv files on the application server(as a result csv files are remote files for the sql server), the dump process takes so much time that sometimes causes transaction log to...
0
8966
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9257
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9204
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8203
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6052
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4567
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3278
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2744
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2192
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.