"Create Unit Tests" option greyed out in Database project

In the Visual Studio in database project, when you open Solution and switch to
Schema View , go to a stored procedure and right click, you should see the
option "Create Unit Tests...".
The problem is that it can be greyed out (sometimes).



In my case it is greyed out when there was no successful build of the database
project, try to issue build, fix errors if you have and see again the option
"Create Unit Test" is greyed out.

Red Gate SQL Compare crashing after 20-30 minutes

Consider following situation:

I use Red Gate SQL Compare to compare database schema between
2 remote database servers. Usually it works fine and the process finishes
within couple of minutes.If you compare a DB where its objects encrypted,
for example, stored procedures, functions are encrypted, SQL Compare
has option to decrypt the encrypted objects and the comparing process
takes a little longer, but it works.

On some new installed Data Center the SQL Compare was getting the
following error after 20-30 minutes of comparing the local database and
the remote database on the new installed Data Center:

"A transport-level error has occurred when receiving results from the server.
(provider: TCP provider, error: 0 - An existing connection was forcibly closed
by the remote host.)"
 

 

 

 

Well, it took us some time to understand why on the new servers

we getting the "transport-level"/"time out" error above.

 

The bottom line: it happened due to "TCP Chimney" option that

was enabled in the Windows Server.

It means the following:

 

TCP Chimney Offload is a networking technology that helps

transfer the workload from the CPU to a network adapter

during network data transfer.

 

This should be a good thing, but in order to get it working correctly

and without any issues you need a NIC to support this feature

and proper drivers.Also it should be enabled in the OS and NIC

Level.

 

The error not happened in all rest data centers because this feature is

enabled/disabled by default in different Windows Servers.

For example, TCP Chimney is enabled by default in the

Windows Server 2003 SP2 and it disabled by default in the

Windows Server 2008.

 

To disable the TCP Chimney use the following:

netsh int tcp set global chimney=disabled

 

According to this article the TCP chimney can cause

also other problems related to SQL Server.

 

I hope this post will help you to save hours:)

OLTP vs BI

If you were asked to explain what is the difference between
OLTP system and BI system, how would you do it?

Here how I did it in one of my presentations:
Here is how OLTP system looks like:

The OLTP system is just flow of raw data (Transactions).

And here how BI system looks like:

PS: Only real DBA can see the red women in the first picture:)

If you did not get it, I recommend you to see the Matrix movie

CHECKSUM and OPTION(RECOMPILE)

Working with CHECKSUM

functions, I encountered a situation

when the result of the

CHECKSUM function is

effected by OPTION(RECOMPILE)

Please see following example:




DECLARE @a VARCHAR(5),@b VARCHAR(5)
SET @a='a'
SET @b='b'

SELECT CHECKSUM(@a,@b) as Result

SELECT CHECKSUM(@b,@a) as Result

 

SELECT CHECKSUM(@a,@b) as Result
OPTION(RECOMPILE)

As you can see the result of CHECKSUM(@a,@b) without recompile
and with recompile is different, when using option(recompile)
we see that SQL Server change the order of the variables.

Do you know why?
If yes, Please leave comments.

SQL Server 2012 - New features

I want to share the PPT that contains new SQL 2012 features,
the presentation agenda is :

• Always On (HADRON)
• Contained Databases
• T-SQL new features
• SSMS new features
• ColumnStore Index
• LocalDB - embedded database
• BI improvements

You can find the PPT file here:

Power Point Presentation file

@@Trancount in DML statement

I was debuging something related to transactions
and saw something strange from first sight.

You know that @@TRANCOUNT returns
the number of BEGIN TRANSACTION
statements that have occurred on the current
connection, right?

Well, check this situation:


CREATE TABLE dbo.TempTbl (Info VARCHAR(10))
GO

INSERT INTO dbo.TempTbl(Info)
SELECT @@TRANCOUNT

GO

SELECT * FROM dbo.TempTbl

 

Here is the resut set:

 
 
 
 
 
So why the value of the @@TRANCOUNT is 2?
I did not use BEGIN TRANSACTION.
I undestand that INSERT is implicit transaction and
therefore the @@TRANCOUNT can be 1, by why it equals to 2?


The reason is that the @@TRANCOUNT counts the user transaction
as well as the inner transaction.So in DML statement it will be always at least 2.

Here is more, when using a variable table the @@TRANCOUNT is 0:

This is because local variables(and tables) are not effected
by transaction(s).


For more info:
http://madebysql.blogspot.com/2010/04/local-variables-and-transaction-or-all.html
http://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx

How to debug/troubleshoot a SP in production environment?

There are times when you need to debug

some stored procedure(s) in Production

environment or to make a SP to behave

differently when you execute it via the SSMS

versus case when the SP executed by an application.

Or to make SP to behave differently from requests

that come from different version of an application

or requests that come from specific IPs and etc.

Or you may want to know what values were

provided for the input parameters of the SP?

Here some usefull SQL functions that you can use in the SPs to achieve the above:

1) APP_NAME() - Returns the application name for the current session if set by
   the application. You can set it in the connection string of the application by
   including this: "Application Name=AppName".
   By using this function you can change a SP to do X if it executed from Server A
   and to do Y if it executed by Server Y or if it executed via the SSMS.
   Simply set the application name for different value in Server X and Y.
   By using this function you can also upload SPs changes without any downtime
   and with full backward compatibility.

2) CONNECTIONPROPERTY('client_net_address') - Returns the Client IP address.

3) CONNECTIONPROPERTY('local_net_address')  - Returns the IP address of the server.
   For example, if you connect to production SQL server from the SSMS in QA/DEV
   SQL Server then it will return the IP of that QA/DEV SQL Server machine.

By using these functions listed above you can add logic (If or CASE statements)
to the SP and you can troubleshoot/debug the SP without running the SQL Profiler.

SELECT APP_NAME() AS Application_Name,
               CONNECTIONPROPERTY('local_net_address') AS local_net_address,
               CONNECTIONPROPERTY('client_net_address') AS client_net_address
GO