Understanding Lockings in SQL Server

 

Locking optimizer hints

SQL Server 7.0/2000 supports the following Locking optimizer hints:

NOLOCK

HOLDLOCK

UPDLOCK

TABLOCK

PAGLOCK

TABLOCKX

READCOMMITTED

READUNCOMMITTED

REPEATABLEREAD

SERIALIZABLE

READPAST

ROWLOCK

NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

PAGLOCK is used by default. Directs SQL Server to use shared page locks.

TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.

READCOMMITTED
Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

READUNCOMMITTED
Equivalent to NOLOCK.

REPEATABLEREAD
Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

SERIALIZABLE
Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

READPAST
Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

ROWLOCK
Use row-level locks rather than use the coarser-grained page- and table-level locks.

You can specify one of these locking options in a SELECT statement.
This is the example:

SELECT au_fname FROM pubs..authors (holdlock)

0

Working with Batch or Command Files

Using batch files as a deployment scripts
Batch files or Command [.cmd] files are still used for deployment purpose in many organizations.

Batch files are simple text files containing some lines with commands that get executed in sequence, one after the other. These files have the special extension BAT or CMD. Files of this type are recognized and executed through an interface (sometimes called a shell) provided by a system file called the command interpreter. 

These are using to trigger SQL Scripts, copy files etc ... This article explains how to create batch files for deployment purpose.

Creating a simple batch/cmd file:

Below file executes a SQL Script file from a specific path, this file accepts server and database as parameters:

@echo off
set /p sname= Please enter the servername:
set /p dbname= Please enter the databasename:
set /p pwd= Please enter the password:
sqlcmd -E -S %sname% -D %dbname% -P %PWD% -i "C:\location\your query.sql" -b)

With Error handling:

echo off
setlocal enabledelayedexpansion
set /p servername=Enter DB Servername :
set /p dbname=Enter Database Name :
set /p spath=Enter Script Path :
set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
set cmd='dir %spath%\*.sql /b/s'
FOR /f %%G IN (%cmd%) DO (
echo ******PROCESSING %%G FILE******
echo ******PROCESSING %%G FILE****** >> %logfilepath%
SQLCMD -S%servername% -E -d%dbname% -b -i%%G >> %logfilepath%
IF !ERRORLEVEL! NEQ 0 GOTO :OnError
)
GOTO :Success

:OnError
echo ERROR ERROR ERROR
echo One\more script(s) failed to execute, terminating bath.
echo Check output.log file for more details
EXIT /b

:Success
echo ALL the scripts deployed successfully!!
EXIT /b

0

TSQL Queries Search for SQL Objects

Frequently used SQL Queries

 
There are many SQL Queries which are used frequently as a part of our SDLC, I am trying to consolidate all and keep at one place. Hope this helps!


Search for a tablename in all SQL Objects:
There are different ways of searching for a text in all SQL Objects, below are few methods:

SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SEARCHSTRING%'

DECLARE @Search varchar(255)
SET @Search='SearchString'
SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%'+@Search+'%' ORDER BY 2,1


Another simple way of doing this,
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%SearchString%'


Get all Tables across all databases in a Server:

There are few instances where we want to list all the tables in all databases in a database server,

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
INSERT INTO @AllTables (CompleteTableName) EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id'
SET NOCOUNT OFF

--Get Result
SELECT * FROM @AllTables ORDER BY 1


 

1

Could not load file or assembly '' or one of its dependencies. An attempt was made to load the program with an incorrect format.

 
 

 

Could not load file or assembly '' or one of its dependencies. An attempt was made to load the program with an incorrect format.

If you have any VC++ CLI components in your azure web role and you are facing the above error, then this post might be helpful for you to resolve your issue.

 

One of the most common problems reported regarding Windows Azure Web Sites is “Assembly Loading” issue.

Two main things you need to verify,

• This error might be causing because Azure runtime is unable to find the dll/assembly that it needs to run the web role.
• Secondly, this error might occur if you are trying to run any of the 32-bit components in windows azure.

 When you create a web role and try to host on Windows Azure you may seen this error because the Azure runtime was unable to load the necessary assemblies to make your web role run successfully.

 

Now, the question is how to identify which assembly is not loading as expected:

First check:
There is a program called Dependency Walker which allows you to see the dependencies of a given PE file (dll, exe, ocx...).
This error is really annoying, and very difficult to debug. You have to make sure that your dll is present as well as ANY dependency this dll has.If you see that any dll/assembly is missing ensure you package that dll into your azure package.

Second Check:
Make sure you run your web role and all the other project (mainly VC++ CLI Components) inside the webrole in "Release" Mode. The reason, few VC++ debug dll cannot be available in Azure Emulator and might cause this issue. :) (This was my scenario, I struggled one week to identify this).
 

 Third Check:

Few additional settings you need to verify, Check whether you AppPool is enabled for 32-bit applications.

 

Go to Respective ==> Site Application Pool ,

Click on ==> Advance Settings
Change value of ==> "Enable 32-Bit Applications" to True.

 Fourth Check:
 

The app would run fine on the developer machines and select others machines but not on recently installed machines. In this case, you need to verify whether the respective Visual C++  runtime installed or not. Adding the respective Visual C++ Runtime redistributable to the app installer will fix the issue.

 
And finally,
Ensure you have packages all the respective dll into your Azure packaging, to do that
1. If you want an your file to be copied, first you can add it to the project.
2. After adding right click on the file --> properties
3. Set the Build Action from "None" to "Content"
4. or The other option you have is to keep the Build Action as "None" but set "Copy to Output Directory" to "Copy if Newer" (or "Copy Always"). 

 This will include your file in Azure packaging.

Hope this resolves your issue, Happy coding..

 

1

You receive an “Explicit value must be specified for identity column” error message when you use the replication feature to run an INSERT statement on a table in SQL Server

If you are facing this error "You receive an “Explicit value must be specified for identity column” error message when you use the replication feature to run an INSERT statement on a table in SQL Server" when you are running transaction replications, here is the reason behind this.

You are trying to insert a row in the secondary server where the table property "NOT FOR REPLICATION" option is enabled.
To work around this issue, disable the NOT FOR REPLICATION option for the IDENTITY column.
Here is how we can check the value:

The "Not For Replication" setting for identity columns allows replication to pass the ID value from the publisher to the subscriber without the subscriber creating a new ID.  Since identity columns will always generate a new number when records are inserted, this setting allows you to ensure the values on both your publisher and subscribers stay in synch.
This option can be set when designing or creating a new table as shown below in the highlighted section.
Right click on the table in the secondary server, click on Design in context menu,
check in the properties whether the "NOT FOR REPLICATION" property is set to "yes".

 

If this value is set to "yes" change it to "no" using the below script

declare @objid int
select @objid = object_id('Adventureworks')
exec sys.sp_identitycolumnforreplication @objid, 0

The above query will set the "NOT FOR REPLICATION" property to "No". Once you set the value you need to perform the reinitialize the replication to get all the old values.

Ref. MSDN Article: http://support2.microsoft.com/kb/908711
http://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/

0

Identify Missing Sequence Numbers SQL Server

There are many scenarios where we may in need to identify the missing numbers in the provided range.

Here is the quick query which help in identifying using CTE [Common Table Expression]:

DECLARE @Test TABLE
(
Num INT
)

INSERT INTO @Test VALUES (1)
INSERT INTO @Test VALUES (2)
INSERT INTO @Test VALUES (4)
INSERT INTO @Test VALUES (5)
INSERT INTO @Test VALUES (8)
INSERT INTO @Test VALUES (9)
INSERT INTO @Test VALUES (10)

--Get the Missing Numbers from the sequence
;WITH Missing (minid, maxid)
AS
(
SELECT 0 AS minid, 10
UNION ALL
SELECT minid + 1, maxid FROM Missing
WHERE minid < maxid
)
SELECT minid
FROM Missing
LEFT OUTER JOIN @Test tt on tt.Num = Missing.minid
WHERE tt.Num is NULL
OPTION (MAXRECURSION 0);

Result:
minid
0
3
6
7

0

Content within this application coming from the website listed is being blocked by internet explorer

This error seems like problem with Internet Explorer, but you will be getting this error due to the setting in the Outlook in most of the cases.

To over come this error, you need to change the People Pane setting in the outlook.
Follow the below steps:
1. Open outlook.
2. Goto to the View tab
3. Click on the People Pane and make it "Off".

This works for me. :)

0