SQL Azure

SQL Azure is a cloud-based service from Microsoft offering data storage capabilities.

Useful links:

SQL Azure - microsoft site:

http://www.microsoft.com/windowsazure/sqlazure/

SQL Azure Labs:

http://www.sqlazurelabs.com/

Unsupported Transact-SQL Statements in SQL Azure:

http://msdn.microsoft.com/en-us/library/ee336253.aspx

Connect to SQL Azure through a special project for developers without SSMS, Houston project:

https://manage-sgp.cloudapp.net

INSTEAD OF DELETE trigger on view - ERROR

Error message:

DELETE is not allowed because the statement updates view "VVV" which participates in a join and has an INSTEAD OF DELETE trigger.

Explanation:

A view with an INSTEAD OF UPDATE / INSTEAD OF DELETE trigger cannot be a target of an UPDATE or DELETE statements with a FROM clause.

Work-Arounds:

1. Replace the JOIN with 'IN':

delete dbo.test1 

from dbo.test1 t1 join dbo.test2 t2 on t1.Id = t2.Id

-->

delete dbo.test1 where t1.Id IN (select Id from dbo.test2)

2. Replace the JOIN with EXISTS:

delete dbo.test1

from dbo.test1 t1 join dbo.test2 t2 on t1.Id = t2.Id

-->

delete dbo.test1

from dbo.test1 t1

where exists ( Select top 1 Id from dbo.test2 t2 Where t1.Id = t2.Id )

3. If possible - do the update/deletion on the original tables (= the source table/tables of the View).

SSRS - Expression-based Connection Strings

="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks

Specify connection strings parameters as a report parameter.

Expression-based connection strings are evaluated at run time.

http://msdn.microsoft.com/en-us/library/ms156450.aspx#Expressions

SQL Server code name Denali Home page

SQL Server code name Denali Home page:

http://www.sqlserverlaunch.com/Home.aspx?Day=120

What's New in SQL Server Denali:
http://msdn.microsoft.com/en-us/library/bb500435%28SQL.110%29.aspx

Whitepaper of the new features and more can be downloaded from Microsoft:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Spatial_Denali_CTP1.docx

Return value of stored procedure

DECLARE @return_value int

EXEC   @return_value = [dbo].[SP_NAME]  @ID = 131279, ......

SELECT 'Return Value' = @return_value

• When the SP is successful, @return_value would be 0.
• Also @return_value=NULL means an error.

If we want to return an output value from the SP, we have to declare an output parameter in the SP declaration:

CREATE PROCEDURE [dbo].[SP_NAME]

@Param1 int,

....... ,

@SpCount INT OUTPUT

AS

.......

and insert it a value in the SP code.

When execute SP:

DECLARE @Count int

EXEC   @return_value = [dbo].[SP_NAME]  @ID = 131279, ......, 

              @SpCount = @Count OUTPUT

Pattern Matching in the LIKE operator

% - Any string of zero or more characters.

_ - Any single character.

[ ] - Any single character within the specified range ([a-f]) or set ([abcdef]).

[^] - Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

[[] - specific speicial character - in this case '['.

http://msdn.microsoft.com/en-us/library/aa933232(v=sql.80).aspx

Find and kill active connections to Database

select DB_NAME(dbid), ('kill ' + convert(nvarchar(10), spid) ) as kill_statement, * 

from sys.sysprocesses where DB_NAME(dbid) = 'DBNAME'
--or:

DECLARE @SQL nvarchar(max) 
SELECT @SQL=COALESCE(@SQL,'') +'Kill '+CAST(spid AS nvarchar(10))+ '; '  
FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'DBNAME'