Author Archives: Jugal Shah

About Jugal Shah

Jugal Shah has 19 plus years of experience in leading and managing the data and analytics practices. He has done the significant work in databases, analytics and generative AI projects. You can check his profile on http://sqldbpool.com/certificationawards/ URL.

GIS DataTypes in SQL Server 2008

What is GIS?

geographic information system (GIS) integrates hardware, software, and data for capturing, managing, analyzing, and displaying all forms of geographically referenced information.

Why GIS?

GIS allows us to view, understand, question, interpret, and visualize data in many ways that reveal relationships, patterns, and trends in the form of maps, globes, reports, and charts.

A GIS helps you answer questions and solve problems by looking at your data in a way that is quickly understood and easily shared.

GIS technology can be integrated into any enterprise information system framework.

A GIS can be viewed in three ways:

1. The Database View

2. The Map View

3. The model View

XP_cmdshell extended stored procedure (Execute Winows commands)

2 Replies

xp_cmdshell

Executes a given command string or batch file as an operating-system command shell and returns any output as rows of text.

Permission/Rights: Only SysAdmin fixed role can execute it.

Syntax

xp_cmdshell {‘command_string‘} [, no_output]

Arguments

‘command_string‘

Is the command string to execute at the operating-system command shell or from DOS prompt. command_string is varchar(255) or nvarchar(4000), with no default.

command_string cannot contain more than one set of double quotation marks.

A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string.

If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

no_output

Is an optional parameter executing the given command_string, and does not return any output to the client.

Examples
xp_cmdshell 'dir *.jpg'

Executing this xp_cmdshell statement returns the following result set:

xp_cmdshell 'dir *.exe', NO_OUTPUT

Here is the result:

The command(s) completed successfully.

<!–[if gte vml 1]> <![endif]–><!–[if !vml]–><!–[endif]–>

Examples

Copy File
EXEC xp_cmdshell 'copy c:\sqldumps\jshah143.bak \\server2\backups\jshah143.bak',  NO_OUTPUT

Use return status

In this example, the xp_cmdshell extended stored procedure also suggests return status. The return code value is stored in the variable @result.

DECLARE @result int
EXEC @result = xp_cmdshell 'dir *.exe'
IF (@result = 0)
   PRINT 'Success'
ELSE
   PRINT 'Failure'

Pass the parameter to batch file

DECLARE @sourcepath VARCHAR(100)
DECLARE @destinationpath VARCHAR(1000)
SET @sourcepath = ' c:\sqldumps\jshah143.bak '
SET @destinationpath = '\\server2\backups\jshah143.bak'
 
SET @CMDSQL = 'c:copyfile.bat' + @sourcepath + @destinationpath
EXEC master..XP_CMDShell @CMDSQL

How to read transaction log data in SQL Server 2000/2005?

3 Replies

We can’t read the transaction logs directly from SQL Server. Some products do allow you to read the logs, such as:

* Apex SQL Log (Works with MSSQL2000 and MSSQL2005)
* Log Explorer
* SQL Log Rescue (Only work with the SQL Server 2000)

The below undocumented DBCC command is working in both SQL Server 2000 & SQL Server 2005

DBCC LOG(<database name>[,{0|1|2|3|4}])
0 – Basic Log Information (default)
1 – Lengthy Info
2 – Very Length Info
3 – Detailed
4 – Full Example
Syntax:
DBCC log (MY_DB, 4)

Another undocumented command is DBCC LOGINFO

Disk Space Alerts using SQL Server 2005

Please visit http://sqldbpool.blogspot.com/

Step 1: Create the database mail profile account using SQL Server Management Studio. Give the profile name to “FreeSpaceAlertMails”

Step2: Create the below procedure in master database which will check the disk space.

CREATE PROCEDURE sendAlertMails
AS
BEGIN
SET NOCOUNT ON
DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(3),
freeSpace FLOAT
)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @availableSpace = ROUND((freeSpace)/1024,1) FROM #tbldiskSpace WHERE driveName = ‘E’

SET @alertMessage = ‘(host:jshah.sqldbpool.com)E:\ Disk Space Critical. Free Space Available on E:\ Drive is ‘ + CAST(@availableSpace AS VARCHAR) + ‘GB’

IF @availableSpace < 10
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘FreeSpaceAlertMails’,
@recipients = ‘jshah143@gmail.com’,
@body = @alertMessage,
@importance = ‘High’,
@subject = ‘host:jshah.sqldbpool.com Disk Critical E Drive’ ;
END

DROP TABLE #tbldiskSpace

END

Step 3: Create the job which will execute the above procedure at every 2 hours interval.

DOS Commands for DBA

6 Replies

As SQL Server DBA, there are many activities which needs the knowledge of DOS Commands. Please find the below useful DOS Commands.

ANSI.SYS – Defines functions that change display graphics, control cursor movement, and reassign keys.
APPEND – Causes MS-DOS to look in other directories when editing a file or running a command.
ARP – Displays, adds, and removes arp information from network devices.
ASSIGN – Assign a drive letter to an alternate letter.
ASSOC – View the file associations.
AT – Schedule a time to execute commands or programs.
ATMADM – Lists connections and addresses seen by Windows ATM call manager.
ATTRIB – Display and change file attributes.
BATCH – Recovery console command that executes a series of commands in a file.
BOOTCFG – Recovery console command that allows a user to view, modify, and rebuild the boot.ini
BREAK – Enable / disable CTRL + C feature.
CACLS – View and modify file ACL’s.
CALL – Calls a batch file from another batch file.
CD – Changes directories.
CHCP – Supplement the International keyboard and character set information.
CHDIR – Changes directories.
CHKDSK – Check the hard disk drive running FAT for errors.
CHKNTFS – Check the hard disk drive running NTFS for errors.
CHOICE – Specify a listing of multiple options within a batch file.
CLS – Clears the screen.
CMD – Opens the command interpreter.
COLOR – Easily change the foreground and background color of the MS-DOS window.
COMMAND – Opens the command interpreter.
COMP – Compares files.
COMPACT – Compresses and uncompress files.
CONTROL – Open control panel icons from the MS-DOS prompt.
CONVERT – Convert FAT to NTFS.
COPY – Copy one or more files to an alternate location.
CTTY – Change the computers input/output devices.
DATE – View or change the systems date.
DEBUG – Debug utility to create assembly programs to modify hardware settings.
DEFRAG – Re-arrange the hard disk drive to help with loading programs.
DEL – Deletes one or more files.
DELETE – Recovery console command that deletes a file.
DELTREE – Deletes one or more files and/or directories.
DIR – List the contents of one or more directory.
DISABLE – Recovery console command that disables Windows system services or drivers.
DISKCOMP – Compare a disk with another disk.
DISKCOPY – Copy the contents of one disk and place them on another disk.
DOSKEY – Command to view and execute commands that have been run in the past.
DOSSHELL – A GUI to help with early MS-DOS users.
DRIVPARM – Enables overwrite of original device drivers.
ECHO – Displays messages and enables and disables echo.
EDIT – View and edit files.
EDLIN – View and edit files.
EMM386 – Load extended Memory Manager.
ENABLE – Recovery console command to enable a disable service or driver.
ENDLOCAL – Stops the localization of the environment changes enabled by the setlocal command.
ERASE – Erase files from computer.
EXIT – Exit from the command interpreter.
EXPAND – Expand a Microsoft Windows file back to it’s original format.
EXTRACT – Extract files from the Microsoft Windows cabinets.
FASTHELP – Displays a listing of MS-DOS commands and information about them.
FC – Compare files.
FDISK – Utility used to create partitions on the hard disk drive.
FIND – Search for text within a file.
FINDSTR – Searches for a string of text within a file.
FIXBOOT – Writes a new boot sector.
FIXMBR – Writes a new boot record to a disk drive.
FORMAT – Command to erase and prepare a disk drive.
FTP – Command to connect and operate on a FTP server.
FTYPE – Displays or modifies file types used in file extension associations.
GOTO – Moves a batch file to a specific label or location.
GRAFTABL – Show extended characters in graphics mode.
HELP – Display a listing of commands and brief explanation.
IF – Allows for batch files to perform conditional processing.
IFSHLP.SYS – 32-bit file manager.
IPCONFIG – Network command to view network adapter settings and assigned values.
KEYB – Change layout of keyboard.
LABEL – Change the label of a disk drive.
LH – Load a device driver in to high memory.
LISTSVC – Recovery console command that displays the services and drivers.
LOADFIX – Load a program above the first 64k.
LOADHIGH – Load a device driver in to high memory.
LOCK – Lock the hard disk drive.
LOGON – Recovery console command to list installations and enable administrator login.
MAP – Displays the device name of a drive.
MD – Command to create a new directory.
MEM – Display memory on system.
MKDIR – Command to create a new directory.
MODE – Modify the port or display settings.
MORE – Display one page at a time.
MOVE – Move one or more files from one directory to another directory.
MSAV – Early Microsoft Virus scanner.
MSD – Diagnostics utility.
MSCDEX – Utility used to load and provide access to the CD-ROM.
NBTSTAT – Displays protocol statistics and current TCP/IP connections using
NET – Update, fix, or view the network or network settings
NETSH – Configure dynamic and static network information from MS-DOS.
NETSTAT – Display the TCP/IP network protocol statistics and information.
NLSFUNC – Load country specific information.
NSLOOKUP – Look up an IP address of a domain or host on a network.
PATH – View and modify the computers path location.
PATHPING – View and locate locations of network latency.
PAUSE – Command used in batch files to stop the processing of a command.
PING – Test / send information to another network computer or network device.
POPD – Changes to the directory or network path stored by the pushd command.
POWER – Conserve power with computer portables.
PRINT – Prints data to a printer port.
PROMPT – View and change the MS-DOS prompt.
PUSHD – Stores a directory or network path in memory so it can be returned to at any time.
QBASIC – Open the QBasic.
RD – Removes an empty directory.
REN – Renames a file or directory.
RENAME – Renames a file or directory.
RMDIR – Removes an empty directory.
ROUTE – View and configure windows network route tables.
RUNAS – Enables a user to execute a program on another computer.
SCANDISK – Run the scandisk utility.
SCANREG – Scan registry and recover registry from errors.
SET – Change one variable or string to another.
SETLOCAL – Enables local environments to be changed without affecting anything else.
SETVER – Change MS-DOS version to trick older MS-DOS programs.
SHARE – Installs support for file sharing and locking capabilities.
SHIFT – Changes the position of replaceable parameters in a batch program.
SHUTDOWN – Shutdown the computer from the MS-DOS prompt.
SMARTDRV – Create a disk cache in conventional memory or extended memory.
SORT – Sorts the input and displays the output to the screen.
START – Start a separate window in Windows from the MS-DOS prompt.
SUBST – Substitute a folder on your computer for another drive letter.
SWITCHES – Remove add functions from MS-DOS.
SYS – Transfer system files to disk drive.
TELNET – Telnet to another computer / device from the prompt.
TIME – View or modify the system time.
TITLE – Change the title of their MS-DOS window.
TRACERT – Visually view a network packets route across a network.
TREE – View a visual tree of the hard disk drive.
TYPE – Display the contents of a file.
UNDELETE – Undelete a file that has been deleted.
UNFORMAT – Unformat a hard disk drive.
UNLOCK – Unlock a disk drive.
VER – Display the version information.
VERIFY – Enables or disables the feature to determine if files have been written properly.
VOL – Displays the volume information about the designated drive.
XCOPY – Copy multiple files, directories, and/or drives from one location to another.

aiDeeva.com

AI for Thinkers, Builders, and Believers.