SQLCMD History – How to Access and Use

sql serversqlcmd

How can I list/review the sqlcmd history?

Mysql, Postgresql and sqlite write out to dot files: .mysql_history, .psql_history and .sqlite_history.

I'm looking for an equivalent (or approximation) when working with mssql/sql-server.

Thanks.

Best Answer

How can I list/review the sqlcmd history?

SQL Server and the SQLCMD utility do not, by default, track any of this. If you haven't yet setup customized logging then you won't be able to get any information.

SQLCMD is a command line utility and does not keep any history itself. You can setup extra command line logging outside of SQLCMD, but there is no switch or setting to turn on to default log any and all SQLCMD input or output.

The closest thing would be to create an extended events session or server audit that looks for connections through SQLCMD... However this would not be extremely worthwhile as application name and information can easily be spoofed.

In short - if you don't have anything setup, there is nothing. If you did set something up, there is no guarantee that you'll get everything.

If the question changes to "Who is executing what on my SQL Server?" this becomes an entirely different discussion.

Related Solutions

Sqlcmd command strips $ out of query variable

Have you tried defining $InstanceName as a string literal with single quotations?

PS C:\> $GoodInstanceName = '$myInstanceName'
PS C:\> "Hostname$GoodInstanceName"
Hostname$myInstanceName

PS C:\> $BadInstanceName = "$myInstanceName"
PS C:\> "Hostname$BadInstanceName"
Hostname

In Powershell, string literals that are defined with double-quotes will resolve variables. However, if they are defined with single-quotes, they will not. Since $myInstanceName resolved in the $BadInstanceName example but has never been defined, it will simply resolve to an empty string.

I suspect this (or something similar) is the problem since the error seems to do with your completed path string.

SQLCMD Mode – Environment Variable Syntax

The sqlcmd syntax for setting variables is :setvar. You can access environment variables from sqlcmd mode (using the $(variableName) syntax) however I find these case-sensitive and it's a bit fussy about what you can and can't do. Try this, hopefully it makes sense:

:setvar mypath "$(SystemRoot)"

:out $(SystemRoot)\test.txt     -- will work

print '$(mypath)'   -- oops won't work
print '$(SystemRoot)'   -- should work

go

:out stdout

Best Answer

Related Solutions

Sqlcmd command strips $ out of query variable

SQLCMD Mode – Environment Variable Syntax

Related Question