Monthly Archives: August 2015

How to determine what stored procedures relate to what tables

SQL Server, , , , , , ,

Foreign keys are usually a good indicator of which tables connect to each other however you may be working in an environment that does not always follow best practices. As well as that you may not know what store procedures relate to what tables.
Sometimes databases need to move from one server to another or are depreciated. Before this can happened it is a very good idea to do a compressive assessment to see what interacts with what.
As once a thing gets moved everything that queries against it needs to point to it its new location.
A good starting point of this assessment would be to use the query below to determine what stored procedures reference the table specified in the WHERE clause.

  • NOTES: This query only pulls the tabled referenced in the database, it does not pull tables that are referenced from other databases.
  • Although the table maybe reference it may not actually be interacted with by the query, the table name may be comment in the code for example. However for the most part it is more likely the table has some action performed against it, be it a SELECT, INSERT, UPDATE etc.

The query works by using the sysobjects and syscomments tables.
Sysobjects: a system table that contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.

Syscomments: a system table that contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

Or to dumb it down it joins, in our case, the stored procedures name to the query and scans this query for references to the specified table.

/*
CHANGE THE FOLLOWING:
SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
TABLE NAME: TableName
 */


USE DatabaseName;
GO

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%TableName%'

To expand on this logic to return every table referenced in every stored procedure in a database run the code below:

/*
CHANGE THE FOLLOWING:
SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
 */
 
USE DatabaseName;
GO 

--DROP TEMP TABLES
IF OBJECT_ID('tempdb..#TablesAndSPs') IS NOT NULL
	DROP TABLE #TablesAndSPs

IF OBJECT_ID('tempdb..#Tables') IS NOT NULL
	DROP TABLE #Tables
	
--DECLARE VARIABLES
DECLARE @tableID AS INT
DECLARE @tableName AS VARCHAR(255)

--CREATE TEMP TABLE TO HOLD THE NAMES OF THE TABLES AND SPs
CREATE TABLE #TablesAndSPs (
	TABLE_ID INT
	,TABLE_NAME VARCHAR(255)
	,SP VARCHAR(255)
	)

--GET A LIST OF THE TABLES IN DATABASE
SELECT row_number() OVER (
		ORDER BY TABLE_NAME
		) AS TABLE_ID
	,TABLE_NAME
INTO #Tables
FROM INFORMATION_SCHEMA.TABLES

SET @tableID = 1

--LOOP AND POPULATE #TablesAndSPs
WHILE @tableID <= (
		SELECT MAX(TABLE_ID)
		FROM #Tables
		)
BEGIN
	SET @tableName = (
			SELECT TABLE_NAME
			FROM #Tables
			WHERE TABLE_ID = @tableID
			)

	INSERT INTO #TablesAndSPs
	SELECT DISTINCT @tableID
		,@tableName
		,so.NAME
	FROM syscomments sc
	INNER JOIN sysobjects so ON sc.id = so.id
	WHERE sc.TEXT LIKE '%' + @tableName + '%'

	SET @tableID = @tableID + 1
END

SELECT * FROM #TablesAndSPs

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 remove NaN and Infinity from a SSRS table

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

Typically this occurs when a field uses an expression in a report table were the expression tries to divide a number by zero (Or the field can populate with #Error when there is a NULL involved). The best solution to resolve this problem is to create a custom function.

Right click on the background of your report (i.e. just below where it says Design) and go to Report Properties as shown:

picture showing user where to click in the reportThen you can left click on Code and add enter the custom code below in the window provided:

Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double
   If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
      Return 0
   Else
      Return dividend / divisor
   End If
End Function

IsNothing() is used to avoid #Error and check whether the divisor or the dividend are 0 to avoid NaN and Infinity.

Now change the field expression from, for example:

SUM(Fields!A.Value)/SUM(Fields!B.Value)

To the expression below using the newly defined function Divide:

= Code.Divide(Sum(Fields!A.Value), Sum(Fields!B.Value))

Next you can update the fields textbox properties to make the number display as a percentage.

NOTE: Inserting a user defined function into a table will mean that that table will no longer be able to be copied and pasted as this throws an error. To copy and past the table you will need to look at the code of the report by right clicking on the report and choosing View Code. Search for “Code.Divide” and comment it out with an apostrophe ( ‘ ). You will now be able to copy the table.

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.

What is BCP?

SQL Server, , ,

BCP (bulk copy program) is a utility that installs with SQL Server and can assist with large data transfers.

“The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.” – Microsoft

To see the parameter options from the command line on a machine with SQL Server installed, type “bcp” and press Enter.

bcp in c.m.d. windowFor further information please see the LINK.

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

How to populate a temp table with the days in a month

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

As described in the title this is a dynamic sql script that will populate a temp table with the day dates of the current month as outputted below:

calendarTableTo create a temporary calendar table for a greater period of time than just the current month:

Set @startDate to any historical date and @numberOfLoops to any number you like (365 being a year from the @startdate).

To make the table persistent:

Remove the # signs from the #calendarTable temp table referenced and rename the table accordingly.

You could also remove any columns you do not need.

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

CREATE TABLE #calendarTable (
	"DayID" INT
	,"DayOfMonth" INT
	,"DayOfQuarter" INT
	,"DayOfYear" INT
	,"MonthOfDate" INT
	,"YearOfDate" INT
	,"DayDate" DATE
	,PRIMARY KEY (DayID)
	)

DECLARE @startDate AS DATE
DECLARE @baseDate DATE
	,@offSet INT
	,@numberOfLoops INT

/*
To create a temporary calendar table:
Set @startDate to any historical date 
and @numberOfLoops to any number you like 
(365 being a year from the @startdate)

To make the table persistent remove the # signs 
from the #calendarTable temp table referenced 
*/
SET @startDate = GETUTCDATE() /*'20150101'*/
SET @baseDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate), 0)
SET @offSet = 0
SET @numberOfLoops = DATEDIFF(DAY, @baseDate, DATEADD(MONTH, 1, @baseDate)) /*365*/

WHILE (@offSet < @numberOfLoops)
BEGIN
	INSERT INTO #calendarTable (
		"DayID"
		,"DayOfMonth"
		,"DayOfQuarter"
		,"DayOfYear"
		,"MonthOfDate"
		,"YearOfDate"
		,"DayDate"
		)
	SELECT (@offSet + 1)
		,DATEPART(DAY, DATEADD(DAY, @offSet, @baseDate))
		,DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, DATEADD(DAY, @offSet, @baseDate)), 0), DATEADD(DAY, @offSet, @baseDate)) + 1
		,DATEPART(DAYOFYEAR, DATEADD(DAY, @offSet, @baseDate))
		,DATEPART(MONTH, DATEADD(DAY, @offSet, @baseDate))
		,DATEPART(YEAR, DATEADD(DAY, @offSet, @baseDate))
		,DATEADD(DAY, @offSet, @baseDate)

	SET @offSet = @offSet + 1
END

SELECT *
FROM #calendarTable

How to fix an SSRS Report that cannot find stored procedure fields or parameters while displaying the define query parameters window

SSRS Reporting, , , , , , , , , , , , , , , , ,

When setting up your data sets in an SSRS report if you are using a complicated stored procedure, i.e. a SP which relies on dynamic SQL, temp tables or finishes with an IF statement, chances are the SSRS report will not be able to figure out what the SP returns. When this happens you will not be able to populate the data set with data.

This happens because the execution plan of the SSRS software isn’t smart enough and won’t be able to determine what fields the SP creates and therefore will not be able to create a means of storing the data on the report end.

Subsequently you’ll see the, often misleading, table below popup.

 

SSRS Pop up Define Query Parameters

The solution to stop this from happening is quite simple, but considering how expensive this software is it’s a solution that shouldn’t have to be employed.

The solution is to trick the SSRS software by simplifying the SP. Basically perform a select on the specific fields you need with no additional logic or create a table with the exact fields you need with corresponding data types and select from that.

Use this dumb SP to populate the dataset in the SSRS report.

In the “Choose a data source and create a query” window as below, click refresh fields.

SSRS window refresh fields
The software should now pick up the fields.
Change the SP back to the way it was before and do not refresh the fields again in the SSRS software and the fields should continue to populate as you need them.