I am continuing my series of talks on fundamental topics like data types. I am proud to be a United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic unit of any database is a TABLE which consists of COLUMNS. A vital decision during the initial database design is to choose the data types that will capture the information you want in the least amount of space. Today, I am exploring binary…
Tag: xp_cmdshell
Database Files – Part 2
The most basic Microsoft SQL Server database is composed of two operating system files: a primary data file and a transaction log file. If possible, the data and log files should be on different physical disks. Today, I am going to be re-creating the [WILDLIFE] database using this simple design in preparation for talking about MOVING and COPYING database files. The key CREATE DATABASE, CREATE TABLE and CREATE INDEX statements are below. The complete script which catalogs ANIMAL names is enclosed as well as today’s new examples. If you have…
Server Settings – Part 1
The simplicity of installing SQL Server out of the box is very appealing to the accidental database administrator (DBA). Run the install program, select default options, and you have a running database. The ease of installation is a double edge sword. You now have a database management server (DBMS) that might be using settings that are not optimal for your company. The purpose of today’s talk is to introduce settings that a DBA can change to make the DBMS perform better. I will be showing where information can be found…
Command Shell
It is sometimes necessary to execute external commands in windows command shell from TSQL. The xp_cmdshell extended stored procedure allows you to do just that. Because this command shell can be a potential target for hackers, it is turned off by default. Today, I am going to use this command to solve a real world ETL problem. The following SQL snippet uses the sp_configure command to enable this option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: #008000;">-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO </span> |
The xp_cmdshell command takes a valid DOS string as input and executes asynchronously in a windows command shell until the…