Showing All Current T-SQL Executions in the database

We would like to see if there exists a query running/suspended in the database without using SQL Activity Monitor. The query also works in SQL Azure.

In order to do that, we used the following query:

SELECT r.total_elapsed_time,text, task_state
FROM
   sys.dm_exec_sessions s
   LEFT  JOIN sys.dm_exec_connections c
        ON  s.session_id = c.session_id
   LEFT JOIN sys.dm_db_task_space_usage tsu
        ON  tsu.session_id = s.session_id
   LEFT JOIN sys.dm_os_tasks t
        ON  t.session_id = tsu.session_id
        AND t.request_id = tsu.request_id
   LEFT JOIN sys.dm_exec_requests r
        ON  r.session_id = tsu.session_id
        AND r.request_id = tsu.request_id
   OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
   where r.total_elapsed_time >1

I only select relevant columns for me and an elapsed time filter.

Ref:
http://stackoverflow.com/questions/7503456/nhibernate-to-linq-3-2-generate-error-sql-on-operator

Accessing Microsoft Azure Virtual Machines

After virtual machine is created, there are various ways to access it.

A)You can click created virtual machine using portal and click connect for downloading rdp file.

B)You can connect to MSSQL in virtual machine with SQL Management Studio:
Make sure you installed MSSQL with known username and password

1)Using portal, add the endpoint to your virtual machine:
Public Port:1433
Private:1433
TCP

2)Add inbound rule via Control Panel>Windows Firewall>Inbound Rules:
Allow connection from port 1433

C)You can directly call published virtual applications or SSRS Web services:

1)Using portal, Add the endpoint to your virtual machine:
Public and Private Port:80
TCP

Creating Login Users and Bind With Database User in MSSSQL

1)Add Login user to master db:
(To perform this operation, you need a valid login account information that can access master db)


CREATE LOGIN [AdminLogin]
WITH PASSWORD='XYZ'


2)Bind login user with db user:
(a default schema can also be used in the query)

CREATE USER AdminUser

FOR LOGIN AdminLogin

3)Giving insert,update permission to binded user:
(db_owner can be the best option for this)


EXEC sp_addrolemember N'db_owner', N'AdminUser'

NOTE:One login user can be used for many database users but for one database multiple users cannot be bind to same login user.

Reseed Identity Column in SQL Server 2008 R2

I want to share some important points while reseeding in SQL Server 2008 R2
In order to reseed a table, the following query can be used:

DBCC CHECKIDENT (tableName, reseed, 0)

But there is a minor issue that we must to consider.If we TRUNCATE table instead of DELETE, the above query will show the following message:

Checking identity information: current identity value 'NULL', current column value '0'.

This means you cannot use reseeding for newly truncated table. In such a case,if you want to set starting value:
1)Open table in desing mode
2)Select identity column
3)Expand Identity Spesification
4)Set Identity Seed

OR

You can just insert a dummy record and DELETE it.After that you can run script above.

It is important to remember that, the value that you set while reseeding(whether using query or designer) is not the inital value. Identity increment will be added to this value when insterting your first record.

(initial value + identity increment)

How to deploy CLR Stored Procedure with external access

CLR Stored Procedures can be deployed within the Visual Studio environment. However CLR-SP has no access to external sources by default. You must give exclusive permission to your CLR-SP in your database. I did not manage to deploy SP in the Visual Studio.Therefore i choose to deploy my sp within the SQL Server after building it with Visual Studio.The following steps explain how to configure SQL Server for external access:

1)Enable CLR:

SP CONFIGURE 'clr_enabled', 1

GO

RECONFIGURE

GO

OR

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

2)Set database trushworthy:

ALTER DATABASE master SET trustworthy ON

3)Authorization:

If database is authorized by another user you should run this statement. Dont write your db name or user name with ' '

ALTER AUTHORIZATION ON DATABASE: 'db name' TO 'user name'

OR

ALTER AUTHORIZATION ON DATABASE: 'db name' TO 'DOMAIN/user name'

In some cases you may wonder who is the owner of a database. You can use the following function to learn SID and name:

SELECT SD.[SID]

,SL.Name as [LoginName]

FROM master..sysdatabases SD inner join master..syslogins SL

on SD.SID = SL.SID

Where SD.Name = 'db name'

4)Create Assembly from dll file produced in .NET:

CREATE ASSEMBLY 'file name'

from 'dll path'

WITH PERMISSION_SET = EXTERNAL_ACCESS

5)Create Stored Procedure using the assembly file above.

CREATE PROCEDURE 'procedure name'

@var1 NVARCHAR(128)

AS

EXTERNAL NAME 'assembly name'.'project namespace'.'function name'

Web services may require more than one assembly reference. For instance XML.Serialization is one of the reference that is automatically added to the project when a web service is used. Therefore you may need to consider additional assemblies. Since i do not want to deal with such references, i used httprequest object to consume web service.

Sources:

http://kevine323.blogspot.com/2011/03/database-owner-sid-recorded-in-master.html

http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/