Running SQLPS.exe<\/h2>\n
Executing SQLPS.exe is as easy as any other executable. When it is run; the SQLPS mini-shell will launch. The first option is to make a shortcut to the executable location. The location is displayed below.<\/p>\n
The next option is to simply access it via command prompt or Powershell. In either console you should be able to simply type \u201csqlps\u201d to run the application.<\/p>\n The navigation of your SQL Server and the execution of queries is very similar to the SQLPS module, see below for those details.<\/p>\n SQLPS the module is included when you install SQL Server 2012. If you don\u2019t have SQL Server 2012 you can create your own SQLPS module by following Chad Miller<\/a>\u2018s article,\u00a0Making A SQLPS Module<\/a>.<\/em><\/p>\n Once installed you must import the module into your active Powershell console window. To do this execute the below script.<\/p>\n As seen above, you trigger a warning when first importing the SQLPS module. This can be ignored. All it is saying is that the SQLPS module is using non-standard verbs in their cmdlets. If you don\u2019t wish to see this message, helpful if you put the import-module command into a profile file, simply run this script instead.<\/p>\n You\u2019ll also notice that it will automatically change directory to the SQLSERVER PSDrive which is created by the module. You can use \u2018cd\u2019 to exit and re-enter this location at will.<\/p>\n Once your new PSDrive is accessed you can navigate through the above listed areas of SQL Server. This module was designed to do far more than simply invoke SQL commands but, unfortunately, many of the SQLPS features are out of scope for this blog series. We will, however, be covering the SQL commands in this next section.<\/p>\n Executing a query or SQL command couldn\u2019t be easier with SQLPS. Below is an example of the Invoke-SqlCmd cmdlet running a query, this can be done from any directory, there is no need to have SQLSERVER:\\ as your working directory.<\/p>\n This next example runs the same query but from within the context of your working directory.<\/p>\n I hope this five part blog series on T-SQL command line options provided you some value. We covered the basic functionality of SQLCMD.exe, Powershell native .NET objects, the open source SQLPSX module, SQLPS.exe the mini-shell, and SQLPS the module. In addition to basic query execution and an overview of features we covered the purpose behind each\u2019s use and recommendations as to when you should choose to use one over the other.<\/p>\n If you have a question you\u2019d like answered or have an idea of a SQL Server topic that you\u2019d like me to discuss please feel free to contact me at dhammer3407@gmail.com<\/a> or on twitter @sqlhammer<\/a>.<\/p>\n![\"\"](\"\/wp-content\/uploads\/sqlps-location.png\")
<\/a><\/p>\n<\/a><\/p>\nEstablishing a connection with SQLPS the module<\/h2>\n
Import-Module SQLPS<\/code><\/pre>\n<\/a><\/p>\nImport-Module sqlps -DisableNameChecking<\/code><\/pre>\n<\/a><\/p>\nQuery execution with SQLPS the module<\/h2>\n
Invoke-Sqlcmd -Query \"SELECT name FROM sys.databases;\"\u00a0-ServerInstance localhost\\sql2012<\/code><\/pre>\n<\/a><\/p>\n<\/a><\/p>\nWrap up<\/h2>\n
Navigation<\/h2>\n