I need to select more than 300 columns out of a SQL server database into a flat file using pipeline as delimiter. Some of these columns have large text content. When testing the SQL query from DOS batch, I noticed a cartridge return character is added after 65536 characters and it breaks one line into two. Because of this, our data mart team cannot load the record into their reporting database. Is there a way to output query results more than 65536 bytes?
Just to confirm, are you using SQLCMD? And what version of SQL server are you on?
Yes, I use SQLCMD in DOS batch to execute the sql script. Data from all columns get extracted into flat text file. But for any record exceeding 65536 bytes, a cartridge return is added after 65536 bytes and break the record into two lines. That's the problem.
SQLCMD probably not the right tool for exporting data. You can use -w to increase column width, but it has a maximum value of 65535 (given your description you are probably already doing this). -y 0 is an alternative, but that will space pad everything massively, and it still has a limit (1 MB I think?)
We built a little program in VBasic, or similar, to handle raw output to some sort of delimited file, but SSIS or similar might be a better route to go down.
I presume you are sure that pipeline characters do not exist anywhere in the data? We get people on our website registering as ||||||||@something.com presumably thinking it will stop us trying to flog their email address to other companies ...
Yes, I already used -y 0 and -w 65535 in my SQLCMD. We also removed the pipeline character (|) from all text columns before writing into flat files.
Is there any reason you can't use bcp to generate the file?
No specific reason not to use bcp, just never tried it. Will take a look at it. Thanks.
In our queries, we used custom function to remove HTML tags, special characters such as new line, cartridge return, and pipeline. We also format date and dollar amount to certain required format. We have to use pipeline as column delimiter. So, can we still do the same with bcp utility?
I don't think you can ... I think TAB is the only option (using -w parameter from memory)
For the removal of HTML tags etc. we point the export tool at either a Stored Procedure or a VIEW as we find that "adjusting" the code/query is easier if we just have to fix SProc/VIEW in the database, rather than having to fiddle about with a text string in a command line in a BATCH file or similar.
I have no knowledge of SSIS, so it may do all you need, but to "get what we want" in the past we've knocked up a 10 line VBasic, or similar, program
Further thought, can you use a Format File with BCP? Pretty sure that lets you specify a column delimiter. My negative on that would be that it needs to describe all columns and, for us, our projects tend to involve adding / changing / swapping columns, so our tool just does "Export all columns I've given"
Had a quick Google ... two routes appear to be using PowerShell (particularly if you have SQL2012 or higher) or using BCP to get TAB delimited output and then piping that through something to cover the TABs to PIPEs (assuming you don't have TABs in your data to start with, or you could REPLACE them on the way out of the DB)
The PowerShell route looks heavy to my eyes, particularly for anyone not very familiar with it (which includes me!)
And I completely missed the "-t" Field Terminator parameter in BCP Help Screen. That might well do exactly what you want.
Looks like BCP does not take sql file as input. We have a huge sql file. We have to either create a view or temp table from the sql file and then use BCP. Will take a look at PowerShell. Hope that works.
My preference would be to use a VIEW (or if the logic is complex, an SProc) anyway. IME Script Files have a habit of getting out of date (in the one location where they need to be on the current version!) and causing a hard-to-spot error. The VIEW lives within the database, so if different versions are needed for different databases (DEV, TEST, PRODUCTION etc. or Client1, Client2) then the VIEW in the specific database will be fit-for-purpose. But that may not suit your Shop of course ...