SQL SERVER – Identifying T-SQL Missing Parameter Data Types with SQL Server

I like challenges and when we are faced with a challenge – I just cannot sleep. I am in constant search for solution. More importantly I look forward for the learning experience these bring to me. Recently a senior developer was talking to me and got me thinking.

Senior Developer: Hey Pinal.

Pinal: Yes, tell me.

Senior Developer: I wanted to ask you something from SQL Server point of view.

Pinal: Sure, go ahead. Let me check if I know it.

Senior Developer: I have seen your session where you said, inappropriate data types while comparing can get into trouble in terms of performance.

Pinal: Yes, you want to see a demo?

Senior Developer: No, I understand your point of view. Having said that, I have a number of junior developers joined my team.

Pinal: Ok, now is that a problem?

Senior Developer: hahaha, no Pinal. That is not the problem. Since our code has a number of Dynamic SQL’s as part of our code. We have made sure we use parameterization techniques of sp_executesql.

Pinal: That is nice.

Senior Developer: Here is the problem, we still see our developers use these sp_executesql but they pass the parameters with wrong data types unknowingly. And I see sub-optimal plans being generated and hampering our performance from time to time during code review process.

Pinal: Ok, what is the help you need from me?

Senior Developer: Is there a way for my developers to validate if they are using the right data type when they are executing their Dynamic SQL inside SQL Server?

Pinal: Oh sure, if you are on SQL Server 2012 and above – there is a neat trick. But it involves your developers do an extra step of validating their parameter data types. Is that ok?

Senior Developer: Oh sure. Please tell me – I am all ears and will add it to our code review process and unit testing by developers.

Pinal: Don’t worry, I am going to write it as a blog post.

Knowing sp_describe_undeclared_parameters

This stored procedure returns a result set that contains metadata about undeclared parameters in a TSQL batch. It considers each parameter that is used in the @tsql batch, but was not declared in @params. It returns one row for each such parameter, with the deduced type information for that parameter. The procedure returns an empty result set if the @tsql input batch has no parameters except those declared in @params.

The template query is:

sp_describe_undeclared_parameters @tsql, @params

MSDN Documentation: sp_describe_undeclared_parameters (Transact-SQL)

Let me start by showing a Dynamic SQL where we are not sure of the parameters and what datatypes are for the statement finally. If we execute the statement as-is this will give us an error.

sp_executesql N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'

The error we will get is:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@id".

Now if the developer wants to know what the datatypes of each of the parameters, then he can use the sp_describe_undeclared_parameters stored procedure. To start with, don’t pass the @params list.

EXECUTE sp_describe_undeclared_parameters @tsql =
N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'

This will output 3 rows. One for each of the parameter defined. It deduces the type information. And this is super cool if you have never used.

Now when I showed this to the Senior Developer, he was pleasantly surprised that SQL Server can do a great job is helping us get the code right.

If you add the @params with possible parameters, those are not displayed in the output. I would generally send it without the parameter list to know the complete sequence. This is a great way to learn from SQL Server.

Do let me know if you found this useful and if you are planning to use the same in your environments.

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

SQL SERVER – Proof of Concepts with Windows Azure – Notes from the Field #056

[Note from Pinal]: This is a 56th episode of Notes from the Fields series. If you are DBA and Developer there is always a situation when you want to prove yourself by building a small proof of concepts of your idea. However, most of the time, it is way more complicated than we think. Building proof of the concepts required many different resources and skills. Above all there are chances that what we have built is not upto the mark and we have to rebuild the example one more time. Trust me it is never simple those tasks which looks simple from the beginning.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how to build proof of concepts and how to maximize the power of Azure. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

Whether you know it or not cloud services are here and they are changing the way we will provide information technology services. For example, in many information technology shops it can take weeks if not months to get an instance of SQL Server up and running.  Here are some minimal action items that must be completed before DBA’s get access server to install SQL Server.  You have to order a physical server, your procurement team must approve the order, and the server has to be shipped. Once the server is received the server must be racked in the data center, cables must be connected, and the data center team needs to document their changes. Then the operations team needs to install and configure windows. I could keep going but there are a lot of things that should be done to a server before the DBA team gets its hands on it. What are you going to do if you’re a DBA and you need instance up in 30 minutes for a proof of concept? It’s becoming more common that the cloud is the answer.

Every time I need a server for a proof of concept I jump to Windows Azure.  I can quickly build a Windows Azure Machine with SQL Server provided within 30 minutes.  In this tip, I am going to walk through the steps to create your first Windows Azure Machine.

1. Get Azure Windows Account. If you don’t have one you can get a free trial. At the time of writing this tip your free trial would include $220 of credit.

Are you an MSDN Subscriber who isn’t leveraging Microsoft’s cloud service? If so, you can sign up for free monthly credit.

2. Log In to Azure Portal

3. Create New Virtual Machine

On the left hand side, click on Virtual Machines and then the add button on the bottom of the left side of the screen. This will load our wizard for creating our first virtual machine.

Now that wizard is loaded as you can see below we can select virtual machine and create it from the gallery. In the Gallery we will be able to select one of many images used that already includes SQL Server baked in.

Looking at the SQL Server images you will see you can access Enterprise Edition, Standard Edition and Web Edition for SQL 2014 down to SQL 2008 R2.


Next you can customize your image by release date. This will allow you to have different service packs or CUs. You can also select between two different tiers and sizes.  You will have to create a user name and password and you will want to keep this credential as it will be your first account.


Next you will be able to select more machine configuration options. You will get to determine where the Azure Virtual Machine is located.  Below you will see I am using my MSDN Subscription.


Finally, you will get to configure more configuration extensions to help automate or secure your virtual machine.

Finally, you will see your server being provisioned. What once use to take weeks or months can now be done in the cloud in minutes.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

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

SQL SERVER – Inside Temp Table Object Creation

When I wrote the article around tempdb like normal database, I had a number of people asked me how it is so simple to understand? Well, they were asking how are the tables created? How will same tables created by different sessions created inside tempdb? Are there anything different about temp tables when compared to normal tables?

These are all valid questions and sometimes we need to check the output to understand them better. So here is a simple test we are going to do to understand how temp tables are created inside SQL Server. For my example I will create a temp table of the following definition.

-- Session 1: Table creation
CREATE TABLE #temptable_test (

After the table is created, we will go ahead and check the object definition created inside tempdb database. We can look at the metadata using:

SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

The important point to note is the negative object id for the table created. Also we need to see that the table created has a long number of underscores and a number (___02). Now why is SQL Server doing this? Why couldn’t SQL Server create the table using the same name we created? The answer is simple. We have a single tempdb for the whole instance and if there are multiple sessions creating the same object, then we don’t want to have conflict in the table created.

To mimic this, let us go ahead and create a second table with the same name but different schema to check what SQL Server does. Our second table definition looks like:

-- Session 2: Table creation
CREATE TABLE #temptable_test (
FullName CHAR(100) NOT NULL

Though the names are same but the column definitions are different, what we see inside SQL Server tempdb metadata is:

As you can see, the names have different auto number extension making each of these objects unique. This is the reason why SQL Server adds the extra extension. For simplicity sake and as explained in the precious article, the temp table is removed as soon as the session is closed / completed. So let me go ahead to check the same. We will close the session 1 and see the effect.

As you can see, temp tables are similar to normal tables with the exception that the table is disposed as soon as the session is closed.

Do let me know if you have seen this behavior in your environments and how many such objects are available at any point in time in your environments today? Let me know and it will be a great learning about your very own environment.

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

SQL SERVER – Maintenance Plan – Maintenance Cleanup Task not Deleting Files

I am always notified by my blog readers whenever they see something unusual and I try to help them so that I can learn something new. Recently, one of my blog reader told that his drive is getting filled with the regular backups taken via SQL Server Maintenance Plans.

My simple answer was – You should delete or move older backups and Maintenance Cleanup Task is available in the Toolbox. And I was shocked when he said that he already has that task, but it is not working as expected. It is supposed to delete files with extension “bak” which are 7 days old, but its not deleting any of those files. My next question was – is there any error message? The answer was – “When I run the job, it gives me a success message, but older .bak files are still present.”

I asked him to look into the log files generated by maintenance plan. They are generated under LOG folder (which contains SQL ERRORLOGs as well). Here is what was shared.

Microsoft(R) Server Maintenance Utility (Unicode) Version 12.0.2381
Report was generated on “local”.
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Maintenance Cleanup Task (local)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2014-10-28T10:14:59.
Task end: 2014-10-28T10:14:59.
Command:EXECUTE master.dbo.xp_delete_file 0,N”E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup”,N”.bak”,N”2014-09-30T10:14:59”

When I configured the plan on my machine and looked at the log, I got similar output. There is a little difference there. Notice the extension of the file given as .bak (notice a dot before bak). I have highlighted in the output.

Then I asked my friend to share the screenshot of the cleanup task and here is what he shared.


And that was the problem. I asked him to keep bak as extension (by removing dot before it) and run the below command.

EXECUTE MASTER.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup',N'bak',N'2014-09-30T10:14:59'

The command is same as what was shown in the log file. I have just replaced two single quotes with one single quote. As soon as that was run, all BAK files older than 2014-09-30T10:14:59 got deleted successfully.

Moral of the story: Doesn’t use dot before the extension of the file in the UI.

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

SQL SERVER – FIX – The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run

The best way one can learn SQL Server is by trying out things on their own and I am no different. I constantly am trying to explore the various options one can use when working with SQL Server. In the same context, when I was playing around with backup restore commands, I made a mistake and unfortunately restarted SQL Server. After that I was unable to start SQL Service.  If I start the service, it doesn’t give any error but gets stop automatically.

Whenever I have any weird problems with SQL, I always look at ERRORLOG files for that instance. If you don’t know the location of Errorlog, you should refer Balmukund’s blog (Help : Where is SQL Server ErrorLog?)

This is what I found in ERROLROG just before the stop.

2014-10-28 002039.02 spid9s      Starting up database 'model'.
2014-10-28 002040.01 spid9s      The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2014-10-28 002040.04 spid9s      Error 927, Severity 14, State 2.
2014-10-28 002040.04 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

The error and behavior which I am seeing makes sense because to start SQL Server, we need master, model and tempdb database. You might think that MSDB is also a system database and would be needed for SQL Engine? Well, you might have been tricked. MSDB is needed for SQL Server Agent Service, not SQL Server Service. So, my master is fine, model has some problem. Every new database is created using model, including TempDB so SQL Service is refusing to start. Since the model database is not recovered successfully, SQL Server cannot create the tempdb database, and the instance of SQL Server does not start understandably.

So I called up Balmukund – these are the perks of having a good friend to rely. He never says “no” but he also doesn’t give complete solution to the problem. He gives hint and asks me to research further. This time also magical words were – “use trace flag 3608 and restore model with recovery”.

I followed his advice and performed below steps.

1. Start SQL Server with trace flag 3608 using net start command

Net Start MSSQL$SQL2014 /T3608

In my case SQL2014 is the name of the instance. If you have default instance then service name would be MSSQLServer. For named instance, it is MSSQL$InstanceNameHere

2. After starting with trace flag 3608, I verified the same from Errorlog as well.

Further, I also found below message in ERRORLOG.

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

3. Connected to SQL Instance using SQLCMD by below command.

SQLCMD -S .\SQL2014 -E

You can read parameter of SQLCMD at Books online here

“1>” means we are connected to SQL Instance and then Executed below command (hit enter at end of each line)


4. Once the command is executed successfully, we will come back to “1>” again. We can type exit to come out of SQLCMD

5. Now stop SQL Service

Net Stop MSSQL$SQL2014

6. And start again without trace flag.

Net Start MSSQL$SQL2014

Now my SQL instance came up happily and I was unblocked.  After sometime I got call from Balmukund asking if SQL is started and I told that my next blog is ready on the same topic.  He finally asked, how did that happen? And my answer was – I ran wrong command. My command was

BACKUP DATABASE model TO DISK = 'Full.bak'

My request to my reader is that please DONOT run the above command in your SQL instance and restart SQL else you need to follow the steps in production server. Learning never stops when working with SQL Server.

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

SQL SERVER – SSMS Trick – Generating CSV file using Management Studio

In my previous blog I talked about using SQLCMD to generate a comma separate files for the table data.

Some blog readers reported that why we should use SQLCMD or BCP to generate the file when the same can be achieved using SQL Server Management Studio itself. Well, that was a strong statement and I liked the simplicity of the solution. Here are the steps. A lot of times it is our inability to learn some of the simple techniques present in the tools that we use make us even more productive.

Here are the steps to achieve the same. Go to SQL Server Management Studio > Choose Tools from Menu Bar > Click on Options. Go to “Query Results” > “SQL Server” > Click on “Results to Text”

Once we choose “Comma delimited” and hit OK – we will get below kind of output when we eecure a query (Note that we need to open a new Query Window so that the settings are saved)


Notice that first column is the column name. If we want to avoid that and wants ONLY the data, then we can customized that in the same place where we have modified the separator setting. If we uncheck “Include column headers in the result set” then we will get only the rows as output.

We can also choose custom delimiter. I have set delimiter as pipe (|) and unchecked the box which I mentioned earlier and below is the output for the same query.

As we can see that output is pipe separated and we just have data not column heading. This can be saved and imported again to some other server using bcp command, Bulk Insert command or Import/Export Wizard.

Recommended reading:

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

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

It’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)