Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: Command line

For this demo, you will require SQL Server with the AdventureWorks database and a copy of Windows Powershell. We intend to build up a number of UPDATE statements based on data within AdventureWorks.HumanResources.Employee. We want to give all 157 production technicians an extra ten vacation hours, but need an easy way of generating the many lines of SQL we require. First of all, we need to run the following query to return the employeeID of all employees who meet this criteria:-

USE AdventureWorks
SELECT EmployeeID
FROM HumanResources.Employee
WHERE Title LIKE 'Production Technician%'

With a fresh installation of the AdventureWorks database, this should return 157 results. Create a file on the root of C: called myfile.txt and copy and paste in the results using notepad or other text editor. You should have one EmployeeID per line.

Now we can build up our UPDATE queries by using Windows Powershell. Open Powershell and enter the following:-

get-content c:\myfile.txt | Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"} | out-file c:\output.txt

If you now check the newly created file (ouput.txt) on C: drive, you should have 157 UPDATE statements which you can copy and paste into SQL Server. Executing will give the production assistants their extra hours.

output.txt

Now to explain the powershell code…..

get-content c:\myfile.txt

simply reads the contents of our file. If you solely run this part of the command in Powershell, the contents of the file will be displayed on screen.

The main part of our command is the foreach loop which builds our SQL statements by adding our text before and after the contents of each row. The contents of each row is held in “$_

Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"}

The final part of our code simply writes the changes out to our output.txt file

out-file c:\output.txt

There are much more elegant ways of writing our SQL statement. We could of course, have written a single UPDATE statement and included,

WHERE Title LIKE 'Production Technician%'

…. but that would not have been as much fun and you would not have just learnt this handy Powershell tip!!

Advertisements

Tags: , , , , , , ,

xp_cmdshell allows Windows CMD shell commands to be executed from within SQL Server. This allows a DBA to perform file and OS-level tasks. All the usual CMD tasks can be issued from within a T-SQL query window or from within a SQL Server Agent job step.

xp_cmdshell is disabled by default so needs to be enabled through the Surface Area Configuration utility. Once enabled, CMD commands can be entered and executed as follows:-

EXEC xp_cmdshell 'enter command here'

So, to rename a file, you can run:-

EXEC xp_cmdshell 'ren "C:\my_file.xml" "renamed.xml"'

A large number of commands can be issued. For a comprehensive list, see here.

Permissions can be problematic when running CMD commands from SQL Server. The user that executes xp_cmdshell must have the correct permissions to perform the required action. This means that the user configured to run the SQL Agent service will require relevant permissions if xp_cmdshell is run from a SQL Agent job. Alternatively, a proxy account with sufficient privileges can be configured.

If you do receive operating system errors relating to permissions, then check which Windows account is running the command by executing:-

EXEC xp_cmdshell 'whoami.exe'

Once the user account is known, steps can then be taken to resolve permissions based issues by granting elevated privileges.

Tags: , , , , ,

%d bloggers like this: