SQL SERVER – What are the Different Ways to Script Procedure with T-SQL

Ever wondered how a simple task can be done multiple ways. You can script a stored procedure in many ways. You can use SSMS tool and use Generate Script option. However, if you want to do it in T-SQL, there are four ways.

Let us create this simple procedure

CREATE PROCEDURE TESTING
(@OPTION INT)
AS
IF
@OPTION=0
SELECT 1 AS NUMBER
ELSE
SELECT
100 AS NUMBER
GO

Now if you want to know the script of the above procedure, you can use the following methods

1 Use SP_HELPTEXT System Stored Procedure

EXEC sp_HELPTEXT TESTING

2 Use ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES view

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='TESTING'

3 Use OBJECT_DEFINITION function

SELECT OBJECT_DEFINITION(OBJECT_ID('TESTING'))

4 Use SYS.SYSCOMMENTS system view

SELECT TEXT FROM SYS.SYSCOMMENTS WHERE ID=OBJECT_ID('TESTING')

All the above four methods will show you the script of the procedure names TESTING.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?

SQL Server has so many things to learn and I always find it amazing. My conversations with customers often come up with security questions esp around SQL Injection. Many have claimed SQL Injection is a SQL Server problem. It takes quite some time for me to let them know there is nothing about SQL Server and SQL Injection. SQL Injection is an outcome of wrong coding practices. One of the recommendations I give is about not using Dynamic SQL. There might be some situations where you can’t avoid it. My only advice would be, avoid if possible. In this blog, I would demonstrate a SQL Injection problem due to dynamic SQL and a possible solution you can have.

Let’s assume that we have a simple search page where user can use blank search or provide filter in any field. We have provided two fields to use “First Name” and “Last Name”. The user types something and hits search. Here is our code of stored procedure which fires behind the scene.

USE AdventureWorks2014
GO
CREATE PROCEDURE search_first_or_last
@firstName NVARCHAR(50)
,
@lastName NVARCHAR(50)
AS
BEGIN
DECLARE
@sql NVARCHAR(4000)
SELECT @sql = ' SELECT  FirstName ,MiddleName, LastName' +
' FROM Person.Person WHERE 1 = 1 '
IF @firstName IS NOT NULL
SELECT @sql = @sql + ' AND FirstName LIKE ''' + @firstName + ''''
IF @lastName IS NOT NULL
SELECT @sql = @sql + ' AND LastName LIKE ''' + @lastName + ''''
EXEC (@sql)
END

If I use this string to execute in last name ”;drop table t1–

EXEC search_first_or_last '%K%', ''';drop table t1--'

The dynamic string would be

SELECT  FirstName, MiddleName, LastName FROM Person.Person WHERE 1 = 1  AND FirstName LIKE '%K%' AND LastName LIKE '';DROP TABLE t1--'

Do you see the problem? Yes, users can drop table t1 if code is running under a high privilege account.

One of the solution of the problem would be to use sp_executesql. Here is the better version using

CREATE PROCEDURE search_first_or_last
@firstName NVARCHAR(50)
,
@lastName NVARCHAR(50)
AS
BEGIN
DECLARE
@sql NVARCHAR(4000)
SELECT @sql = ' SELECT  FirstName , MiddleName, LastName' +
' FROM Person.Person WHERE 1 = 1 '
IF @firstName IS NOT NULL
SELECT @sql = @sql + ' AND FirstName LIKE   @firstName'
IF @lastName IS NOT NULL
SELECT @sql = @sql + ' AND LastName LIKE  @lastName '
EXEC sp_executesql @sql
,N'@firstName nvarchar(50), @lastName nvarchar(50)'
,@firstName
,@lastName
END

Hope you would be able to use this and implement in your project. Are you using these simple techniques in your production code? Have you ever faced similar problems during audit? Do let me know of your learnings.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Invoking a Stored Procedure from Azure Mobile Services – Notes from the Field #066

[Note from Pinal]: This is a 66th episode of Notes from the Field series. Azure Mobile Services is a very critical aspect and not many people know about it. When I read this article, I find it humorous at points and some of the examples also reminded me of my past experience. If you are in data security, you will have a great time reading these notes, but if you are not, you will still love it.

In this episode of the Notes from the Field series database expert Kevin Hazzard explains Invoking a Stored Procedure from Azure Mobile Services. Read the experience of Kevin in his own words.


One of the questions I’m often asked is whether it’s possible to call SQL stored procedures from Azure Mobile Services. The answer is yes and it’s probably easier than you think. In case you don’t know, Azure Mobile Services is a way to very simply expose an HTTP service over data stored in an Azure SQL database. By default, Azure Mobile Services exposes the SQL tables directly as resources. So the HTTP methods GET, POST, PUT and DELETE will essentially be mapped to SQL operations on the underlying tables.

While this simple mapping mechanism is good for resource-oriented access to the tables, the logic to produce a usable Web API is often a bit more complex than that. Stored procedures can provide an interesting abstraction layer that allows us to use the efficiencies of the SQL Server query engine to reduce round trips to and from Internet clients, for example. Or perhaps stored procedures might be used to hide normalization peculiarities from clients or perhaps to use advanced parameter handling logic. Whatever the case may be, it would be helpful from time to time to be able to invoke stored procedures from the HTTP API that Azure Mobile Services provides.

Let’s start by assuming that an Azure Mobile Service exists with some data that we would like to expose via a stored procedure. For the purposes of this example, my service is called MobileWeatherAlert which contains a backing table in an Azure SQL Database named [MobileWeatherAlert_db]. It’s really helpful that Azure Mobile Services uses schema separation in the underlying database to manage all of its data. That schema separation allows us to expose many separate middle-tier services from one common database if needed. So, in my weather database, there’s a schema called [MobileWeatherAlert] corresponding perfectly to the name of the service that it supports. For the purposes of this example, that schema contains a table called [Observation] which is used to collect weather data by [City].

Figure 1 shows a very simple stored procedure called [GetObservationsForCity] that I’d like to be able to call from the service API.

There are a number of places where this procedure might be invoked. For this example, I’ll implement a custom API in the mobile service called observation.

Figure 2 shows the dialog in the Azure management console where the custom API will be created.

For this simple example, I’ll only implement the HTTP GET method in the API to invoke the stored procedure. For simplicity of the example, I’ll open up access to everyone to avoid having to pass any sort of credentials. Now I can add a bit of JavaScript to the API to make the stored procedure call.

Figure 3 demonstrates adding that JavaScript to the API via the Azure management console.

Lines 1 through 9 in the script encompass the get function that will be invoked when the HTTP GET method is used to call the service. The parameters passed to the JavaScript function are the request and response objects. From the request object, line 2 shows how to obtain a reference to the mssql object which exposes a query function for making calls into the database. Line 3 demonstrates how to call the query function to execute the [GetObservationsForCity] stored procedure, passing a single parameter for the City by which to filter. It’s important to note here that the schema in which the stored procedure resides is not named in the EXEC call. This is counter-intuitive, in my opinion, and is likely to trip up novices as they experiment with this functionality. Since we are invoking the GET method for the MobileWeatherAlert service, there’s an implicit assumption used in the preparation of the SQL statement that objects will reside in a similarly-named database schema.

Notice also on Line 3 that the request object passed into the JavaScript function exposes a query property that conveniently contains an object named city which will be parsed directly from the URL.

Figure 4 shows how that URL might be passed from PostMan, a really excellent Google Chrome plug in that allows the invocation of nearly any sort of HTTP-oriented web service or API.

Finally, lines 4 through 6 of the JavaScript method, the success function that process the results of the SQL query logs the results and returns them to the caller with an HTTP 201 (OK) response. I’ve included a called to the console.log() function to show how easy it is to log just about anything when you’re debugging your JavaScript code in Azure Mobile Services. After invoking an API or custom resource method that logs something, check out the logs tab of the mobile service in the management console to see what got saved. Of course, you’ll want to do minimal logging in production but while you’re testing and debugging, the log is a valuable resource.

In studying the URL and its output in Figure 4, remember that the JavaScript for the observation API didn’t have to do any special parsing of the row set returned by SQL Server to produce this result. Simply returning that data from SQL Server caused the API to emit JavaScript Object Notation (JSON) which has arguably become the lingua franca of the Internet for expressing data.

In closing, I’ll share a couple of thoughts. If you’re interested in building a simple query interface on top of a mobile service, you don’t have to use stored procedures as shown here. Azure Mobile Services implements fairly rich OData support directly on table resources. With OData, filtering, sorting and pagination of SQL data are built in, so to speak. Also, the web way of doing services (sometimes called RESTful based on Dr. Roy Fielding’s dissertation and the HTTP standards that flowed from it), assume that we’ll use HTTP in the way it was intended: accessing and linking resources at a more basic level, using the HTTP methods GET, POST, PUT, and DELETE as a complete, fully-functional language for accessing those resources. Database people inherently understand and respect this access pattern better than many programmers working in traditional programming languages like C# and Java. After all, we’re accustomed to using four basic methods to manipulate data in our databases: SELECT, INSERT, UPDATE, and DELETE. Yet, as database people, we also know that giving software developers strict table-level access can cause all sorts of performance problems. For those situations, where you know that some complex database operation could be performed much more efficiently with a bit of T-SQL code, a stored procedure or a view may be just the prescription your developers need. Hopefully, this article has helped you understand how to invoke programmatic resource in a SQL Azure database and perhaps it will help you along the way to making the correct architectural choices in the design of your modern, data-driven web applications.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to fix the SQL Server Error 8657 or 8658?

It is sometimes very difficult to troubleshoot error which are relating to memory. In a recent query from one of my customer DBA, he sent me an error message which read like the ones below:

Error: 8657, Severity: 17, Could not get the memory grant of %I64d KB because it exceeds the maximum configuration limit in workload group ‘%ls’ (%ld) and resource pool ‘%ls’ (%ld). Contact the server administrator to increase the memory usage limit.

Error: 8658, Severity: 17, Cannot start the columnstore index build because it requires at least %I64d KB, while the maximum memory grant is limited to %I64d KB per query in workload group ‘%ls’ (%ld) and resource pool ‘%ls’ (%ld). Retry after modifying columnstore index to contain

The first instinct for me was to ask, hey you are sending me the template. Can you give me more information on the Resource Pool and other values? He said, he had not configured any Resource Governor on his server and was just trying to configure few columnstore indexes on a very large table when some of these errors were coming. Since he was part of a banking domain, he was unable to send me the exact values as per organizations security reasons. With very less information, I thought I need to dig more into these errors to understand why these happen. I was surprised, I had not written about this yet on this blog.

On quizzing few friends, I learnt that the default value for a query is limited to 25% of available memory on a server. This got me interested to find out how can this be viewed. The tip was part of the error message – check the resource governor settings.

SELECT request_max_memory_grant_percent,* FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default'
GO

As you can see, the default value is 25% preconfigured and part of standard setup. Now to answer why the columnstore index creation was erroring.

It is possible for creation of a columnstore index to fail either at the very beginning of execution if it can’t get the necessary initial memory grant, or later during execution if supplemental grants can’t be obtained. If the initial grant fails, we will see error 8657 or 8658. We may get error 701 or 802 if memory runs out later during execution.

Resolution: If out-of-memory error 8657 or 8658 occur at the beginning of columnstore index creation, first, check the resource governor settings. The default setting for resource governor limits a query in the default pool to 25% of available memory even if the server is otherwise inactive. This is true even if we have not enabled resource governor. Consider changing the resource governor settings to allow the create index statement to access more memory in such cases. The TSQL would look like:

-- Increase the value of GRANT to 50% from default of 25%
ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 50)
GO
-- RECONFIGURE to make the setting take effect
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I sent this script to my friend and told him to see the effect. I got a response last week that the query now ran without any problem and he was no longer getting the errors during index creation. Post this email conversation, I thought of uploading this script back into this blog for future reference.

I am curious to know, have you ever encountered 8657 or 8658 errors in your environments? What did you do to mitigate the errors? Would love to learn from you too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script – When/Who did Auto Grow for the Database?

What happens when you try to copy a large file from different hard disks? The operating system tries to see if the destination location has enough space available for the copy to be successful. If we don’t have enough space, then it is second to human nature to make space and restart the copy operation. I am sure most of us who use USB drives have done this task. If you look at SQL Server, it does a similar act. When there is no space in the file while writing, SQL Server goes ahead and expands the file based on our configuration. But if you are a seasoned DBA, you would want to know when these expansions happen. This blog is inspired by a question asked by one of the blog readers. All these questions are a major source of my blog content. Recently I received below email:

We see the database file size getting increase automatically every day. We are not having many active users, or transactions in the database. I read your blogs and understood that this might be due to auto grow setting of the database. Is there any way to find “who” is doing it?

The problem is half solved if we understand the reason. DBA has already nailed it down till auto growth and to help my reader further I provided him below script to find the auto growth events happen on the server. This information is pulled from Default Trace which runs by default on every SQL Server installation.

Here is the script to get the auto grow events which happened of database “SQLAuth”. Please change it as per database name in your environment

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SET @database_name = 'SQLAuth'
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT DatabaseName
,Filename
,(Duration / 1000) AS 'TimeTaken(ms)'
,StartTime
,EndTime
,(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
,ApplicationName
,HostName
,LoginName
FROM::fn_trace_gettable
(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
AND DatabaseName = @database_name
AND (d.create_date < EndTime)
ORDER BY t.StartTime DESC;

To test the script, you can created a dummy database, insert some rows so that auto growth is caused. Then check the report.
CREATE DATABASE [SQLAuth]
GO
ALTER DATABASE [SQLAuth] SET RECOVERY FULL
GO
BACKUP DATABASE [SQLAuth] TO DISK = 'NUL'
GO
USE [SQLAuth]
GO
CREATE TABLE PinalDave (Pinal INT ,Dave CHAR(8000))
GO
SET NOCOUNT ON
GO
DECLARE @i INT
SET
@i = 1
WHILE @i < 10000
BEGIN
INSERT INTO
PinalDave
VALUES (1,'Pinal Dave')
SET @i = @i + 1
END

Here is the output of the script which shows the growth event details.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How Can Deadlock Happen on Same Table?

Interesting TRUE Story

As part of learning I get inspiration from the activities we do daily and see how it can be mapped to SQL Server. This in a way many say is an obsession for the subject I love – SQL Server. But I think it is part of how we laterally think. In the recent past, I have been following a number of sports and this quest is fundamental to make sure when my little girl asks me, I am ready with the answers. Till date it has been a fun ride and I have nothing to complain. Recently, I was watching a live World Chess Championship Tournament and it was intriguing. There was silence all around and I was intensely watching the match. My little girl was amused with this silence and let me be myself for close to 15 mins. She then tried to join me where in the match was about to end. She was watching me and TV with curiosity to why I was concentrating so much. The match actually ended in a draw and people were clapping hands and the players walked away. Seeing this, my daughter asked me to why there was no shouting or cheering from the audience (sorry for the football/cricket fever that was running for a while).

Then she asked an interesting question, why did the players walk away? They didn’t talk, fight or do anything but they just walked away, why? I told it was a stalemate game and none of the players won. So they decided to end it this way gracefully. That night I was thinking to how such simple things can get the curiosity of our children. It was a dead game and in SQL Server terms I would say a deadlock.

Back to Reality

This incident was in my mind for a while and I was planning to share it when the appropriate time comes. Unrelated, I got a simple enquiry from one of my blog readers. Can a table deadlock itself? Is that possible? Moreover, we are just doing inserts into this table as it is getting deadlocked, how is it possible? They were interestingly getting something like this in their environment and wanted to know how that is possible.

A web search can give you deadlock information generally where there are two objects that are in cyclic deadlock with each other, rarely do we get to see a single table deadlock. So I thought to write this blog to show how we can still get deadlocks using a single table. The sequence for this script would look like:

Session 1 Session 2
1 Create our table
2 BeginTransactionInsert row1
3 BeginTransactionInsert Row2
4 Insert row2Will get blocked
5 Insert Row 1Will get blocked and become deadlock
6 Deadlock encountered, one of the session terminates

Now that we got the flow ready, let us put the same in code. Below is the script for your reference.

Script for Scenario

-- Session 1: Step 1. Create our table
USE tempdb
GO
CREATE TABLE deadlocks
(ID INT, Name CHAR(20), Company CHAR(50));
GO
CREATE UNIQUE CLUSTERED INDEX deadlock_ind ON deadlocks (ID, Name)
GO
-- ----------------------
-- Session 1: Step 2. Insert row 1
BEGIN TRAN
INSERT INTO
deadlocks
VALUES (1, 'Pinal', 'SQLAuth')
-- ----------------------
-- Session 2: Step 3. Insert Row 2
BEGIN TRAN
INSERT INTO
deadlocks
VALUES (10, 'SQLAuth', 'Blog')
-- ----------------------
-- Session 1: Step 4. Insert row 2
INSERT INTO deadlocks
VALUES (10, 'SQLAuth', 'Blog')
-- ----------------------
-- Session 2: Step 5. Insert Row 1
INSERT INTO deadlocks
VALUES (1, 'Pinal', 'SQLAuth')

Please take a note of the session above. We will alternate the way in which the insert statements are built. Once these steps are followed, the famous deadlock error message of 1205 is shown:

Msg 1205, Level 13, State 47, Line 7

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In the above example, we are just using insert statements to generate a deadlock situation. How many of you have ever encountered such deadlocks in your environments? Do let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #005 – List All the Tables with Name Containing Specific String

The other day, I received an email from a blog reader. When he appeared for an interview, he was asked to retrieve all the tables where the name of the table contained the words ‘tbls_’. This was a very interesting scenario. As the organization had earlier adopted a specific naming convention where they were writing the string ‘tbls_’ before any table name. However, as the new manager joined the organization, he decided to remove this static prefix and wanted the list of the all the tables where the name of the table contained ‘tbls_’ anywhere in the table. I think this is a very common question and we often see this particular requirement in the industry.

Question: How do you list all the tables in the database where the name of the table contains specific strings like ‘tbls_’ anywhere in the name of the table.

Answer: Here is a simple script which can list all the tables with specific string in the name of the table.

DECLARE @SearchString VARCHAR(256)
SET @SearchString = 'tbls_%' -- Change SearchString
SELECT name FROM sysobjects
WHERE TYPE = 'U'
AND crdate <= DATEADD(m,-1,GETDATE())
AND
name LIKE '%'+@SearchString+'%'

Remember this script is using LIKE keyword with a % sign before and after searching string, hence it may be a bit slower in performance, but will for sure return accurate results. You can change the like condition based on your business need.

Here is the similar script which I had written a few years ago with the help of the cursor SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created.

Reference: Pinal Dave (http://blog.sqlauthority.com)