Append 2nd result to first file output in osql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Append 2nd result to first file output in osql

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-07 : 17:14:17

How to create a batch file with osql that can run 2 store procedures and have the second store procedures results append to the first store procedures output file. Currently my batch flie looks like this.
the EdiTest2 is the second store procedures name.

osql -Sservername -ddatabasename -Usa -Pxxxxxx -h-1 -n -s* -w5000 -iC:\EdiTest.txt -oC:\editoutput.txt
C:
osql -Sservername -ddatabasename -Usa -Pxxxxxx -h-1 -n -s* -w5000 -iC:\EdiTest2.txt >>C:\editoutput.txt

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-07 : 17:53:31

EdiTest.txt would contain the T-SQL for both stored procedures. So just put the T-SQL code from EdiTest2.txt into EdiTest.txt. But your way works fine too. You don't need C: in between though. Just save both lines to a cmd file.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-08 : 09:29:17

By the way I did your way and it works but how can I close the gap between the first results and the second resutls in the out put file.If you see below my results you notice that between line 16(which is the first results) and line 17 (which is the second result) there is a row gap that I wanted to close up.Please advise. Thanks.

//STX12//850ABCDEF 000000000000000000000401080922 0401080922
0100NE000000000000000003704520021121
02
03
04
05ST ZZ
0655109998
07EFGHIJ
08
09
10
11
12
13
14
15
16

17236.00000000EA
18EXXL7997A

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-08 : 12:17:03

You'll have to write some code to interrogate the output file.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-08 : 12:24:51

Is it a batch file code or SQL code and Do you know the code?Can you give ne some tips

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-08 : 12:27:18

Personally, I would use VBScript to do it. But then again why do you care if there is a gap between the two result sets? If you are later importing this in, you can write code to remove it.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-08 : 13:13:07

Because this is a flat file that will import into an EDI software that will convert the flat file into EDI file. Thus,the EDI software is sensitive over character,space,and row, if not the conversion will fail. How about is there a way to run 2 select statement in a store procedure and brigde the gap in the output file results between the 2 select statements. I don't know VB script..

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-08 : 13:19:29

You could use a UNION to get the result sets together. You just need to have the data types be the same for the columns. If they aren't, CONVERT them to VARCHAR. Here is an example of the SELECT:

SELECT Column1, Column2
FROM Table1
UNION ALL
SELECT CONVERT(VARCHAR(50), ColumnA), ColumnB
FROM Table2

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-08 : 14:42:35

I tried Union already and is not going to work because I have a prefix of string in select the firstselect statement I show you my 3 select statements how they look like..perhaps you can give me some ideas. Thanks.

--first select statment --always a single row
select
'//STX12//850ABCDEF '+a.TranID+' '+a.Docno+' '+char(13)+
'0100NE'+a.PO_No+a.PODate+char(13)+
'02'+' '+' '+char(13)+
'03'+' '+char(13)+
'04'+' '+' '+' '+char(13)+
'05ST ZZ'+char(13)+
'06'+a.ShipTowhsecode+' '+char(13)+
'07'+'GHIJKLM '+char(13)+
'08'+' '+char(13)+
'09'+' '+char(13)+
'10'+' '+char(13)+
'11'+' '+char(13)+
'12'+' '+char(13)+
'13'+' '+char(13)+
'14'+' '+char(13)+
'15'+' '+char(13)+
'16'+' '+' '+' '
from A a, B b
where a.id_num = b.Countrow

--Second Select statement --usually more than 1 row
Select
'17'+AssgnID+OrderQty+'EA'+' '+' '+' '+char(13)+
'18'+ItemID+' '+char(13)+
'19'+' '+' '+char(13)+
'20'+' '+' '+char(13)+
'21'+' '+' '+char(13)+
'22'+' '+' '+char(13)+
'23'+' '+' '+char(13)+
'24'+' '+' '+char(13)+
'25'+' '+' '+char(13)+
'26'+' '+' '+char(13)+
'27'+' '+char(13)+
'28'+' '+' '+char(13)+
'29'+' '+' '
from C

--Third select statement --- empty spaces
select
'30'+' '+' '+char(13)+
'31'+' '

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-08 : 14:48:49

Please post the code that you wrote for UNION because it works fine for me.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-08 : 15:46:34

You know what I changed all my datatype to char and it works just like what you said.I want to thank you..for all your help..Now I have to fix the little thing to make it more accurate.. Thanks again.. below is my output file results

//STX12//850ABCDEFG 000000000000000000000401081538 0401081538
0100NE000000000000000003766020030131
02
03
04
05ST ZZ
0655109998
07HIJKLMN
08
09
10
11
12
13
14
15
16
171 283.00000000 EA
18ABCC77411
19
20
21
22
23
24
25
26
27
28
29 172 55.00000000 EA
18ABCC80005
19
20
21
22
23
24
25
26
27
28
29 30 31

k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources