BCP and Bulk Insert to Linked Servers

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

re: BCP and Bulk Insert to Linked Servers

Ervs Sevilla via SQLMonster.com (forum@SQLMonster.com) writes:[color=blue]
> 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)[/color]

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, esquel@sommarskog.se

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

re: BCP and Bulk Insert to Linked Servers

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

re: BCP and Bulk Insert to Linked Servers

Ervs Sevilla via SQLMonster.com (forum@SQLMonster.com) writes:[color=blue]
> Do you have any other suggestions to copy and insert those 190,000 records
> to MS Access?[/color]

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, esquel@sommarskog.se

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

re: BCP and Bulk Insert to Linked Servers

"Ervs Sevilla via SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:41fb40a4015744f5bee5bfdc03052973@SQLMonster.c om...[color=blue]
> 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[/color]

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

re: BCP and Bulk Insert to Linked Servers

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