BCP and Bulk Insert to Linked Servers

[Ervs Sevilla via SQLMonster.com]

BCP and Bulk Insert to Linked Servers
Hi guys!

Heres my set up:
1) Im using Win2003 with MS SQL 2000
2) I have a linked server in SQL Server pointing to an MS Access DB.

Why MS Access? Gee, I dont know. The guy who owns it refused to update his
VB app and point it to SQL Server.

Anyway, I have 190,000 records in SQL Server that I wanted to dump and
insert it to MS Access.
I tried to use OPENQUERY but OLE DB provider choked and wont be able to
handle that much records. Sucks!
Moreover, DTS packages wont do the job. I tried it and it have the same
problem.
Now, I got one last option to go to. I EXPORTED SQL Server data to a text
file using BCP but my problem is how to IMPORT those data from the TEXT
file to my Linked Server that points to an MS Access.

This is what Im trying to do:

SQL Server Data ---> Text file ---> Linked Server (MS Access)
bcp LinkedServerName..MSAccess_TableName in Shares1_tmp.txt -c -T -t ',' -r
'\n'

SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).

Thank you and you guys have a nice day.

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Ervs Sevilla via SQLMonster.com (fo***@SQLMonster.com) writes:

Heres my set up:
1) Im using Win2003 with MS SQL 2000
2) I have a linked server in SQL Server pointing to an MS Access DB.

Why MS Access? Gee, I dont know. The guy who owns it refused to update his
VB app and point it to SQL Server.

Anyway, I have 190,000 records in SQL Server that I wanted to dump and
insert it to MS Access.
I tried to use OPENQUERY but OLE DB provider choked and wont be able to
handle that much records. Sucks!
Moreover, DTS packages wont do the job. I tried it and it have the same
problem.
Now, I got one last option to go to. I EXPORTED SQL Server data to a text
file using BCP but my problem is how to IMPORT those data from the TEXT
file to my Linked Server that points to an MS Access.

This is what Im trying to do:

SQL Server Data ---> Text file ---> Linked Server (MS Access)

This sounds like a dead end to me. Bulk insert to linked server is not
supported, as I recall. And last time I looked at it, at least the
other server was another SQL Server.

I would suggest that you inquire in an Access newsgroup for how to import
that data into Access.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Ervs Sevilla via SQLMonster.com]

Yah thats what I thought too because those option fields from BCP dont have
something for Linked Servers.

Do you have any other suggestions to copy and insert those 190,000 records
to MS Access?

Thank you for the reply.
I appreciate it.

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Ervs Sevilla via SQLMonster.com (fo***@SQLMonster.com) writes:

Do you have any other suggestions to copy and insert those 190,000 records
to MS Access?

To repeat myself: ask in a newsgroup devoted to Access. Maybe there
are some people here who knows Access, but I am certainly not one of
them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Simon Hayes]

"Ervs Sevilla via SQLMonster.com" <fo***@SQLMonster.com> wrote in message
news:41******************************@SQLMonster.c om...

Yah thats what I thought too because those option fields from BCP dont
have
something for Linked Servers.

Do you have any other suggestions to copy and insert those 190,000 records
to MS Access?

Thank you for the reply.
I appreciate it.

--
Message posted via http://www.sqlmonster.com

One thing to try would be to experiment with the batch size option for a DTS
Transform Data task. The OLE DB provider might not like handling 190,000
rows in a single insert, but if you do it in batches of 10,000 rows (or
whatever), it might work. However, that's pure speculation, and as Erland
says, you'll probably get better information on importing into Access in an
Access group.

Simon

[Ervs Sevilla via SQLMonster.com]

Thank you guys....
Ill post my prob in MS Access forum.

By the way, I did tried to insert 1,000 records at a time but again OLEDB
Jet 4.0 for MS Access choked.

I forgot to mentioned that the destination table in Access have 106 columns
thats why using OPENQUERY choked as well. The table is flat like a pan cake.
Moreover, theres another table that only have 54 columns/fields and I was
able to insert a total of 230,000 records.

--
Message posted via http://www.sqlmonster.com