question

MichealBolton-7901 avatar image
0 Votes"
MichealBolton-7901 asked ErlandSommarskog commented

Lixux shell scripts - convert isql istructions to sqlcmd istructions

Hi!
We are migrating some shell scripts from unix to linux and we are bsolutely newby. Some scripts invoke slq scripts which contain instructions for operations on a syabase database that we are migrating to sql server database, so we are rewriting these sql scripts as well. Our new shell scripts call directly the sqlcmd command and already pass db server, db name, user and password stored in environment variables:

sqlcmd -S $SERVER_DB -d $NAME_DB -U $USER_DB -P $PASSW_DB < /home/scripts/update_data.sql

but at the first line of each sql script we need to convert, we are finding similar statement:

isql -U$DBUSER -P$DBPASSWD -w 80<< EOF|grep -v "return status" >>/usr/local/abc/ABC.txt

Perhaps because for some users a certain connection to the syabase database was set by default, so it was sufficient to put the call to the isql command on the first line of the sql file passing only the user and password, but in our case using sqlcmd we already pass everything directly in the shell script, it is not necessary to invoke the sqlcmd command again at the first line of the sql script. So what should we write in place of isql call but leaving the part that redirects the output to the ABC.txt file? Thanks in advance





sql-server-generalsql-server-migration
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MichealBolton-7901, we have not get a reply from you. Did any answers could help you? If there has an answer helped, do "Accept Answer". By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
DanGuzman avatar image
0 Votes"
DanGuzman answered

Instead of redirecting STDIN, use -i with the script file path. You can still redirect STDOUT similarly to the isql version.

 sqlcmd -S $SERVER_DB -d $NAME_DB -U $USER_DB -P $PASSW_DB -i /home/scripts/update_data.sql >>/usr/local/abc/ABC.txt
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichealBolton-7901 avatar image
0 Votes"
MichealBolton-7901 answered MichealBolton-7901 edited

Thanks for reply Dan!
Just to be sure I understand what you wrote to me..
Now we have some shell scripts ,for example the launchSQL1.csh script, with this kinds of calls to the SQL script inside:

 isql -U$DBUSER -P$DBPASSWD < $path/update.sql

or

 isql -U$DBUSER -P$DBPASSWD < $path/insert.sql>> $strLog

and in both update.sql and insert.sql there aren't call to isql at the first line.
Conversely in some scripts, for example the launchSQL2.csh sript, we have this kinds of call to the SQL script:

 $path/delete.sql >>$log

but in delete.sql there is this call to isql at the first line:

 isql -U$DBUSER -P$DBPASSWD  -w  80<< EOF|grep -v "return status" >>/usr/local/abc/ABC.txt 

So in this second case (launchSQL2.csh) I seem to understand that I have to replace the isql call with the instruction you suggested, but in that statement there is the complete path of the sql script which, however, at the first line of the same sql script would not make sense. So you say to put those statement in the shell script and not put anything in the first line of the sql script?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

, we have this kinds of call to the SQL script:

 $path/delete.sql >>$log

But that is not an SQL script - it is obviously a Shell script. Someone is just muddling the waters.

This line:

   isql -U$DBUSER -P$DBPASSWD  -w  80<< EOF|grep -v "return status" >>/usr/local/abc/ABC.txt 

The part << EOF indicates that the input comes from somewhere that I don't see. Is the rest of this file delete.sql just SQL statements?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @MichealBolton-7901,

I found two threads may help you:
1.Sybase query:save an output to a file
2.Shell script : Redirect sql query output to a file

Because I am not familiar with shell script, hope the links will help you.

Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichealBolton-7901 avatar image
0 Votes"
MichealBolton-7901 answered

Thanks for your suggestion @CarrinWu-MSFT , i will try to take a look at the two threads.
@ErlandSommarskog the answer is yes, the delete.sql is just SQL statements.

Sorry, a couple of other questions..
Can I redirect sqlcmd output to a log file and output of the stored procedure execution in another file contemporary in a single sqlcmd istruction?
For example is this istruction correct?

     sqlcmd -S $SERVER_DB -d $NAME_DB -U $USER_DB -P $PASSW_DB -Q "EXECUTE dbo.SPdel" -o $path1/RESULT.REQ  >>$log


In case a previous sqlcmd statement had already been executed with the -Q and -o option in order to write the result of the stored procedure execution to a RESULT.REQ file, which happens if you reexecute the same command with another stored procedure with output to the same file RESULT.REQ? Will the file be overwritten or will the output be added without losing the previous output?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Multiple calls to SQLCMD -o will overwrite the output file, so are better off scrapping -o and use only >> to write the output.

I would take out isql commands from the .sql files to make them true SQL scripts and simply run them with SQLCMD.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichealBolton-7901 avatar image
0 Votes"
MichealBolton-7901 answered ErlandSommarskog commented

Ok @ErlandSommarskog, so I should write?

 sqlcmd -S $SERVER_DB -d $NAME_DB -U $USER_DB -P $PASSW_DB -Q "EXECUTE dbo.SelectAbc" >> $path1/RESULT.REQ
    
 sqlcmd -S $SERVER_DB -d $NAME_DB -U $USER_DB -P $PASSW_DB -Q "EXECUTE dbo.SelectDef" >> $path1/RESULT.REQ

But in this way I lose the ability to keep track of what happens on a log, there would be no other way?

Many thanks for all your answers!!

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Your requirements are not clear to me, but one solution might to put all procedures to run into a script script, and add PRINT statements between the calls to track what is going on.

0 Votes 0 ·

Ok, it's only because before I've used:

 o $path1/RESULT.REQ  >>$log

the -o will overwrite the output file, so that part of istruction became:


 >>$path1/RESULT.REQ 

no more log information are stored only output in file RESULT.REQ, right?

0 Votes 0 ·

Yes. The -i and -o options are sort of odd on Unix where tools typically read from STDIN and write to STDOUT and then you use <, > and >> to redirect the output.

You should also add 2>&1 at the end of the command line to also redirect STDERR to the file. I don't know if SQLCMD writes error messages to STDERR, but a good Unix citizen should do that.

0 Votes 0 ·
MichealBolton-7901 avatar image
0 Votes"
MichealBolton-7901 answered ErlandSommarskog commented

Mayday mayday... we're going down..
When I use sqlcmd with -o option:

  sqlcmd -S $SERVER_DB -d $NAME_DB -U $USER_DB -P $PASSW_DB -Q "EXECUTE dbo.SPgetData" -o $path1/RESULT.REQ

I get an extra line on the first line, something like:



and even worse, an empty line at the end and immediately below a line that says (722 rows affected).
Please, tell me there is a way to just take the records returned by the query without this additional garbage!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what do you want SQLCMD for? SQLCMD is an interactive query tool, just like SSMS, but it is written for a console window. So the garbage you see, is a result of this.

You can get rid of the headers with the option -h-1, and if you add the SET NOCOUNT ON to the SQL command, you can get rid of the rows affected messages.

But if your intention to do data export, you are not using a tool intended for the task, and you will have to accept some rough edges.

0 Votes 0 ·