SQL - extra carriage return in output file

[md67]

I'm getting a carriage return in my output file after each of my select statements and I don't want the carriage return.

c:\Illinois_temp.sql is basically a command file that contains the following:
----------------------------------------------------------
set nocount on
select hdr from Illinois_Cig_Return where Illinois_CigReturn.row = 1
select records from Illinois_Cig_Return where not (Illinois_Cig_Return.records is null) order by Illinois_Cig_Return.row
select eof from Illinois_Cig_Return where not (Illinois_Cig_Return.eof is null)
-----------------------------------------------------------
The first select statement gets one record. The second select statement will get many records and the third select statement gets one record. My output which is below has a carriage return (and possibly a line feed) after each select statement has been processed and I don't want it there.

I run a query that contains these 2 statements and uses the c:\Illinois_temp.sql file:

set @cmd = 'osql -i"c:\Illinois_temp.sql" -E -s -h-1 -d"Order" -w641 -n ' +
'-o' +
@folder +
@file
exec @cmdshell_result = master..xp_cmdshell @cmd,no_output


-----------------------------------------------------------

My output file looks like this:

*************HDR19E18RC6A M

2020000022124500
3030000122124500
3600000122124500

*************EOF00000016


Any suggestions?

 

[link9]

What you need is each query to be a column, rather than a row, which is how you have it. If you group each in ()'s and alias them, then it should work:

set nocount on
SELECT
(select hdr from Illinois_Cig_Return where Illinois_CigReturn.row = 1) AS col1,
(select records from Illinois_Cig_Return where not (Illinois_Cig_Return.records is null) order by Illinois_Cig_Return.row) AS col2,
(select eof from Illinois_Cig_Return where not (Illinois_Cig_Return.eof is null)) AS col3

Hope you don't have to modify your query very often, though... not exactly a very flexible one.


-paul

The answer to getting answered -- faq855-2992

 

[md67]

Thanks for the response. I tried and and this is the error I get:

Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My first and third selects get one record but by second select can get several records.

Thanks!

 

[link9]

You're probably going to be better off, then, asking the people over in the SQL Server forum if they know how to do this.

I wouldn't know how to do it in your case.

-paul

The answer to getting answered -- faq855-2992