BCP in SQL 2000

[jschaddock]

Hello,

I am currently working migrating an application from SQL 7 to 2000. I'm testing out a scheduled job which imports a file using BCP. The original job on the current SQL 7 Server runs a step that uses an operating system command of:

bcp new_reference_data_test..exchange_rates in etc...

however, when running this on our new SQL 2000 server it does not work. I know that there are no problems with the code as it works when running the step as a Transact-SQL script inside xp_cmdshell.

I'm looking at the configuration of the SQL 2000 server trying to figure out why I can't run this code as an operating system command but so far I can't spot anything. Can anyone help? Is there a system stored procedure or option to allow operating system commands in SQL that I am missing?

Thanks

J

 

[mrdenny]

Have you checked the basics, path statements for the SQL Accounts, etc?

If should run just as before. What error message if any are you getting back?

Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.

 

[jschaddock]

Figured it out.

Although I know that the select into/bulk-copy option has been removed at database level on SQL 2000, I didn't know that it has been added in at user login level instead. Just found the new option to make a user a 'bulk insert administrator' under the server roles option (damn training course didn't tell me about this!). It works fine with this option selected, clearly being a system administrator is not enough in SQL 2000

Thanks anyway

J

 

[mrdenny]

no prob. Permissions were going to be my next question.



Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.