Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am exporting a table to a TSV file using sqlcmd, and I am running into issues. The table has 16+ million rows and about 55 columns.

The problem is that it does not export the full table, but seems to stop randomly at various points (i am guessing a timeout?) Each time a different number of rows are exported and each time the file is of a slightly different size (indicating that I am not hitting any row or size limit).

I am not using any timeout switch (meaning the default of "as long as it takes" is used).

Here is my command (with most columns removed for simplification and illustration purposes):

sqlcmd -U myUsername -P myPassword -S SERVERNAME -d "DBNAME" -Q "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" -o "OUTPUT_FILE.txt" -h-1 -s"|" -W

I wonder if it could have something to do with timeouts or the use of ISNULL() on all of the columns (although when i run the query in sql server management studio I get the correct number of rows returned e.g. 16 million + )?

Again, I get about 4-8 million rows each time, but never the full amount. I am in a sql server 2k5 db, and running sqlcmd from a remote machine with sql server 2k8.

share|improve this question

3 Answers 3

You'll probably want to do this with bcp, which is intended for this kind of thing. It would be something like this:

bcp "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" queryout OUTPUT_FILE.txt -c -S <servername> -U <login_name> -P <password>

Replace -c with -w if you're dealing with nvarchar/nchar, and want Unicode output.

share|improve this answer
    
+1 for suggesting bcp. I would suggest using -n to bcp out in Native Format which is very fast compared to regular bcp. You wont be able to see the contents of the file as they are in binary, so there is no conversion involved and it will cut down your time as well. –  Kin Jul 11 '13 at 14:59

What @db2 said. If that doesn't work, try segmenting your data. If you have an integer ID field, you could add

 WHERE ID % @NumSegments = @SegmentNumber

Loop for all values of @SegmentNumber from zero to one less than the number of segments, and you should get all records. Microsoft SQL's implementation of modulus only returns positive numbers, unlike some (http://stackoverflow.com/questions/4412179/best-way-to-make-javas-modulus-behave-like-it-should-with-negative-numbers/4412200#4412200) languages.

share|improve this answer

Might consider using Invoke-Sqlcmd since you are working from a machine with SQL 2008 installed. You do not get the column separator -s option as sqlcmd does, but within PowerShell you could export it to XML or CSV.

Basic syntax for your query would be:


$q = @"
SELECT ROW_KEY, 
   ISNULL(CARRIER_ID, ''),
   ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),
   ISNULL(HEIGHT, ''),
   ISNULL(RESIDENTIAL_CHARGE, '') 
FROM MYTABLE
"@

Invoke-Sqlcmd -ServerInstance MyServer -Database MyDatabase -Query $q -Username  -
Password  | Export-Csv MyFile.txt

Then if you absolutely needed to keep a particular separator between the columns you could just modify your query a bit:


SELECT ROW_KEY + '|' + 
   ISNULL(CARRIER_ID, '') + '|' +
   ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),'') + '|' +
   ISNULL(HEIGHT, '') + '|' +
   ISNULL(RESIDENTIAL_CHARGE, '')
FROM MYTABLE
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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