Suggestion for SQL Prompt and SQL Test (Red-Gate)

Recently I moved my job on ALM topics, focusing on Source Control Management and Testing, in order to reach Continuous Integration on database development. I evaluated some tools and Red-Gate was my first choice. In this post I’d like to share the suggestions I made on two of the most important tool I’m using atm:

  • SQL Prompt (productivity tool for code formatting, intellisense, code inspection, etc.)

suggestions: TODO highlights. I’d like to improve readability of TODO and HACK comments, like in the following picture:

 For ReSharper user (and Visual Studio ones), this is very familiar.
  • SQL Test (addon for SQL Server Management Studio, unit testing utility)


“Expand/Collapse all” for avoiding clicks when you have lots of test procedures

Logical folders for grouping tests logically and not with the strict database schema names
If you think that those suggestions could help us, feel free to vote!
Stay Tuned!

Avoid nested INSERT EXEC (where possible)

Some days ago, I received a tedious error while executing a pipeline of nested stored procedures, which contains INSERT-EXEC statements. The issue is based on a SQL Server limitation, by design since SQL Server 2005. Actually, the INSERT-EXEC was supported in SQL Server 2000, but I’ve never tried that behavior on the older version. Let’s see the behavior. Continue reading “Avoid nested INSERT EXEC (where possible)”

SQL Server Feature Limitations (Windows Azure SQL Database)

I’ve just received a question about features comparison between SQL Server and SQL Azure Database. This is one of the most frequently asked questions I receive in my blog PMs and forums.. Thus, I’m sharing the links in order to gather some information about this topic.

Another good start can be this link.
Stay Tuned! 🙂

ALTER SCHEMA does not reflect changes into the sys.sql_modules catalog view (definition)

I have recently found a strange behavior related to a comfortable DDL command, the ALTER SCHEMA statement. That happened while I was trying to move a stored procedure from a schema to another one. Both stored procedure and function definitions are stored on a catalog object which is accessible by the sys.sql_modules catalog view (column “definition”). After executing the ALTER SCHEMA statement the definition field is not updated.

We’re speaking about both SQL Server 2012 on premise and SQL Azure databases. My current SQL Server 2012 version/edition is:

Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) 
Dec 28 2012 20:23:12 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Continue reading “ALTER SCHEMA does not reflect changes into the sys.sql_modules catalog view (definition)”

Backup Windows Azure SQL Database with Sql Server Management Studio

Recently I had to create a backup procedure on my Windows Azure SQL Database.  SQL Server Management Studio allows us to access to our SQL Databases and provides the user interfaces for creating backup files.

In this post we will understand:

  •  how to create a Azure SQL Database copy
  •  how to save a logical file backup on the cloud storage
  •  how to restore the logical file on a new Azure SQL Database

Since the database copy can be considered as a full backup, we want to create also a file in order to place it on another place, that could be a local or a cloud storage.

Continue reading “Backup Windows Azure SQL Database with Sql Server Management Studio”