June 17, 2010 at 5:21 am
Hi,
I'm trying to export a column which is a NVARCHAR(MAX) into a text file, the column is very large, I'm trying out a method using SQLCmd where I'm executing the following;
sqlcmd -S ServerName -d dbname -q "EXEC dbo.USPtestexport 1,1" -o C:\output.txt -y0 -YO
The issue is the file is truncated around 2/3 of the way through, I thought that the -y and -Y switches would resolve this, but sadly thats not the case, anyone have any idea's on the best way to do this.
Thanks,
Nic
June 17, 2010 at 5:35 am
June 17, 2010 at 5:39 am
I'd use SSIS, query parts of it and build a file by appending the seperate parts.
June 17, 2010 at 5:41 am
skcadavre (6/17/2010)
As I mentioned in the previous version of this thread - I'd use SSIS, query parts of it and build a file by appending the seperate parts.
Hi,
Sorry I'd not seen this, I'll have a play round with it, I have it all working in SSIS, it just takes a long time to run and I was just examining if there were better methods, I'm going to have a try at BCP, not really done to much with it, so it's a good excuse to learn something new.
Thank you both for the feedback.
Nic
June 17, 2010 at 5:59 am
NicHopper (6/17/2010)
skcadavre (6/17/2010)
As I mentioned in the previous version of this thread - I'd use SSIS, query parts of it and build a file by appending the seperate parts.Hi,
Sorry I'd not seen this, I'll have a play round with it, I have it all working in SSIS, it just takes a long time to run and I was just examining if there were better methods, I'm going to have a try at BCP, not really done to much with it, so it's a good excuse to learn something new.
Thank you both for the feedback.
Nic
When I read back what I typed I realised it sounded like I was being aggressive, which wasn't the intention, sorry.
Anyway, I'd look again at the SSIS. Assign sections of the SQL to a variable, then write out the variable to a file. You can experiment with buffer sizes, since different machine setups find different buffer sizes to be optimal.
June 17, 2010 at 10:06 am
Hey,
No you weren't being agressive, honestly.
I've got it working now, the BCP is by far the fastest method, in the SSIS (passing it to a variable and then writing that out) it takes an age to run, so I will revisit it and see if I can get it running quicker.
Many thanks for your help, wouldn't of thought of BCP otherwise (been a long day).
Nic
June 17, 2010 at 10:13 am
Well, well, well...
Actually, no surprise, BCP, old as Sybase (and also originated by them), is the fasterst way to get data out DB and back into it...
Cheers 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply