Why @@SERVERNAME Returns NULL?

The @@SERVERNAME can return NULL, when you accidentally delete the server. Most probably by this query

sp_dropserver '<ServerName>'

After the SQL Server restart, the @@SERVERNAME started returning NULL. You can confirm this; if the following query returns no records, the local server was deleted.

Select * From sys.servers Where server_id=0

Solution 1

Run the following query to add the server back.

sp_addserver '<ServerName>',local

Solution 2

I would recommend you to use

Select ServerProperty('ServerName')

This approach returns the machine name, even if the sever name is not present in the sys.servers table.

The SQL Profiler Secures Username and Password

Whenever you run a login related query, the SQL Profiler converts the credentials as ‘----’ (hyphens). This feature improves SQL server security.

For example when you run this query

EXEC sp_addlogin 'Victoria', 'B1r12-36';

The profiler hides the username and password by substituting with hyphens

--*sp_addlogin---------------------------

Similar way, when you run sp_addlinkedsrvlogin, it will display as

--*sp_addlinkedsrvlogin---------------------------

Designing a Wrapper

Here I am sharing my experience with wrappers. This article is intended for beginners. Nowadays it is a fashion to create a wrapper for every third party library. The theme of this article is to think beyond the present situation. An unnecessarily created wrapper or a poorly designed wrapper will lead to messy code.

Wrappers created only for the two reasons.

1. Wrapper provides abstraction to a library and makes the developer life easy.
Example: When you have a third party library which has wide range of functionality, but your application needs only very few of them. You should create a wrapper
2. Wrapper enforces best coding practices of the product to use a third party library.
Example: When there are many options in the third party library but few of the options can only be compatible with your application; you should hide/abstract others.

Points to consider when designing a wrapper

1. Ask yourself, are you extending the functionality by a wrapper? If that is the case go for extension methods (C#). Unless until you have a solid reason, don’t create a wrapper.
2. Look around! You may get lot of wrappers from online. But, you need to validate them properly whether it fits your needs and has it well designed. I can see lot of wrappers in code project.
3. Never create a wrapper with reflection or dynamic calls. The degree of dynamism should be very minimal. In my experience introducing generic types are the maximum limit for designing a wrapper with dynamism. Because of dynamism, when there is a bug, the developer can’t even search it them in Google and can’t get help from any online forum. The wrapper should be very simple and neat.
4. A poorly designed wrapper restricts thinking ability; you should decide what level of abstraction is needed.

   Example: One of the NHibernate wrappers didn’t allow me to write QueryOver for sub queries. I couldn’t alter the wrapper immediately, because it has dynamic and reflection calls. To understand those dynamic calls, it would have been taken 2 more days. I had to find alternative way.

5. Think about production issues; never leave a wrapper half-way designed. The wrapper should cover all functionalities of the third party library. You should not design it for the present situation; if the third party library is too big, at least your wrapper should have some guidelines in future if some others want to extend the wrapper. When there is a production issue comes in, developers are forced to fix them as soon as possible. When they fix bugs, they generally don’t alter/extend the wrapper’s design. The code will become messy when the developer finds alternative way to fix the bug.

   Example: We faced a production issue, w3wp.exe was crashing. All our sites were down. The reason was, the wrapper for SqlConnection didn’t handle the SqlDataReader’s close operation. As open connections were not closed, the number of connections crossed the maximum limit and crashed the application. Instead of redesigning the wrapper, we first closed all the opened connections properly by typing “sqlConn.Close()” in 72 places of the application. Then we took time to refine the wrapper.

6. Writing wrappers often lead to performance problems. You should be careful on that.