June 23, 2015 at 1:36 pm
When I save my output (from a query I ran) to a text file, there seems to be rows of spaces. Is there a way i can just kill off any spaces at the end of my query? Like rtrim or something?
June 23, 2015 at 1:41 pm
Post your query.
Tell us how you got the results to your text file
Gerald Britton, Pluralsight courses
June 23, 2015 at 1:49 pm
my query
DECLARE @Curr_Date DATETIME;
--Set @Curr_Date = CURRENT_TIMESTAMP;
set @Curr_Date = '2015-06-03 00:00:00.000'
SELECT RTrim(c.REFERENCE_NO) + '|', 'HBBCKGRND' + '|' AS Literal , 'Refund Check Sent', RTrim([DESCRIPTION]) + ', ' + LTrim([TRANS_NBR]) + ', ' + LTrim([BANK_CHK_AMT]) + ', ' + convert(char(10),[CHECK_DATE],101) + ', ' + 'Refund check sent to ' + rtrim(PAYEE_NAME) AS [Free Text] ,+ '||||'
FROM CBPAYMENT c
where c.CASH_CODE = 101 and convert(varchar(10), c.CHECK_DATE,101) = convert(varchar(10), @Curr_Date, 101)
order by c.CHECK_DATE desc
when i open the text file, there seems to be a bunch of blank rows.
June 23, 2015 at 2:10 pm
Since you are getting a lot of blank rows it seems like results are present but not being displayed. If I had to guess it is because there are logic problems in the strings that are being created in your columns. If any part of a string has an error the entire string will not get displayed.
Can you try the query removing the concatenation just returning each part and see if you get results? If that works then start building your columns to see where things start breaking.
Joie Andrew
"Since 1982"
June 23, 2015 at 2:31 pm
because you are appending fields to each other, if any value is NULL, when sent to a text file, you end up with blank rows.
i'd start with bulletproofing the query for nulls, somethignl like this?
DECLARE @Curr_Date DATETIME;
--Set @Curr_Date = CURRENT_TIMESTAMP;
SET @Curr_Date = '2015-06-03 00:00:00.000'
SELECT Rtrim(ISNULL(c.REFERENCE_NO,'')) + '|',
'HBBCKGRND' + '|' AS literal,
'Refund Check Sent',
Rtrim(ISNULL([DESCRIPTION],'')) + ', '
+ Ltrim(ISNULL([TRANS_NBR],'')) + ', '
+ Ltrim(ISNULL([BANK_CHK_AMT],'')) + ', '
+ CONVERT(CHAR(10), ISNULL([CHECK_DATE],''), 101) + ', '
+ 'Refund check sent to ' + Rtrim(ISNULL(PAYEE_NAME,'')) AS [free text],
+'||||'
FROM CBPAYMENT c
WHERE c.CASH_CODE = 101
AND CONVERT(VARCHAR(10), c.CHECK_DATE, 101) = CONVERT(VARCHAR(10), @Curr_Date, 101)
ORDER BY c.CHECK_DATE DESC
Lowell
June 25, 2015 at 10:08 am
thank you guys but I noticed when I opened up my query in notpad ++ it shows carriage returns (CR) in the file.
for example,my ouput is below (i added the CR myself so you can see what I see in notepad++) How do I make it in SQL managment studio to not tell me how many rows are showing? I also need to remove those extra CR at the end of my file.
|literal||free text|CR
1021276||HBBCKGRND||Refund Check Sent|4681419, 2100027, 14841.77, 06/03/2015, Refund check sent to Mutual of Omaha/State Farm We|||||
508119||HBBCKGRND||Refund Check Sent|4681418, 2100031, 145.50, 06/03/2015, Refund check sent to BLUE CROSS|||||
504675||HBBCKGRND||Refund Check Sent|4681415, 2100030, 369.95, 06/03/2015, Refund check sent to MICHAEL HELD|||||
1006306||HBBCKGRND||Refund Check Sent|4681414, 2100026, 46.43, 06/03/2015, Refund check sent to MARZENA PLEBANEK|||||
1098688||HBBCKGRND||Refund Check Sent|4681416, 2100028, 36.00, 06/03/2015, Refund check sent to MARYYYYYYYYYYYYYYYYYYYYYY FRAN|||||
470413||HBBCKGRND||Refund Check Sent|4681417, 2100029, 165.62, 06/03/2015, Refund check sent to AETNA|||||
CR
(6 row(s) affected)
CR
June 25, 2015 at 10:23 am
In SSMS go to "Query" > "Query Options" > "Advanced", select the checkbox "SET NOCOUNT" and click "OK".
Joie Andrew
"Since 1982"
June 25, 2015 at 10:51 am
beautiful! thank you.. 1 more question please..
In notepad++, there is anothor blank row with a CR..how do I remove that in sql studio?
|Literal||Free Text|
1021276||HBBCKGRND||Refund Check Sent|4681419, 2100027, 14841.77, 06/03/2015, Refund check sent to Mutual of Omaha/State Farm We|||||
508119||HBBCKGRND||Refund Check Sent|4681418, 2100031, 145.50, 06/03/2015, Refund check sent to BLUE CROSS|||||
504675||HBBCKGRND||Refund Check Sent|4681415, 2100030, 369.95, 06/03/2015, Refund check sent to MICHAEL HELD|||||
1006306||HBBCKGRND||Refund Check Sent|4681414, 2100026, 46.43, 06/03/2015, Refund check sent to MARZENA PLEBANEK|||||
1098688||HBBCKGRND||Refund Check Sent|4681416, 2100028, 36.00, 06/03/2015, Refund check sent to MARYYYYYYYYYYYYYYYYYYYYYY FRAN|||||
470413||HBBCKGRND||Refund Check Sent|4681417, 2100029, 165.62, 06/03/2015, Refund check sent to AETNA|||||
June 25, 2015 at 1:59 pm
I read about using the REPLACE command with char(13) AND char(10), but I dont know how to use this. Can anyone please help me?
June 25, 2015 at 2:10 pm
I read about using the REPLACE command with char(13) AND char(10), but I dont know how to use this. Can anyone please help me?
If your extra carriage return is before or after your result set and not in the row data itself I do not think using REPLACE in the query will help you.
Where in your file is the extra blank line? Also, what does the extra line matter? Does it break something?
Joie Andrew
"Since 1982"
June 25, 2015 at 2:28 pm
wallywizard (6/25/2015)
I read about using the REPLACE command with char(13) AND char(10), but I dont know how to use this. Can anyone please help me?
REPLACE(YourString, CHAR(10), '') would replace the linefeed characters with nothing. This would be just like deleting the linefeeds.
REPLACE(YourString, CHAR(13), '') would do the same for carriage returns.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply