CODEWITHSUNDEEP
sql-servercsvexport
Jim Michael
Jim Michael

Reputation: 11

Why are random characters being deleted from my SQL Server export?

I'm using MS SQL Server 2008 to generate a '~'delimited dataset. My query output is being saved as a csv file with this custom delimiter set in SQL Server settings. The problem is, random characters are being deleted.

If I run 'results to text', the output is fine. If, however, I run 'results to file', random characters are being replaced with a single space. E.g., one of my entries in one of my fields reads 'North', and this is fine in 'results to text' output, but appears as ' orth' in 'results to file'. Similarly, some of my delimiters have gone missing: e.g. '1~1~1~1~1~1' is coming through as '1~1 1~1~1~1'.

I would happily copy and paste the 'results to text' output into notepad and save as a csv myself, but unfortunately it won't let me copy more than a few lines (my dataset is large).

Desperate for ideas if anyone has any. Many thanks.

UPDATE: I cannot get my code to work in the Export Wizard and I do not have access to SSIS as I am working within a very strict firewall. Does anyone have any ideas on why the error is occurring in the first place?

Upvotes: 1

Views: 102

Answers (2)

DavidG
DavidG

Reputation: 119066

As mentioned in comments, using SSMS to export data is fraught with issues, usually things like truncating long strings and adding random carriage returns. The best option is to use am SSIS task, especially if you want to run this job more than once. However, sometimes it's just as simple to use the SQL Export Data Wizard. Right click on the database where your data is, select Tasks, then Export Data... and follow through the wizard.

If for some reason the SQL you are using is not being accepted, then you could put it into a stored procedure. For example, lets say the query you had was something very simple:

SELECT Column1, Column2, Column3
FROM MyTable

You can put this into a stored procedure like this:

CREATE PROCEDURE GetMyData
AS    

SELECT Column1, Column2, Column3
FROM MyTable

This means that the query is stored inside your database so you don't need an .sql file or to paste it into SSMS every time you need it.

And now the query you run is simply this:

EXEC GetMyData

Upvotes: 1

Joe C
Joe C

Reputation: 3993

I never use those results other than quick checking. As commented it is better to run the export wizard. I suggest going one step further and using the SSIS designer to create that package instead. It will be easier to maintain and build upon in the future.

Here is a good video tutorial on a site with other great SSIS stuff: https://www.youtube.com/watch?v=VCdPzdD-boQ

Here is a good non video tutorial: http://knowlton-group.com/using-ssis-to-export-data-to-flat-files/

One is for pipe delimited and the other for comma, but in both cases the delimiter can be changed to ~

Upvotes: 0

Related Questions