SQL Server sqlcmd execute os command in script

Question

In SQL Server 2016, I am executing a SQL script through SQLCMD like this:

SQLCMD -H XXXXXX,1433 -U username -P password -d mydatabase 
       -v varMDF="testing" -i "Script.sql" -o "DATA.txt"

and in Script.sql, I want to echo some text to the console, just to see the progress. I have a while loop in the script and executing the command

echo I am in sql script

as shown here:

OPEN tab_cursor  

FETCH NEXT FROM tab_cursor INTO @tablename 

WHILE @@FETCH_STATUS = 0  
BEGIN
    !!echo i am in sql script   
    PRINT @tablename    

    FETCH NEXT FROM tab_cursor INTO @tablename
END 

CLOSE tab_cursor  
DEALLOCATE tab_cursor

The problem is, it display the line "i am in sql script" only once in console but I could see many entries for tablename in my output file. Please help to solve this issue or suggest if there is any other way to do this.

Thanks

You can try `PRINT`, but I'm not sure if `sqlcmd` will process these promptly or cache them until everything's done. If it caches, [try `RAISERROR`, as detailed here](https://stackoverflow.com/a/307005/4137916). `!!` is useless because this command is parsed and executed by `sqlcmd`, which completely ignores the T-SQL logic surrounding it. SQL Server will not "see" such commands and `sqlcmd` doesn't know what SQL Server is doing, so the command is executed only once, when the loop is being sent over to the server. — Jeroen Mostert, Jul 12 '18 at 11:40
thansk @Jeroen, I tried print and raiseerror but both put messages in output file, not on console. — usersam, Jul 12 '18 at 11:54
That's unavoidable, I'm afraid. You cannot have `sqlcmd` output things as SQL Server is processing them, and all SQL Server output would be written to a file (as you've asked it to be written to a file). If you leave off the `-o`, you could pipe standard output to a tool like `tee` to get it in both a file and the console, but I don't think there's any way to get `sqlcmd` to send only certain output to stdout. — Jeroen Mostert, Jul 12 '18 at 11:58

score 0 · Answer 1 · answered Jul 12 '18 at 12:42

I would try the following solutions in order:

1) Look into BCP; it might allow you to see what you are doing much more effectively, and depending on the size of your output file it may be significantly faster. (1b : look into SSIS, even though it's a huge pain)

2) putting a SQLCMD execution inside of Script.sql that did the data push to the file, and having the PRINT statement work as normal without a -o. (NOTE: If this is a Complicated Stored Procedure, why aren't you writing a Complicated Stored Procedure?)

3) Monkeying with server monitoring and profiler. This would be for debugging purposes only, if that's why you need the output.

Generally, it sounds to me like the source of your problem is that you're using the wrong tool for the job. If you want lots of output from SQLCMD on process status, you're probably using it where you should be using BCP, which is designed for doing exports programmatically. SQLCMD isn't all that great an interface for running complicated scripts, in my experience; it needs fire-and-forget.

SQL Server sqlcmd execute os command in script

1 Answers1