Having trouble exporting data from sql table to a text file

I am trying to export data from a sql table into a text file.  I am using fixed width, but it puts all the records on 1 line, how would I get it to do one line per record?  I am using the import export wizard.
LVL 1
CeleritasPrimeAsked:
Who is Participating?
 
DALSOMConnect With a Mentor Commented:
Hi,

See my answer's picture at this comment :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27505962.html

I hope this help you!!
Happy New Y.
Dalsom.
0
 
CeleritasPrimeAuthor Commented:
The actual export works, but after I export the file to text, I run and executable file that picks up the text file and imports the data into 2 tables in another database.  This isn't working because it is picking up the carriage return symbols as part of the data, and I am getting an error saying the record is invalid.  Also I notice that the export is put extra 15 extra spaces in my file, which is another thing that is throwing my fixed width off.
0
 
CeleritasPrimeAuthor Commented:
also here is a copy of the query that I am using to generate that data for the text file, and spacing is very essential.

SELECT DISTINCT
                      (RTrim(affiliateid) + Space(15-Len(dbo.TRIM(affiliateid))) + SPACE(2)
                      + RTrim(fedid) + Space(10-Len(dbo.TRIM(fedid))) + 'C' + Space(15) +
                       '1060' + Space(5) + 'Y' + Space(6) + '01/01/2005' + dbo.Trim('12/31/2400') + Space(28) + dbo.trim('00') +
                       Space(60) + RTrim(fullname) + Space(60-Len(dbo.TRIM(fullname))) +
                       Space(28)+'0000000000000000000' + SPACE(1)+ '100.00' + Space(92) + 'R' + 'Y' + RTrim(phystate) +
                       RTrim(phyzip) + Space(10-Len(dbo.TRIM(phyzip))) + Space(2) +
                        RTrim( (CASE WHEN attributeid = 'TMSC000834493' THEN thevalue END)) +
                        Space(60-Len(dbo.TRIM((CASE WHEN attributeid = 'TMSC000834493' THEN thevalue END)))) + Space(60) +
                         RTrim(phyaddr1) + Space(60-Len(dbo.TRIM(phyaddr1))) +
                         RTrim(phyaddr2) + Space(60-Len(dbo.TRIM(phyaddr2))) +
                          Space(60) + RTrim(phycity) + Space(20-Len(dbo.TRIM(phycity))) +
                          RTrim(phone) + Space(25-Len(dbo.TRIM(phone)))+ SPACE(1677)) as Report
FROM         dbo.dbo_entity INNER JOIN
                      dbo.dbo_provider ON dbo.dbo_entity.entid = dbo.dbo_provider.entityid INNER JOIN
                      dbo.dbo_providerattribute ON dbo.dbo_provider.provid = dbo.dbo_providerattribute.provid INNER JOIN
                      dbo.dbo_affiliation ON dbo.dbo_provider.provid = dbo.dbo_affiliation.affiliateid
WHERE     (dbo.dbo_entity.enttype = N'PROVIDER')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.