Tag Archives: lesson

How to identify the location of the SQL Server Error Log file

dba, sql, SQL Server, T-SQL, , , , , , , , , ,

So you want to find out where the SQL Server Error Log file is located for a specific SQL Server instance?

The Error Log is a great place to start tracking down reasons why SQL Server might be experiencing problems.

The queries below (first one works on SQL Server 2008, second one works on 2016) use the extended stored procedure XP_READERRORLOG, which is typically used to read error logs directly, to return the location of the SQL Server Error Log files. The returned value is then cleaned up so only the directory location is provided. You can then copy and paste the result into your file explorer. 

SQL Server 2008

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file'
	,NULL
	,NULL
	,N'asc';

-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir AS ErrorFileLocation

DROP TABLE #TmpErrorLog

SQL Server 2016

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';


-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir AS ErrorFileLocation

DROP TABLE #TmpErrorLog

 

Five Database icons arranged in a star with the largest one in the center

How to identify all user objects in the SQL Server Master database

sql, SQL Server, T-SQL, , , , , , , , , , , , , , , , , ,

What is the SQL Server Master Database used for?

The master database is used by SQL Server to contain all of the system level information:

  • Logins
  • Linked servers
  • Endpoints
  • Configuration settings
  • Information about the other databases on this instance and the location of their files

If the master database is not present, SQL Server cannot start.

This is way you should always take regular backups of the master database.
as if SQL Server suffers a failure, those changes will be lost and you’ll be in a lot of trouble.

So the master database is needed for SQL Server to work. Logic and objects needed to enable your application should not be tangled up in there so you should always avoid creating objects in the master database. Use a user database instead.

In saying that everyone has made the mistake of not specifying the database they want to USE in a script and by default objects get written to the Master database. Or you might have inherited the responsibility for an application which did not conform to best practices and it uses objects created in Master.

To gain visibility of these objects just run the script below.

SELECT o.object_id AS ObjectId
	,o.NAME AS ObjectName
	,o.type_desc AS ObjectType
	,o.create_date AS CreateDate
	,o.modify_date AS ModifyDate
	,SUM(st.row_count) AS RowCnt
	,CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) AS DataSize_MB
FROM master.sys.objects o
LEFT JOIN master.sys.dm_db_partition_stats st ON st.object_id = o.object_id
	AND st.index_id < 2
GROUP BY o.object_id
	,o.NAME
	,o.type_desc
	,o.create_date
	,o.modify_date
	,o.is_ms_shipped
HAVING o.is_ms_shipped = 0
	AND o.NAME <> 'sp_ssis_startup'
	AND o.type_desc NOT LIKE '%CONSTRAINT%'
ORDER BY CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) DESC
	,RowCnt DESC

How to create a running total in SQL

Data, sql, T-SQL, tsql, , , , , , , , , ,

Below is a simple example of how to create a running total in T-SQL using a self-join.

The total is created by summing the preceding values, the precedence of which is determined by, in this instance, the sale date field.

A date field, or another unique order field, is required for this technique to work otherwise all the values would be summed at once, based on grouping, and the summed value would be displayed for each relevant record.

CREATE TABLE #Product (
Product_ID INT
,Product VARCHAR(10)
)

CREATE TABLE #Sale (
    Product_ID INT
    ,SaleAmount MONEY
    ,SaleDate DATETIME
    )

INSERT INTO #Product
VALUES (
    1
    ,'Bike'
    )
    ,(
    2
    ,'Car'
    )
    ,(
    3
    ,'Truck'
    )


INSERT INTO #Sale
VALUES (
    1
    ,10
    ,'20150101 12:00:00.000'
    )
    ,(
    1
    ,10
    ,'20150102 13:00:00.000'
    )
    ,(
    2
    ,20
    ,'20150101 13:00:00.000'
    )
    ,(
    2
    ,30
    ,'20150101 14:00:00.000'
    )
    ,(
    3
    ,30
    ,'20150101 12:00:00.000'
    )

SELECT a.Product_ID
    ,p.Product
    ,a.SaleAmount
    ,SUM(b.SaleAmount) AS RunningTotal
    ,a.SaleDate
FROM #Sale AS a
INNER JOIN #Product AS p ON a.Product_ID = p.Product_ID   
LEFT JOIN #Sale AS b ON a.Product_ID = b.Product_ID
    AND b.SaleDate <= a.SaleDate
GROUP BY a.Product_ID
    ,p.Product
    ,a.SaleAmount
    ,a.SaleDate


DROP TABLE #Sale
DROP TABLE #Product
report cartoon

How to keep an SSRS Report on one page

SQL Server, SSRS, SSRS Reporting, , , , , , , , , , ,

Using visual studio left click just under design to focus on the report.

Image showing where to click

Now the report properties window should be visible in the bottom right of the screen.

Set the interactive height to 0 as shown below.

Properties windowYou will get the following warning sign below. Limiting the report to one page, by setting the interactive height to zero, means everything needs to be loaded all at once. This will create lag when a user loads the report and interacts with it.

warning message stating can cause performance issuesIf you are basically using SSRS as a means for end users to pull a data dump via excel, heed this warning. If you just have a few aggregated tables and you want them all on the same page you should be fine.

How to connect your android device to a Playstation 3 or 4 controller wirelessly

Android, How To, , , , , , , , , , , , , , , , , , , , , , , , ,

A list of directly compatible games is at the bottom of this post.

Prerequisites:

Firstly you will need your device rooted. Rooting the device just means you have administrative privileges on the device, but unlike windows it’s not as simple as just changing a setting. Please see this tutorial on checking if an android device is rooted and this tutorial if you need instruction on rooting your device.

Connecting your PS3/4 controller to your android device:

As with all things android you might have guessed you need an app to connect your device to your Playstation controller. In this case you need the sixaxis controller. Unfortunately the app isn’t free but it is cheap!

However before you go handing over your dosh the guys at dancing pixel studios (strange choice of names considering they don’t seem to make any games) were nice enough to create the sixaxis compatibility checker so you can try before you buy.

Step 1.

Install the compatibility checker from the play store here.

Step 2.

Once installed, open the app and click Start. If you get a connection failed your device is not supported.

Sorry dude.

If not, Yay!

Continue!

Step 3.

Connect your controller to your device with a mini usb cable.

Step 4.

Click pair. This will write your android’s Bluetooth address to the controller. (Don’t worry about the pad not working on the playstation anymore, they’re a terrible console anyway. JK, to reconnect your controller to your playstation just connect the controller via the usb cable and it will write the playstation address back onto the controller.)

Step 5.

Once paired click start on the app. The device is now listening for controllers. If the controller is not turned on press the power button in the center. Now when you press buttons on the controller you should see them appearing on screen.

Congratulations, your device is compatible you now have the honour of buying the app here.

Some setup advice:

When you first use the app you’ll be asked to tick the box recognising Sixaxis Controller as a Keyboard & Input Method. Do so. It asks because this is a security feature as such devices could be potential recording key strokes etc.

In general settings I always have the box Auto Start ticked, this means the app launches whenever the device is turned. For the idle timeout option I set the timeout for 300, which means if the controller is not interacted with for five minutes the app turns the controller off saving the battery. On the subject of battery you can use your device to charge the controller which is nice.

Not all games are compatible even more so if you’re using an Android box or stick:

Native compatible of a game means that it was programmed to accept inputs from controllers some great examples being BombSquad, Asphalt 8, Dead Trigger however, sadly, a lot of games do not support native compatibility.

This could be for a number of reasons, like the developer just never bothered to code compatibility into the app or the app might compete with a console version of the game and the developer doesn’t want to cannibalize the market. Man I wished Fifa had controller support. . .

However the sixaxis controller app comes with a great feature to map button presses. This is especially useful to the PUBG mobile fans out there.

How to do this though is another post in itself so I suggest you google around, there’s plenty of great tutorials out there and the gentle souls of the internet have even created touch profiles for games available to download saving you the trouble of doing the mapping yourself.

Sadly though mapping on many Android boxes and sticks don’t seem to work to well if at all. This is down to these devices often shipping without the necessary touch drivers because they don’t have screens. Which makes sense, why ship a device with touch drivers that doesn’t have a screen? For PUBG mobile players who want to use a controller of course!

The following is a list of games that you can play using the android sixaxis controller app.

Remapping of keys maybe necessary, the correct mapping is as follows.

  • Cross: A
  • Circle: B
  • Square: X
  • Triangle: Y

Games:

I’ll be adding to this list over time so if I’ve missed a game you’ve tested and know to work please comment below.

  1. Dead Trigger (free)
  2. Dead Trigger 2 (free)
  3. Evac
  4. Asphalt 8 (free)
  5. Dead Effect (free)
  6. Beach Buggy Blitz (free)
  7. Beach Buggy Racing (free)
  8. Dream League Soccer (free)
  9. BombSquad (free)
  10. Horizon Chase (free)
  11. PewPew (free)
  12. PewPew 2
  13. Skyriders (free)
  14. ShadowGun
  15. SoulCraft (free)
  16. Real Boxing (free)
  17. Skate Party 2 (free)
  18. GT Racing 2
  19. Manuganu 2 (free)
  20. Shooty Skies (free)
  21. Reckless Racing
  22. Rail Racing
  23. Jet Car Stunts 2
  24. Aces of the Luftwaffe (free)
  25. Tank Riders (free)
  26. Raging Thunder 2 (free)
  27. Annelids (free)
  28. Warlings: Armageddon (free)
  29. Table Top Racing (free)
  30. Sine Mora
  31. Pako – Car Chase Simulator (free)
  32. Sword Of Xolan (free)
  33. Nub’s Adventure (free)
  34. Super Dangerous Dungeons (free)
  35. Only One (free)
  36. Turbo Dismount (free)
  37. Particle Arcade Shooter
  38. Luminescence (free)
  39. Leo’s Fortune
  40. Does Not Commute
  41. Pac-Man (free)
  42. Redline Rush (free)
  43. Radiant (free)
  44. Zenonia
  45. Voxel Rush (free)
  46. Winter Fugitives *
  47. Hopeless *
  48. Tiny Thief *
  49. Smash Hit * (free)

Any other suggestions please feel free to add a comment below.

How to create Clustered and Nonclustered Indexes on a Temp Table

SQL Server, , , , , , , , , , , , , , , ,

One of the best features of temp tables is that an index can be applied to them.

To clarify temp tables start with #, exist within the tempdb and are accessible within child batches (nested triggers, procedure, exec calls) of the query.

The execution plan can also determine the relevant statistics regarding their operation and suggest means of optimisation and will often suggest applying an index to the table.
Below is a simple example of applying both clustered and nonclustered indexes to the temporary table #Apostle created from a CTE (Common Table Expression).

 

IF OBJECT_ID('tempdb..#Apostle') IS NOT NULL
	DROP TABLE #Apostle;

WITH CTE
AS (
	SELECT 1 AS ID
		,'Simon Peter' AS NAME
	
	UNION
	
	SELECT 2
		,'James'
	
	UNION
	
	SELECT 3
		,'John'
	
	UNION
	
	SELECT 4
		,'Andrew'
	
	UNION
	
	SELECT 5
		,'Philip'
	
	UNION
	
	SELECT 6
		,'Thomas'
	
	UNION
	
	SELECT 7
		,'Bartholomew'
	
	UNION
	
	SELECT 8
		,'Matthew'
	
	UNION
	
	SELECT 9
		,'James'
	
	UNION
	
	SELECT 10
		,'Simon'
	
	UNION
	
	SELECT 11
		,'Thaddaeus'
	
	UNION
	
	SELECT 12
		,'Judas'
	)
SELECT *
INTO #Apostle
FROM CTE

-- CREATE INDEXES
CREATE CLUSTERED INDEX IDX_CLUSTERED_ID ON #Apostle (ID)

CREATE NONCLUSTERED INDEX IDX_NONCLUSTERED_ID ON #Apostle (ID)

 

Microsoft defines clustered and nonclustered indexes as the following:

Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

  • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

Source

To learn more about indexes this is a good video on the topic.

How to write text to a file with SQL Server

SQL Server, , , , , , , , , , , , , , , ,

The following is a tutorial on creating a stored procedure that will allow you to create a file in any directory and insert text into this newly created file.

In order for this to work you will need to authorise the running of system stored procedures with Object Linking and Embedding functionality (See OLE).

Authorisation is needed as the stored procedure we will create rely on the system SPs sp_OACreate and sp_OAMethod.

sp_OACreate: Creates an instance of an OLE object.

sp_OAMethod: Calls a method of an OLE object.

NOTE: By default, SQL Server blocks access to OLE Automation stored procedures by turning the components off as part of the security configuration for the server.

Run the script below to grant authorisation.

--AUTHORIZE SYSTEM STORED PROCEDURES
sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures'
	,1;
GO

RECONFIGURE;
GO

--AUTHORIZED

 
Next we will create the stored procedure WriteToFile. Substitute the DatabaseName with the database you will be using.

--CREATE STORED PROCEDURE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[WriteToFile]', 'P') IS NOT NULL
	DROP PROCEDURE [WriteToFile];
GO

CREATE PROCEDURE [dbo].[WriteToFile] @File VARCHAR(255)
	,@Text VARCHAR(MAX)
	WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @OLE INT
	DECLARE @FileID INT

	EXECUTE sp_OACreate 'Scripting.FileSystemObject'
		,@OLE OUT

	EXECUTE sp_OAMethod @OLE
		,'OpenTextFile'
		,@FileID OUT
		,@File
		,8
		,1

	EXECUTE sp_OAMethod @FileID
		,'WriteLine'
		,NULL
		,@Text

	EXECUTE sp_OADestroy @FileID

	EXECUTE sp_OADestroy @OLE
END;

 
Below is an example using the WriteToFile stored procedure.

--WRITE TO FILE EXAMPLE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'
SET @Txt = 'Hello World'

EXEC [DatabaseName].[dbo].[WriteToFile] @Path
	,@Txt;

 

Using the same stored procedure here’s an example writing a table to the file using concatenation and a loop. This process works by taking the table row by row and writing the concatenated value to the file. There are easier ways to achieve this however, i.e. utilising the export functionality built into SSMS and saving this as a package to be run as a job.

--STORED PROCEDURE CREATED
--WRITE TABLE TO FILE EXAMPLE
DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)
DECLARE @loopId AS INT
DECLARE @maxId AS INT
DECLARE @TempCustomer TABLE (
	ID_column INT IDENTITY PRIMARY KEY
	,FirstName NVARCHAR(100)
	);

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'

INSERT INTO @tempCustomer (FirstName)
VALUES ('Paul')
	,('Jim')
	,('John')

SET @loopId = 1
SET @maxId = (
		SELECT MAX(ID_column)
		FROM @TempCustomer
		)

WHILE @loopId <= @maxId
BEGIN
	SELECT @Txt = CONVERT(VARCHAR(10), ID_column) + ', ' + FirstName
	FROM @TempCustomer
	WHERE ID_column = @loopId

	PRINT @Txt

	EXEC [TEST_DB].[dbo].[WriteToFile] @Path
		,@Txt;

	SET @loopId = @loopId + 1
END

 

How to save game scores in Corona SDK by writing to a file.

Corona SDK, Game Development, Lua, , , , , , , , , , , , ,

The scope of this tutorial will be to record and store the highest score achieved by a player in a game. This is done by:

  • Creating a file with a score of zero where the file does not exists.
  • When a player dies:
    • The previous score, i.e. value within the file, is assigned to a variable.
    • The player’s new score is assigned to a variable.
  • The two variables are compared.
  • If the previous score is higher nothing is changed.
  • If the previous score is lower the new score will be written to the file in its place.

Note:

No UI is provided as part of the tutorial as corona sdk often changes the manner in which objects are displayed, meaning the UI could break with future corona sdk versions. We will be working from the simulator output window alone.

For security reasons, you are not allowed to write files in the system.ResourceDirectory (the directory where the application is stored). You must specify either system.DocumentsDirectory, system.TemporaryDirectory, or system.CachesDirectory in the system.pathForFile() function when opening the file for writing. Read move about this here.

Below is a table describing when and where each directory should be used.

systemDirectoriesTo use this tutorial create a folder containing a main.lua file.
Paste the code below into the file and save.
Open the file with Corona SDK and the score.txt file will be created and populated with a score of zero.

Play around with the Player Score variable:
newScore = 99

Enter a higher score and it will overwrite what currently exists in the file.

Enter a lower score and nothing will be changed.

-- main.lua

local path = system.pathForFile( "score.txt", system.DocumentsDirectory )

deleteFile = function()
 local result, reason = os.remove(path) 
	if result then
		print( "File removed" )
	else
		print( "File does not exist", reason )  --> score.txt: No such file or directory
	end
end

--[[ 
Uncomment below to remove file
--]]

--deleteFile()

-- Player Score

newScore = 99

-- io.open opens a file at path. returns nil if no file found
-- fh short for file handle
-- "r" is the read instruction
local fh, reason = io.open( path, "r" )

if fh then
    -- Read all contents of file into a variable oldScore
	-- This will be the previous score
	-- To read file content as number use "*number"
	-- To read file content as text use "*a"
	-- "\n" new line
    local oldScore = fh:read( "*number" )
    print( "Old contents of " .. path .. "\n" .. oldScore )
	
	if oldScore < newScore then
		-- re-opening the file in "w+" mode will erase all previous data stored in the file
		-- in the comments below is a table listing all the file mode types
		fh = io.open( path, "w+" )
		-- Set the score to the player's new score.
		fh:write( newScore )
		print( "New contents of " .. path .. "\n" .. newScore )
	end	
else
	-- Error logic
    print( "Reason open failed: " .. reason )  -- display failure message in terminal

    -- create file because it doesn't exist yet
    fh = io.open( path, "w" )

    if fh then
        print( "Created file" )
    else
        print( "Create file failed!" )
    end
	
	-- Set the score to zero.
    fh:write( 0 )

end

io.close( fh )

--[[
The various file modes are listed in the following table:

"r"	Read-only mode and is the default mode where an existing file is opened.
"w"	Write enabled mode that overwites existing file or creates a new file.
"a"	Append mode that opens an existing file or a creates a new file for appending.
"r+" Read and write mode for an existing file.
"w+" All existing data is removed if file exists or new file is created with read write permissions.
"a+" Append mode with read mode enabled that opens an existing file or creates a new file.

]]--

See the lua online book’s I/O library section for more information on working with files.

How to export a table as an XML file using a SQL script

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , ,

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.

How to enable and disable xp_cmdshell

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , , , , , ,

xp_cmdshell is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.

By default, the xp_cmdshell option is disabled on new installations. Along with other unwanted behavior malicious users can elevate their privileges with the use of xp_cmdshell. For this reason it is generally recommend to leave xp_cmdshell disabled. It can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

 

To disable xp_cmdshell use the following code example. Note: The following example also sets show advanced options server configuration option to 0. It is best practice to leave this option in the default state of 0 to stop the enabling of features.

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO