SQLCMD data truncated

Question

SQLCMD data truncated

Lonely Rogue

SSCrazy

Points: 2579
More actions
June 17, 2011 at 5:51 am

#242337

Hi to All ...
I use this below SQLCMD in the DOS Prompt to get the data into a output file. But, the data in a column is getting truncated and I found the swithces "-y display_width" & ":XML ON" on avoiding this truncate.
SQLCMD -E -W -u -Q "SET NOCOUNT ON SELECT * FROM ThisTable" -o "C:\RawOutputFilePath.txt" -s "|"
My questions on usage of these switches are -
1. In BOL, only value for -y has been shown as zero which truncates it to 1MB or 256 characters. Any way to override this or do we have any other value for this switch that prevents this truncation?
2. Same BOL shows work around by using ":XML ON", but how/where do I write this in DOS Prompt. Also, if this generates a XML output of the table data...I got to parse it through the XML queries of T-SQL.. right??
[font="Comic Sans MS"]In 'thoughts',[/font]
Lonely Rogue
--In 'thoughts'...
Lonely Rogue

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

SQLCMD is not made to export a large amount of data, so there are no efficient or easy way to get around some of the limitations.

Use BCP.exe if you need to export a lot of stuffs.

Use reporting services if you need to format it in an advanced way.

If it's still not enough, make a .net application to do it.

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 2

Thanks for the reply...

and any help on explaning about those switches.

Also, I should have set some background why I chose this approach... ok, here it is..

I need to create a app that accepts names of Database, Table and destination File, implying dynamic leaving no chance for creating schema structure. So, using SQLCMD I never have to bother to which table I am connecting to... I just execute the query and tap the output in a file.

So, now regarding building of .Net app.. Can we build such versatile app in this... I remember it needs Data Set to be prepared ( ain't expert.. just a beginner there).. meaning we need to know the schema details... so where do I have a chance to accept parameters.. And same is with SSIS also.

Any offering here... please...

In 'thoughts'....

Lonely Rogue.

--In 'thoughts'...
Lonely Rogue

Oliiii SSCertifiable Points: 5328 More actions · Answer 3

You don't need to define the dataset in advance in a .net application (you can if you want though).

You can pretty much send any query to any databases and output the result to a file.

Create your own SqlDataReader instead of using the wizards and fill it with any SqlCommand you want.

From there you can use the SqlDataReader object to count the number of columns and output each into a file (someone probably already made a function to export any DataReader to a csv though).

If you are a beginner with .net that's a good exercise to get you started 🙂

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Lonely Rogue (7/15/2011)
Thanks for the reply...
and any help on explaning about those switches.
Also, I should have set some background why I chose this approach... ok, here it is..
I need to create a app that accepts names of Database, Table and destination File, implying dynamic leaving no chance for creating schema structure. So, using SQLCMD I never have to bother to which table I am connecting to... I just execute the query and tap the output in a file.
So, now regarding building of .Net app.. Can we build such versatile app in this... I remember it needs Data Set to be prepared ( ain't expert.. just a beginner there).. meaning we need to know the schema details... so where do I have a chance to accept parameters.. And same is with SSIS also.
Any offering here... please...
In 'thoughts'....
Lonely Rogue.

The -y and -Y switches are supposed to be followed by a number representing the max width for the various datatypes they each represent. They should probably be used in conjuction with the -w switch which limits the overall width and defaults to only 80 characters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 5

Sure.. I will try this with .Net ..but being a SQL Server habitat, I thought of playing with it..

Thanks again 🙂

--In 'thoughts'...
Lonely Rogue