sqlquantumleap/simplesqlexec

Lightweight command-line utility to execute queries on SQL Server in place of SQLCMD

simplesqlexec's Issues

Features missing

I will try to write here the features that would be useful from my point of view:

1. Connecting to a running instance should be most simple, I would say writing instance name should be enough, or it can connect automatically to the only instance, that is running
2. Support for SELECT queries should be added
3. Should provide rows affected info for queries with NOCOUNT OFF
4. Better query error handling
5. Allow execute more statements using command prompt (not just command line parameter)
6. Perhaps a simple password protection against meddling of inexperienced users

[Suggestion] [PR] Implement .NET Core support.

I wanted to start a discussion on the viability of a .NET Core version.

Earlier this week I posted a draft PR (#16) and from the looks of it its working fine. We did a couple of tests on our build agents and noticed no issues. At the same time our migration scripts are all generated by EF Core so we dont do anything too complex or weird there.

Maybe the best way forward would be to leave both console application projects in the solution and move all functionality to a Netstandard library. That way we will keep the classic framework application for Windows/Legacy projects and the .NET Core one for Linux (and Windows).

Let me know what you think :) if you like the idea I'll go ahead and push the changes adding the library to the solution.

Kind regards!

Support variable substitution

Allow substitution of $(var_name) variable.

This also requires handling:

• -v command-line switch
• :setvar SQLCMD command
• checking all 3 levels of environment variables
• -x ignore scripting variables

The following notes are taken from the MSDN page for the sqlcmd Utility:

Variable Precedence (Low to High)

1. System-level environmental variables.
2. User-level environmental variables
3. Command shell (SET X=Y) set at command prompt before running sqlcmd.
4. sqlcmd -v X=Y
5. :Setvar X Y

NOTES:

• SQLCMD processes commands and variable substitution per each batch!
• Scripting variable names are case-insensitive
• If no value is assigned to a sqlcmd environment variable, the variable is removed.
• Using :setvar VarName without a value clears the variable.
• SQLCMD commands inside of block comments and string literals are not processed.
• See also: Use sqlcmd with Scripting Variables

What License is this shared under? MIT?

What license is this released under ? The MIT License (MIT)?

Example:
https://raw.githubusercontent.com/adamralph/stylecop-msbuild/master/license.txt

Support SQLCMD commands

Add support for SQLCMD commands, such as:

• :r
• :Setvar
• [:] RESET
• [:] !!
• [:] QUIT
• [:] EXIT
• :List
• :Listvar
• :Error
• :Out
• :Connect
• :On Error

Optionally:

• [:] ED
• :Perftrace
• :Help
• :XML [ON | OFF]

Do not do these:

• :Serverlist

These commands are described on the following MSDN page:

sqlcmd Utility

Start with :r and :setvar as they are the most commonly used, especially if using SSDT.

NOTES:

• SQLCMD processes commands and variable substitution per each batch!
• SQLCMD command names are handled as case-insensitive
• SQLCMD commands inside of block comments and string literals are not processed.
• need to also handle -X[1]:
  • Disables ED and !! commands
  • default behavior is to display a warning only
  • if the 1 is also specified, error and exit

Handle errors similarly to SQLCMD and SSMS

By default in SQLCMD and SSMS, errors do not abort the current batch or cancel any remaining batches. The following, run in either of those, will return all four result sets:

SELECT 1;
RAISERROR('test 1', 16, 1);
SELECT 2;
GO

SELECT 3;
RAISERROR('test 2', 16, 1);
SELECT 4;
GO

Currently, SimpleSqlExec will abort at the first error. While that behavior is probably preferred, it is different than SQLCMD and SSMS. There should at least be the ability to continue after an error.

Also, emulating SQLCMD behavior requires handling the -b "batch abort" switch which will terminate at the end of the current batch if there was an error. In the example code above, using -b should result in getting just the 1 and 2 result sets, not 3 or 4.

Handle block comments before a GO

Batch parsing should be able to handle the following cases:

1. single, single-line block comment:

   /* comment */  GO
   

2. single, multiple-line block comment:

   /* comment line 1
        line 2
         line 3        */  GO
   

3. multiple block comments (no space between them!):

   /* comment line 1
        line 2 *//* start of #2 
                */            GO 4
   

4. batch terminator embedded in a multi-line comment should be ignored:

   /* comment line 1
   GO
    more comments */
   

5. batch terminator embedded in a multi-line string literal should be ignored:

   ' some text
   GO
    more text'
   

Batch terminator is used to split even when located inside a comment.

Script to reproduce:

/* 
  Instructions to use this stored procedure:
  exec stored_procedure arg1, arg2
  go
*/
create procedure stored_procedure
-- etc

Sometimes there may be a go within a comment - most often as some kind of documentation.
These should not be recognized as a batch separator.