1

I was tasked w/ getting data out of an obsolete document management system running on top of an instance of MS SQL Server.

Trying to export > 30000 documents through the app is not an option, as it shoots itself in the foot if I pick more than 10 items.

Trying to export the database (or individual tables) from SQL Server Management Studio renders a variety of errors, namely Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Destination - documents" (that happened when trying to export to either Excel or CSV).

Given the fickle state of the machine and my utter lack of knowledge in all things Microsoft I don't want to try and mess with the SQL Server installation by adding components.

The only way I managed to extract ANY data from tables is via bcp.
That, however, produced an other interesting complication. Having explicitly chosen TAB as a delimiter I get e.g. for a table w/ 61 (or 65 respectively) columns output that has a variety of column numbers for each row:

awk -F"\t" '{print NF}' folders.txt | sort | uniq -c | sort -n
    3 0
    5 1
    9 17
    9 45
15277 61


awk -F"\t" '{print NF}' documents.txt | sort | uniq -c | sort -n
    1 4
    1 62
   14 0
   24 1
   41 29
   41 37
31291 65

Does anyone have advice on how to get sensible results out of bcp?

Here's a sample query:

bcp "select * from objProd.dbo.documents" queryout z:\test.txt -c -T

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
1000 rows successfully bulk-copied to host-file. Total received: 12000
1000 rows successfully bulk-copied to host-file. Total received: 13000
1000 rows successfully bulk-copied to host-file. Total received: 14000
1000 rows successfully bulk-copied to host-file. Total received: 15000
1000 rows successfully bulk-copied to host-file. Total received: 16000
1000 rows successfully bulk-copied to host-file. Total received: 17000
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
1000 rows successfully bulk-copied to host-file. Total received: 20000
1000 rows successfully bulk-copied to host-file. Total received: 21000
1000 rows successfully bulk-copied to host-file. Total received: 22000
1000 rows successfully bulk-copied to host-file. Total received: 23000
1000 rows successfully bulk-copied to host-file. Total received: 24000
1000 rows successfully bulk-copied to host-file. Total received: 25000
1000 rows successfully bulk-copied to host-file. Total received: 26000
1000 rows successfully bulk-copied to host-file. Total received: 27000
1000 rows successfully bulk-copied to host-file. Total received: 28000
1000 rows successfully bulk-copied to host-file. Total received: 29000
1000 rows successfully bulk-copied to host-file. Total received: 30000
1000 rows successfully bulk-copied to host-file. Total received: 31000

31333 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 3094   Average : (10127.02 rows per sec.)

Note: This is my first time ever using SQL Server, I'm a Linux admin, and had good exposure to Postgres and some to MySQL. The state of the windows instance and the DB have not changed since long before I joined my current employer.

Improve this question
8
  • 1
    There is no 2003 version of SQL Server - we have SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014 and 2016 - which one is it'?
    – marc_s
    May 17, 2017 at 5:07
  • Oops, thanks, the Windows server it's on is 2003. SQL Server is 2005. Good catch.
    – tink
    May 17, 2017 at 5:09
  • Just to say, try with "import and export data" tool bundled with sql server management studio. It is easyer at the beginning and probably you can export in excel with no problem. Or in another database via odbc..
    – user_0
    May 17, 2017 at 6:14
  • What do you consider 'sensible results'?
    – Erik Darling
    May 17, 2017 at 8:30
  • @sp_BlitzErik: one where all exported rows have the same number of fields?
    – tink
    May 17, 2017 at 8:33

1 Answer 1

Reset to default
1

Right.

It would appear that free-form text fields in several rows (and varied columns) in two of the tables had embedded newline/carriage return characters, which then messed up the field-count (obviously).

I now am just exporting 5 (and 3, respectively) columns from those tables, and the problem is gone away. Feeling kind of silly after this.

Thanks everyone for trying to help.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.