Extended Stored Procedures

      An extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server. You can run extended stored procedures as normal stored procedures. Extended stored procedures are used to extend the capabilities of SQL Server. You can take advantage of the many extended stored procedures that come with SQL Server, or you can write your own.

This article describes some useful undocumented extended stored procedures that
shipped with SQL Server 2005.

1) SP_MSgetversion

This extended stored procedure can be used to get the current version of Microsoft SQL Server.
To get the current SQL Server version, run:

EXEC master..sp_MSgetversion

Note. A more common way to retrieve the current SQL Server version (this way provides more
information) is to use following SELECT statement:

SELECT @@version

2) XP_availablemedia

This extended stored procedure returns available drives and free space in bytes on these drives.
In comparison with xp_fixeddrives the xp_availablemedia extended stored procedure returns not
only the hard drives, but all available drives.
The xp_availablemedia returns free space in bytes when xp_fixeddrives returns free space in Mb.
To get the list of all available drives with free space on them, run:

EXEC master..xp_availablemedia

3) XP_delete_file

This extended stored procedure can be used to delete a SQL Server backup file or a Maintenance
Plan report file.

Syntax:

EXECUTE xp_delete_file 0|1, 'file_name' 



0 - to delete a SQL Server backup file.

1 - to delete a Maintenance Plan report file.

For example, to delete the TestBackup.bak SQL Server backup file from the SQL directory of the
D: disk, you can run this statement:

EXEC master..xp_delete_file 0, 'd:\SQL\TestBackup.bak'

4) XP_dirtree

This extended stored procedure can be used to get a list of all the folders for the folder named
in the xp. To get a list of all the folders in the D:\Install folder, run:

EXEC master..xp_dirtree 'D:\Install'

5) XP_create_subdir

This extended stored procedure creates a subdirectory for the specified directory. For example,
to create 'SQL' subdirectory in the 'Install' directory on the D: disk, you can run the following:

EXEC master..xp_create_subdir 'D:\Install\SQL'

6) XP_enum_oledb_providers

This extended stored procedure is used to list of all the available OLE DB providers. It returns
Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your
SQL Server, run:

EXEC master..xp_enum_oledb_providers

7) XP_enumcodepages

This extended stored procedure can be used to list of all code pages, character sets and their
description for your SQL Server. To get a list of all code pages and character sets, run:

EXEC master..xp_enumcodepages

Note. To run this procedure, you should enable the 'Web Assistant Procedures' using the sp_configure
system stored procedure.

8) XP_enumerrorlogs

This extended stored procedure returns the list of all error logs with their last change date
and error log files size.
To get the list of error logs, run:

EXEC master..xp_enumerrorlogs

9) XP_fileexist

You can use this extended stored procedure to determine whether a particular file exists on the
disk or not.

Syntax:


EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

For example, to check whether the file boot.ini exists on C: disk  or not, run:

EXEC master..xp_fileexist 'c:\boot.ini'

10) XP_fixeddrives

This very useful extended stored procedure returns the list of all hard drives and the amount of
free space in Mb for each hard drive.

To see the list of drives, run:

EXEC master..xp_fixeddrives

11) XP_getnetname

This extended stored procedure returns the WINS name of the SQL Server that you are connected
to. To view the SQL Server name, run:

EXEC master..xp_getnetname

12) XP_get_tape_devices

This extended stored procedure is used to get the names of all the available tape devices.
To get a list of all tape devices for your SQL Server, run:

EXEC master..xp_get_tape_devices

13) XP_readerrorlog

This extended stored procedure returns the content of the last errorlog file. To see the text
of the errorlog file, run:

EXEC master..xp_readerrorlog

14) XP_regdeletekey

This extended stored procedure can be used to delete an entire key from the registry. You should
use it very carefully.

Syntax:


EXECUTE xp_regdeletekey [@rootkey=]'rootkey',

                        [@key=]'key' 

For example, to delete the 'SOFTWARE\Test' key from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletekey


     @rootkey='HKEY_LOCAL_MACHINE',  

     @key='SOFTWARE\Test'

15) XP_regdeletevalue

This extended stored procedure can be used to delete a particular value for a key in the registry.
You should use it very carefully.

Syntax:


EXECUTE xp_regdeletevalue [@rootkey=]'rootkey',

                          [@key=]'key',

                          [@value_name=]'value_name'

For example, to delete the 'TestValue' value for the 'SOFTWARE\Test' key from
'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletevalue

     @rootkey='HKEY_LOCAL_MACHINE',

     @key='SOFTWARE\Test',

     @value_name='TestValue'

16) XP_regread

This extended stored procedure is used to read from the registry.

Syntax:


EXECUTE xp_regread [@rootkey=]'rootkey',

                   [@key=]'key'

                   [, [@value_name=]'value_name']

                   [, [@value=]@value OUTPUT] 

For example, to read into the @test variable from the 'TestValue' value from the 'SOFTWARE\Test'
key from the 'HKEY_LOCAL_MACHINE', run:

DECLARE @test varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key='SOFTWARE\Test',

  @value_name='TestValue',

  @value=@test OUTPUT

SELECT @test

17) XP_regwrite

This extended stored procedure is used to write to the registry.

Syntax:


EXECUTE xp_regwrite [@rootkey=]'rootkey',

                    [@key=]'key',

                    [@value_name=]'value_name',

                    [@type=]'type',

                    [@value=]'value'

For example, to write the 'Test' variable to the 'TestValue' value, 'SOFTWARE\Test' key,
'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regwrite

     @rootkey='HKEY_LOCAL_MACHINE',

     @key='SOFTWARE\Test',

     @value_name='TestValue',

     @type='REG_SZ',

     @value='Test'

18) XP_test_mapi_profile

This extended stored procedure can be used to resolve problems with mapi profiles. For example,
you can run the statement below to test if the 'profile_name' mapi profile exist and correctly
configured:

EXEC master..xp_test_mapi_profile 'profile_name'

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Replication Between Two SQL Servers

     Replication often plays an important part in your database management strategy. An organization may use database replication for load balancing, offline processing, redundancy, and other typical administrative tasks. This How do I… shows you how to configure replication between two Microsoft SQL Server 2005 systems using the SQL Server Management Studio.

    First, you will need to connect the database engine that contains the data you wish to replicate in SQL Server Management Studio. From there, you will need to right-click on the Replication folder (Figure A) and select Configure Distribution.

Figure A

                        Replication folder

     Next, you will specify how you want to select your distribution server. (Figure B) In most small replication environments, using the server that hosts the data as your distributor is fine. However, if you are replicating large amounts of data on very busy servers, you may wish to set up a dedicated box to be your distribution system. This individual distribution system can be the distributor for multiple SQL Servers if you have that need.

Figure B

                         Distribution server

     You will need to have your SQL Server Agent service configured to start automatically. (Figure C) This is due to the SQL Agent being responsible for managing subscriptions. To configure this through this wizard, the account running the SQL Server Service must have administrator privileges on the computer. If it does not, anyone with administrator privileges can set the SQL Server Agent account to run automatically.

Figure C

                           SQL Server Agent

     Next, you will need to set your snapshot folder. (Figure D) This folder will contain a snapshot (a collection of files, which contain all of the data you are choosing to replicate initially). This folder will need to have sufficient space to contain all the data you will be replicating. If you are only going to be pushing your subscriptions to subscribers, a local path is fine. If your subscribers will be pulling data, you will need to place your snapshot on a network path that is accessible by both systems.

Figure D

                            Snapshot folder

     The settings for distribution database (Figure E) must be determined by the amount of data you will be replicating and the frequency of the replication. The database (by default, named distribution) can be large (if you replicate sporadically) and highly active (if you have a lot of activity in replicated data). Be careful with your placement of this database to make sure you have sufficient storage and that it will not negatively impact disk subsystem performance on your server.

Figure E

                           Distribution

     Now that the distribution aspect is set, you can select the data you would like to publish. To do this, right-click on Local Publications and select New Publication. (Figure F)

Figure F

                           Local Publication

     You will then need to select the database you wish to replicate data from. (Figure G) You will see all of your user databases in this window and simply need to select the database that contains your data and click next.

Figure G

                         Publication database

      Now, you need to select the tables that contain the data you wish to replicate. (Figure H) You can replicate data only from tables that have a primary key. If you data does not have a primary key, you will need to create one before this step. You can choose to replicate a whole table (or tables) worth of data.

Figure H

                            Select tables

     You can also choose to replicate only certain columns from a table. (Figure I) This is useful if you need just a subset of your data to populate a Web-based or other application. Doing this will keep the size of your snapshot down and minimize the space requirements on the subscriber.

Figure I

                          Replicate columns

     You can also filter your data if you need to. (Figure J) However, this is a more advanced option and is not recommended unless you are comfortable with manipulating the data you will be publishing.

Figure J

                              Filter data

      You can choose to take your snapshot then or schedule it. (Figure K) The snapshot will lock tables while it copies the data out of them (to preserve replication integrity). The agent does run speedily, but if you have a large amount of data it can still take some time, so be sure to schedule accordingly.

Figure K

                          Snapshot Agent

        You can then provide credentials for the snapshot agent. (Figure L) If your SQL Server Agent service does not have permissions to the directory you choose to place your snapshots in, you will need to provide a different authentication account to provide this access.

Figure L

                         Credentials

       After selecting from a few more menus (where nothing but the defaults are needed), you can name your publication. (Figure M) You can send this same publication to many subscribers, so you will want to name the publication by the data it contains and not where it is going.

Figure M

                           Name your publication

       You are now ready to set up subscribers to your publication. (Figure N) Subscribers are the servers that will be receiving your replicated data. To do this, right-click on the publication you have just created and select New Subscriptions.

Figure N

                         Set up subscribers

       You will first need to select the publication you wish to replicate. (Figure O) If you are using a separate distributor system, you will need to connect to the SQL Server that houses the publication you will replicate.

Figure O

                        Select replication publication

       Now you can select your subscribers. (Figure P) This is done by selecting Add Subscriber, which will prompt you with the standard SQL Server Management Studio prompt for a server connection. Simply provide the server name and your preferred type of authentication. You can also add multiple servers as subscribers at this point by repeating this step.

Figure P

                          Select subscribers

       You can configure your authentication type for the distribution agent next. (Figure Q) If you wish to use a specific account to push to the data to the subscription server, this is where you do it. You can choose to use the SQL Server Agent account, a Windows account, or an SQL account, depending upon your own security infrastructure.

Figure Q

                         Authentication type

      Next, you will set the schedule. (Figure R) You can set the agent to run continuously, which will provide a near real-time replication of your data. You can also replicate at any schedule you wish. This is useful if you have a slow WAN and need to maximize bandwidth during certain hours.

Figure R

                          Set the schedule

       You can now see your subscriber under the publication. (Figure S) From here, you can modify the properties of your subscription, reinitialize the subscription, view your synchronization status, and bring up the replication monitor.

Figure S

                         Subscriber properties

       By viewing the replication monitor, you can verify that your transactions have been delivered successfully and see the amount of time it took for each step to complete. (Figure T) If you have any errors, you can also view details on them here to help your troubleshooting.

Figure T

                        View replication monitor

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Keyboard Shortcuts for Windows XP, in SQL Server

In SQL Server 2005, 2008, 2012 ,  Here is the list of Keyboard Shortcuts for Windows XP, described and categoriesed on the basis of their scope and functionalities.

SQL Server Management Studio:

CTRL+ R for Run and then SQLWB	To launch SQL Server Management Studio.
CTRL+D	Output results in text format.
CTRL+E	Execute the query.
CTRL+F	Display the Find dialog box.
CTRL+H	Display the Find and Replace dialog box.
CTRL+I	Start incremental search
CTRL+L	Display the estimated execution plan
CTRL+M	Include Actual Execution Plan.
CTRL+N	New Query Window.
CTRL+R	Show/Hide Results Pane.
CTRL+S	Save the open Script as an sql file.
CTRL+T	Output results in a grid
CTRL+W	Select the word or number on which cursor is.
CTRL+Tab	Toggles through SQL files.
CTRL+K+K	Set or remove a bookmark at the current line.
CTRL+K+N	Next bookmark.
CTRL+K+P	Previous bookmark.
CTRL+K+L	Clear bookmarks.
CTRL+K+C	Comment the selected text.
CTRL+K+U	UnComment the selected text.
CTRL+SHIFT+U	Make the selected text Upper Case.
CTRL+SHIFT+L	Make the selected text Lower Case.
F1	Displays the MSDN Help related to the word on which cursor is.
F3	Find Next.
ALT+F1	Information about all the objects in the current database.
ALT+F4	Close the SS Management Studio.
F5	Parse the query.
F6	Switch between Query, Results & Message panes.
F8	Display Object Explorer.
ALT+F	Display the File menu.
ALT+E	Display the Edit menu.
ALT+V	Display the View menu.
ALT+Q	Display the Query menu.
ALT+P	Display the Project menu.
ALT+T	Display the Tools menu.
ALT+W	Display the Windows menu.
ALT+C	Display the Community menu.
ALT+H	Display the Help menu.
ALT+X	Execute the query.
ALT+Break	Cancel the executing query.

General keyboard shortcuts:

CTRL+C	Copy
CTRL+X	Cut
CTRL+V	Paste
CTRL+Z	Undo
DELETE	Delete
SHIFT+DELETE	Delete the selected item permanently without placing the item in the Recycle Bin
CTRL while dragging an item	Copy the selected item
CTRL+SHIFT while dragging an item	Create a shortcut to the selected item
F2 key	Rename the selected item
CTRL+RIGHT ARROW	Move the insertion point to the beginning of the next word
CTRL+LEFT ARROW	Move the insertion point to the beginning of the previous word
CTRL+DOWN ARROW	Move the insertion point to the beginning of the next paragraph
CTRL+UP ARROW	Move the insertion point to the beginning of the previous paragraph
CTRL+SHIFT with any of the arrow keys	Highlight a block of text
SHIFT with any of the arrow keys	Select more than one item in a window or on the desktop, or select text in a document
CTRL+A	Select all
F3 key	Search for a file or a folder
ALT+ENTER	View the properties for the selected item
ALT+F4	Close the active item, or quit the active program
ALT+ENTER	Display the properties of the selected object
ALT+SPACEBAR	Open the shortcut menu for the active window
CTRL+F4	Close the active document in programs that enable you to have multiple documents open simultaneously
ALT+TAB	Switch between the open items
ALT+ESC	Cycle through items in the order that they had been opened
F6 key	Cycle through the screen elements in a window or on the desktop
F4 key	Display the Address bar list in My Computer or Windows Explorer
SHIFT+F10	Display the shortcut menu for the selected item
ALT+SPACEBAR	Display the System menu for the active window
CTRL+ESC	Display the Start menu
ALT+Underlined letter in a menu name	Display the corresponding menu
Underlined letter in a command name on an open menu	Perform the corresponding command
F10 key	Activate the menu bar in the active program
RIGHT ARROW	Open the next menu to the right, or open a submenu
LEFT ARROW	Open the next menu to the left, or close a submenu
F5 key	Update the active window
BACKSPACE	View the folder one level up in My Computer or Windows Explorer
ESC	Cancel the current task
SHIFT when you insert a CD-ROM into the CD-ROM drive	Prevent the CD-ROM from automatically playing
CTRL+SHIFT+ESC	Open Task Manager

Dialog box keyboard shortcuts:

If you press SHIFT+F8 in extended selection list boxes, you enable extended selection mode. In this mode, you can use an arrow key to move a cursor without changing the selection. You can press CTRL+SPACEBAR or SHIFT+SPACEBAR to adjust the selection. To cancel extended selection mode, press SHIFT+F8 again. Extended selection mode cancels itself when you move the focus to another control.

CTRL+TAB	Move forward through the tabs
CTRL+SHIFT+TAB	Move backward through the tabs
TAB	Move forward through the options
SHIFT+TAB	Move backward through the options
ALT+Underlined letter	Perform the corresponding command or select the corresponding option
ENTER	Perform the command for the active option or button
SPACEBAR	Select or clear the check box if the active option is a check box
Arrow keys	Select a button if the active option is a group of option buttons
F1 key	Display Help
F4 key	Display the items in the active list
BACKSPACE	Open a folder one level up if a folder is selected in the Save As or Open dialog box

Microsoft natural keyboard shortcuts:

Windows Logo	Display or hide the Start menu
Windows Logo+BREAK	Display the System Properties dialog box
Windows Logo+D	Display the desktop
Windows Logo+M	Minimize all of the windows
Windows Logo+SHIFT+M	Restore the minimized windows
Windows Logo+E	Open My Computer
Windows Logo+F	Search for a file or a folder
CTRL+Windows Logo+F	Search for computers
Windows Logo+F1	Display Windows Help
Windows Logo+ L	Lock the keyboard
Windows Logo+R	Open the Run dialog box
Windows Logo+U	Open Utility Manage

Accessibility keyboard shortcuts:

Right SHIFT for eight seconds	Switch FilterKeys either on or off
Left ALT+left SHIFT+PRINT SCREEN	Switch High Contrast either on or off
Left ALT+left SHIFT+NUM LOCK	Switch the MouseKeys either on or off
SHIFT five times	Switch the StickyKeys either on or off
NUM LOCK for five seconds	Switch the ToggleKeys either on or off
Windows Logo +U	Open Utility Manager

Windows Explorer keyboard shortcuts:

END	Display the bottom of the active window
HOME	Display the top of the active window
NUM LOCK + Asterisk sign(*)	Display all of the subfolders that are under the selected folder
NUM LOCK + Plus sign(+)	Display the contents of the selected folder
NUM LOCK + Minus sign(-)	Collapse the selected folder
LEFT ARROW	Collapse the current selection if it is expanded, or select the parent folder
RIGHT ARROW	Display the current selection if it is collapsed, or select the first subfolder

Shortcut keys for Character Map:

After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:

RIGHT ARROW	Move to the right or to the beginning of the next line
LEFT ARROW	Move to the left or to the end of the previous line
UP ARROW	Move up one row
DOWN ARROW	Move down one row
PAGE UP	Move up one screen at a time
PAGE DOWN	Move down one screen at a time
HOME	Move to the beginning of the line
END	Move to the end of the line
CTRL+HOME	Move to the first character
CTRL+END	Move to the last character
SPACEBAR	Switch between Enlarged and Normal mode when a character is selected

Microsoft Management Console (MMC) main window keyboard shortcuts:

CTRL+O	Open a saved console
CTRL+N	Open a new console
CTRL+S	Save the open console
CTRL+M	Add or remove a console item
CTRL+W	Open a new window
F5 key	Update the content of all console windows
ALT+SPACEBAR	Display the MMC window menu
ALT+F4	Close the console
ALT+A	Display the Action menu
ALT+V	Display the View menu
ALT+F	Display the File menu
ALT+O	Display the Favorites menu

MMC console window keyboard shortcuts:

CTRL+P	Print the current page or active pane
ALT+Minus sign(-)	Display the window menu for the active console window
SHIFT+F10	Display the Action shortcut menu for the selected item
F1 key	Open the Help topic, if any, for the selected item
F5 key	Update the content of all console windows
CTRL+F10	Maximize the active console window
CTRL+F5	Restore the active console window
ALT+ENTER	Display the Properties dialog box, if any, for the selected item
F2 key	Rename the selected item
CTRL+F4	Close the active console window. When a console has only one console window, this shortcut closes the console

Remote desktop connection navigation:

CTRL+ALT+END	Open the Microsoft Windows NT Security dialog box
ALT+PAGE UP	Switch between programs from left to right
ALT+PAGE DOWN	Switch between programs from right to left
ALT+INSERT	Cycle through the programs in most recently used order
ALT+HOME	Display the Start menu
CTRL+ALT+BREAK	Switch the client computer between a window and a full screen
ALT+DELETE	Display the Windows menu
CTRL+ALT+Minus sign(-)	Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer.
CTRL+ALT+Plus sign(+)	Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.

Microsoft Internet Explorer navigation:

CTRL+B	Open the Organize Favorites dialog box
CTRL+E	Open the Search bar
CTRL+F	Start the Find utility
CTRL+H	Open the History bar
CTRL+I	Open the Favorites bar
CTRL+L	Open the Open dialog box
CTRL+N	Start another instance of the browser with the same Web address
CTRL+O	Open the Open dialog box, the same as CTRL+L
CTRL+P	Open the Print dialog box
CTRL+R	Update the current Web page
CTRL+W	Close the current window

Other information:

• Some keyboard shortcuts may not work if StickyKeys is turned on in Accessibility Options.
• Some of the Terminal Services client shortcuts that are similar to the shortcuts in Remote Desktop Sharing are not available when you use Remote Assistance in Windows XP Home Edition.

 Reference: http://support.microsoft.com/kb/301583

Reference: Govind Badkur(http://govindbadkur.blogspot.com)