export data from sql server to ms access using BCP

Question

export data from sql server to ms access using BCP

Manuni Shah

SSC-Addicted

Points: 425
More actions
May 3, 2007 at 5:19 am

#69801

hi,
I want to export data from sql server to ms access using BCP utility. The data will be extracted using a query involving more than 1 tables.
Can you please help me out.
I am executing the following command from command prompt
bcp "select status_id, status_name, status_type_id from status" queryout "db1.mdb" -c -U"sa" -P"sa"
It creates db1.mdb but when trying to open the mdb file it gives error saying "Unrecognized database format 'C:\db1.mdb' "
Thanks & Regards
Manuni

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

Check books online. Search BCP and then look at "Copying Data Between Different Collations". You are using the -c option to specify a codepage but not identifying a codepage.

I would suggest removing the -c option and try again.

If that sitll fails then I would suggest that you need to create the Access Database manually the first time so that all the table structures are in place. Then everytime after that you should be able to use the BCP to "recreate" the export.

HTH

Regards,
Matt

Manuni Shah SSC-Addicted Points: 425 More actions · Answer 2

I tried removing -c option and doing it. but still it gives me the same result that is it still fails.

Also I tried creating ms-access database and table structures manually and then tried to execute BCP but that too also fails.

Please can you help me out

thanks

Matt Simmons SSCrazy Points: 2846 More actions · Answer 3

Couple of options and questions:

1. Create a new DTS to connect to the Access DB that you created.

2. Create a new DTS and a new Access DB.

Now for the questions:

1. What version of Access?

2. Are you using the Access Connector in the DTS.?

3. Does the SQLAgent/Account that runs the DTS Package have write access to the drive where the MDB is?

Regards,
Matt