When I try to BCP out the contents of Adventureworks in SQL Server 2005 with this command ...
I get the error
but only with the STORE table. The rest works fine. Whereas the identical command
works fine with the SQL Server 2008 instance. I've googled for an answer to this and all I can find is a lot of other very frustrated people posting on forums the same question and getting a variety of answers, none of which helps any of them. Is this another of Microsoft's 'stealth' bugs or is there a simple answer?
asked Nov 24 '11 at 04:39 AM in Default
I'm going to answer my own question, since I've now discovered what was wrong (it got worse with even more weird errors when trying to BCP in). if you get weird BCP errors, it is worth first doing a
This will tell you the version of BCP that you're using. It should be the latest one since it is (supposedly) backward-compatible. Every time Microsoft installs a new instance, it alters the PATH to get the directory of the utilities first in line for being scanned for the exe file. This assumes that you always install the latest version of SWL Server last. The problem here is that with SQL 2000 there were no such things as instances, so you install a later version first and then you might then pop in SQL Server 2000 for testing backward-compatibility.
If you decide to add a retro instance such as SQL 2000 or 2005 on a Dev machine, you will need to alter the PATH environment variable so that it reads the latest version first before going all historical. Otherwise you get wierd BCP errors like this.
I've been trawling the internet looking at the answers when this same problem comes up on the internet. I never found this silly and obvious answer given!
So beware. If you get BCP or BULK COPY errors on a machine that don't make any sense, check the version number of the BCP you're using first!
answered Nov 25 '11 at 04:09 AM