Bash Script to execute sql store procedure/output to file

what product is being used?  microsoft sql server?  mysql? postgresql?

what linux distribution and version?

There are different ways to access SQL server, much depends on what you have installed.

say mysql:

mysql database <SQLscript.sql >TheOutputfile.txt

Select allOpen in new window

say postgresql:

psql dbname <SQLscript.sql >TheOutput.txt

Select allOpen in new window

in general if you want to capture the output use 

> to send to a file

>> to append to a file

| to send to another program.       f.e.  mysql dbname <whatever.sql | less

If you want to capture in in a shell variable:

VAR=$( psql dbname <whatever.script )

@Seth, using  microsoft sql server, and linux redhat:enterprise_linux:7.7. 

@noci, I will need to output the file in a specific format. Does that part all need done in the sql procedure?

What is the output of the stored procedure?
What is different from that output and the actual output you need?

Sample data end expected results would help a lot here.

First, make sure you have sqlcmd installed on your RHEL server.

You can use a system sp to practice your command or script, and then replace the system sp with your user sp.

Here is an example using system sp_who2 for you to get started:

at command line:

sqlcmd -S <hostname> -U <username> -P <password> -Q <exec sp_who2>

Or put the line into a shell script as:

#!/bin/bash

sqlcmd -S <hostname> -U <username> -P <password> -Q <exec sp_who2> 

Note, you do not have to run the command from localhost, it can run remotely if FW is open.

If you do not have sqlcmd, you would need to use an intermediary, perl or php or simialr with PDO
as scripts to connect to the SQL and run the commands to then be output back.
bash might have been used as an inclusive create a script to pull data from SQL.

Thanks guys. I am out sick today. I will get back to this hopefully monday. Hows it going slightwv? You've helped me out quite a bit in the past. Right now I am just brainstorming. Trying to find best methods here. I should have more file specs and sample data next week

get Well/Feel Better

Redirecting output toa  file ( > / >> ) does require you to have the right makeup in the SQL script.

If the SQL script output raw columns, csv etc..., then you can use a pipeline with formatting interspersed.

mysql <script.sql db | formatting-script > output.file

Select allOpen in new window

Ok so this may be out of scope for the question I posed. If so let me know and I'll open another question for it. 

Putting the task to output the data aside for the moment, I am working on constructing the data in sql and running into a issue. 

I'll try to simplify this the best I can. So I have 4 tables. Header, Item, Tendor, WebOrder

Each table has an orderID and a record field. Example below for 1 order: 

What I need to do is combine the HeaderRecord with all the ItemRecords, and then the TendorRecord and finally the WeborderRecord. Final output shown at the bottom. 

"0:531:1234:2212072242:0:1531::5407::0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0","1:ED1:400:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::1:4:0:0:0","1:Test105:800:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::1:8:0:0:0","1:test-57:799:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::1:7.99:0:0:0","1:test-51:599:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::1:5.99:0:0:0:0:0","1:test-11:109:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::1:1.09:0:0:0","1:Test101:2100:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::2:21:0:0:0","1:test-38:600:HQ:0:0:0:0:0:0:ITEM:0:0:0:0:0:0::0:0:,"2::0:0::2:6:0:0:0","5:8:6:5407:::", "99:2:21:111-3741377-3117822"<cr/lf>

The ambiguity is which SQL is on use.

You can join the two tables on orderId

Info.need is the table definitions and their relative relationship a

Left outer join on table1.column=table2.column1
Will return all elements fro. Table1.

You seem to have :colon separated data as a value.
Are you trying to extract those as individual columns?

Do the column values actually have the double quotes and commas in them?

What is the maximum length of ALL values together?

This was posted in the SQL Server Topic Area.  So what version of SQL Server?

If the length is small enough, maybe a simple string_agg?
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

I'm using SQL Server Management Studio. 

The relationships between the tables is the orderID. 

Colon separated values are needed. Between every colon is a value, and if blank its assumed NULL. These are just long strings of values that have been concatenated from other tables to form each record. 

The problem is, to form a complete record, i need the header, all item records, tendor record, and weborder combined into one string like my example. Some records will only have 1 item record. here is an example without any data.  

"0:Header::::","1:itemRecord::::","2:itemExtentionRecord::::","5:TendorRecord:::","99WeborderRecord:::"

The example above would be a complete order that only had one item in it. 

If more there is more than one item in an order then those itemRecords need included like this below. 

"0:Header::::","1:itemRecord::::","2:itemExtentionRecord::::","1:itemRecord::::","2:itemExtentionRecord::::","1:itemRecord::::","2:itemExtentionRecord::::","1:itemRecord::::","2:itemExtentionRecord::::","5:TendorRecord:::","99WeborderRecord:::"

I hope I am making sense

Yes, the quotes,  and commas are in the fields. Although I can add them separately if needed. 

I'm using version 17.9.1. 

I have an nvarchar (max) length set on the fields. 

Would it make more sense to simply the data even more like this. 
From the tables below I want to concat the fields to make one string like this. 
"0:36:7","1:40001","1:40002","1:40003","5:8:6","99:5:8" 

the single header record, then all three item records, then followed by the tendor and weborder. 

See if this will get you started.

It assumes only one header, tendor and weborder record per order with multiple items.

It also assumes everything together will not exceed 8000 (maybe 4000... not sure of string concatenation in sql server straight SQL.  8000 is the limit for string_agg) characters.  If it can, we cannot use string_agg and need to switch to the XML trick to generate a CSV.

select h.orderid,
	'"' + headerrecord + '",' +
	coalesce(string_agg('"' + itemrecords + '"',','),'') +
	'"' + coalesce(tendorrecord,'') + '",' +
	'"' + coalesce(weborderrecord,'') + '"'
from header h
	left join item i on h.orderid=i.orderid
	left join tendor t on h.orderid=t.orderid
	left join weborder w on h.orderid=w.orderid
group by h.orderid,
	headerrecord,
	tendorrecord,
	weborderrecord
;

Select allOpen in new window

Here is my working fiddle:
https://dbfiddle.uk/9Ln1bGMc

Thanks slightwv. With some modifications that is working. However,  I am concerned of the max length of 8000 for string_agg you brought up. These are order records and could have 100's of items in an order potentially. I am gonna run some tests, but i think it will fail in large order tests cases. 

There are several examples using XML to CSV out there.

For example:
https://sqlrambling.net/2013/12/27/xml-and-stuff-for-a-simple-csv-column/

That should get you around the string_agg limitation.

What I'm still not 100% sure of is string concatenation on long strings.

For example:  select 'x' + some_10000_character_value;

Let us know if you need a more complete example.

Thanks once again slightwv. 
So here is what I have. 

SELECT
      h.orderid, h.store_number,
      headerrecord +
      coalesce(string_agg(itemrecords,''),'') +
      coalesce(tendorrecord,'') +
      coalesce(weborderrecord,'') as tlogData
FROM 
      #HeaderRecord h
         LEFT JOIN #ItemRecords i on h.orderId = i.orderId
         LEFT JOIN #TendorRecord t on h.orderId = t.orderId
         LEFT JOIN #WebOrderRecord w on h.orderId = w.orderId
GROUP BY
      h.orderid,
      h.store_number,
      headerrecord,
      tendorrecord,
      weborderrecord;

Select allOpen in new window

And this produced the correct ouput like this. 
"0:531:1234:2212141722:0:1531::6301::0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0","1:1600012479:938:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::2:9.38:0:0:0:0:0:0:0:0:0:0:0","1:20725700000:1678:12:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0:0:1:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::1.25:16.78:0:0:0:0:0:0:0:0:0:0:0","1:4157005618:1784:23:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::4:17.84:0:0:0:0:0:0:0:0:0:0:0","1:83874102193:1901:17:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::1:19.01:0:0:0:0:0:0:0:0:0:0:0","5:8:6:6301:::","99:2:21:113-1031843-6073021"<cr/lf>

So now trying the xml path to create csv data. 

SELECT STUFF
   ((SELECT
      headerrecord +
      coalesce(string_agg(itemrecords,''),'') +
      coalesce(tendorrecord,'') +
      coalesce(weborderrecord,'') 
FROM 
      #HeaderRecord h
         LEFT JOIN #ItemRecords i on h.orderId = i.orderId
         LEFT JOIN #TendorRecord t on h.orderId = t.orderId
         LEFT JOIN #WebOrderRecord w on h.orderId = w.orderId
WHERE h.orderId = '113-1031843-6073021'
GROUP BY
      h.orderid,
      h.store_number,
      headerrecord,
      tendorrecord,
      weborderrecord
FOR XML PATH('')), 1, 2, '');

Select allOpen in new window

And I get the following:

:531:1234:2212141722:0:1531::6301::0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0","1:1600012479:938:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::2:9.38:0:0:0:0:0:0:0:0:0:0:0","1:20725700000:1678:12:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0:0:1:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::1.25:16.78:0:0:0:0:0:0:0:0:0:0:0","1:4157005618:1784:23:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::4:17.84:0:0:0:0:0:0:0:0:0:0:0","1:83874102193:1901:17:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:0:0","2::0:0::1:19.01:0:0:0:0:0:0:0:0:0:0:0","5:8:6:6301:::","99:2:21:113-1031843-6073021"&lt;cr/lf&gt;

Its close but it cut off the "0  at the beginning from the headrecord. and then it ends with &lt;cr/lf&gt; instead of <cr/lf>

Not abandoning question. I appreciate this and still working on it. But had lots of production issues to deal with. I'll get back to this shortly. I have another question I need to open 1st. 

Hi,

Yes, you can use sqlcmd on Linux. sqlcmd is a command line utility used to execute SQL scripts and stored procedures. It can be used to run ad-hoc queries, output the results to a file, and even automate the execution of stored procedures.

To use sqlcmd on Linux, you will need to install the mssql-tools package. Here is a guide on how to install mssql-tools on Linux.Verified that sqlcmd is working properly, you can create a bash script to run a stored procedure and output the results to a file. Here is an example of how to do this:

#!/bin/bash

# Connect to SQL Server
sqlcmd -S <ServerName> -U <UserName> -P <Password> -i <Input Query File>

# Execute stored procedure
sqlcmd -S <ServerName> -d <DatabaseName> -Q "EXEC <Stored Procedure Name>"

# Output results to file
sqlcmd -S <ServerName> -d <DatabaseName> -o <Output File Name> -Q "EXEC <Stored Procedure Name>"

This should give you an idea of how to use sqlcmd to execute a stored procedure and output the results to a file from a Bash script.


Cheers

Thank you guys. I was able to work off of what slightwv game me for the Sql and for the bash script, i was able to reuse a script another developer already had made for a similar task.

Then can I ask why you accepted the Oracle script posted by dfke as a solution?

My original post was on how to create a bash script which is what dfke provided. In the end I didn't really need it, but its similar to what I used. If the proper thing to do is just to mark it as helpful i can do that.

There is really no 'proper'.  I was curious why an Oracle script was considered an answer to a SQL Server question.

It was a bash script though, not an oracle script. My question was "Bash Script to execute sql store procedure/output to file"