the windows 7 helpdesk shortcut

%windir%\system32\msra.exe /offerra 

Xencenter Export

I had to move a vm from a "old" XenPool to a brand new Xen 5.6 Pool.

but unfortunately it was not as simple as it looked. everytime when i did an export i received an error after about 8 minutes. Being desperate after a lot of exports and errors i removed the xencenter and started a reinstall of XenCenter 5.6 ..... 5.6 ????  hmmmm... using XenCenter 5.6 for an export from a 5.5 environment can be tricky.


So i reinstalled xencenter 5.5 and my problems with exports was over

update Foreign keys in all tables with constraints

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Elcomeno
-- Create date: 23/11/2010
-- Description: Searches for all constraints to the Referenced Column
-- generates update statements to replaces old FK with new FK
-- =============================================


CREATE PROCEDURE [dbo].[Z_Replace_OldFK_By_NewFK]


-- Add the parameters for the stored procedure here


@ReferencedColumnName Nvarchar(100),
@ReferencedTableName NVArchar(100),
@NewValue NVARCHAR(10),
@OldValue NVARCHAR(10)


AS
BEGIN
DECLARE @col1 NVARCHAR(100),@col2 NVARCHAR(100), @getRow CURSOR
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @getRow = CURSOR FOR
SELECT
-- rc.CONSTRAINT_NAME,
rcu.TABLE_NAME 'Ref_Table',
rcu.COLUMN_NAME 'Ref_Column'
-- rcu1.TABLE_NAME 'Referenced_Table',
-- rcu1.COLUMN_NAME 'Referenced_Column'
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu

ON rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG

AND rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME

INNER JOIN

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1

ON rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG

AND rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME

WHERE rcu1.COLUMN_NAME=@ReferencedColumnName AND rcu1.TABLE_NAME=@ReferencedTableName

ORDER BY rcu1.TABLE_NAME

OPEN @getRow

FETCH NEXT
FROM @getRow INTO @col1,@col2
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'UPDATE SET ' + @col2 + '=' + @NewValue + ' FROM ' + @col1 + ' WHERE ' + @col2 + '=' + @OldValue
--PRINT 'SELECT * FROM ' + @col1 + ' WHERE ' + @col2 + '=' + @OldValue
FETCH NEXT
FROM @getRow INTO @col1,@col2
END
CLOSE @getRow
DEALLOCATE @getRow
END

select all tables from a database

user generated tables
Select * from sysobjects where xtype='u'


system generated tables

Select * from sysobjects where xtype='s'

Generate Sql queries on the fly

BEGIN
DECLARE @TableName as nvarchar(50),@test as nvarchar(max)
SET @TableName = 'orders'
Print @TableName
SET @test = 'SELECT * FROM ' + @TableName
EXEC(@test)
END

SQL send an email from database mail

use Test
execute msdb.dbo.sp_send_dbmail
@profile_name='TEST',
@recipients = 'anemailadres@domainname.com',
@body = 'body of the mail',
@query = 'select * from orders',
@execute_query_database='',
@subject = 'subject of the mail',
@attach_query_result_as_file = 1

SQL With TIES Example

SELECT *
FROM QryCustomersWithMostOrders

Customer   OrderCount
A               3
B               3
C               1

SELECT  TOP 1  *
FROM QryCustomersWithMostOrders 
ORDER BY  OrderCount DESC

Customer   OrderCount

A               3

SELECT  TOP 1 WITH TIES *
FROM QryCustomersWithMostOrders 
ORDER BY  OrderCount DESC

Customer   OrderCount

A               3

B               3