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.