SQL-Server : Unlock tables locked by SQL

If your tables are locked in SQL SERVER using some SSIS or SSRS or some other tools.
For unlock we need to kill process id from sql server .

if your table is locked then SPID=-2 stored in syslockinfo table .

you can't delete directly this process id (SPID=-2)
use this query and kill whatever you got from this query result.

select req_transactionUOW
from master..syslockinfo
where req_spid = -2

KILL '010C2764-C765-7168-854X-5475A5D789S3'

Thx,
RS

SSRS : Limitations

Below are the some limitations with SSRS


1) Page number can't access in RDL Body.
2) Cannot merge table columns.
3) SSRS preview mode does not allow you to modify any formatting on the fly like Crystal Reports does.
4) You can use Limited Html tags and you cannot use JavaScript in SSRS unlike Cognos.
5) As SSRS doesn’t allow JavaScript code, we can’t rename the Label of parameters dynamically based on the selection of some other parameters.
6) SSRS does not currently support CSS.
7) Customization of chart colors according to the company’s brand color. If you click a color (country) say ‘blue’ in a first chart (say For ex. Sales across Countries) then the child chart (state) must display the data in different shades of blue.
8) SSRS Subreport not allowed in Page Header/Footer.
9) SSRS Subreport value can not access anywhere in RDL.
10) SSRS doesn’t provide Backward Compatibility.

SSRS : Spliting Values

If we want to split the value in SSRS then we can use the in built split function as follows

For example : 

if we want to split "AB,BC,CD" in to "AB" "BC" and "CD".

Split("AB,BC,CD",",").GetValue(0)
Split("AB,BC,CD",",").GetValue(1)
Split("AB,BC,CD",",").GetValue(2)

Thx,
RS

Swipe a column value in SQL-Server

Some time we need to update one column with another in the same table or we can say swipe a value. For this we can use simple update statement as follows

UPDATE temp t
SET t.testColimn1 = t.testColimn2, 
t.testColimn2 = t.testColimn1

Thx,
RS

SSRS : client machine date and time

Hi All,

In SSRS some times we need to show the client machine date and time in the Report File we can do this using below code

=System.TimeZone.CurrentTimeZone.ToLocalTime(Globals!ExecutionTime)

Thx,
TX

SQL SERVER – 2008 – Download and Install Samples Database AdventureWorks 2008 - Using Script

Hi All,

This post is for Adventure Works Sql-Server sample database. Many time we need a sample database to perform our query and operation. AdventureWorks is the Microsoft's open source database. There are different-2 method to download and install the AdventureWorks but here i'll mention the method which i feel easy.

Below is the link to download the script and data files.

Click here : AdventureWorks 2008 OLTP Script
                                        OR
                  AdventureWorks 2008R2 OLTP Script

Click on the above link and download the full files. Extract it some where in your PC. Open the folder there you will find "instawdb.sql" file open it. 

un comment "SqlSamplesDatabasePath" and "SqlSamplesSourceDataPath" variable and change the path. 

Now in menu select Query>SQLCMD Mode.

Execute the script.

Now this AdventureWorks sample database is intalled in your computer.

Thx,
RS