Enable broker service on all databases but it check if Service Broker is enabled.

-- ***************************************** --
--       SQL Script to ENABLE_BROKER
-- ***************************************** --
--
PRINT @@servername;
PRINT Getdate();
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
 
--#region Select databases to Enable Broker Service

SELECT [name] INTO #dbs
FROM sys.databases
WHERE
    database_id > 4
    AND is_broker_enabled = 0  -- check if Service Broker is enabled.
    AND name NOT IN ('ReportServer','ReportServerTempDB')

--#endregion Select databases to Enable Broker Service

USE [MASTER];
SET NOCOUNT ON;
DECLARE @name nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN

    SELECT TOP 1 @name = name
    FROM #dbs;
    DELETE FROM #dbs
    WHERE
        @name = name;

    DECLARE @msg nvarchar(200) = '-- SET ENABLE_BROKER Database : ' + @name;
    RAISERROR (@msg, 10, 1) WITH NOWAIT;

    SET @ToKill = '';
    SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok   to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
    FROM master..sysprocesses
    WHERE dbid = DB_ID(@name);

    RAISERROR (@tokill, 10, 1) WITH NOWAIT;
    EXEC (@tokill);

    SET @sql = 'ALTER DATABASE [' + @name + '] SET ENABLE_BROKER WITH NO_WAIT;'

    RAISERROR (@sql, 10, 1) WITH NOWAIT;
    EXEC (@sql);

END

Lire la suite de Enable broker service on all databases

We have many ways to drop a database, but if you want to drop many databases.

You can have some problems with opened connections, but this script kill all connection and drop the database.

-- ***************************************** --
--       SQL Script to drop databases
-- ***************************************** --
--
PRINT @@servername;
PRINT Getdate();
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;

--#region Select databases to DROP

SELECT [name] INTO #dbs
FROM sys.databases
WHERE
    database_id > 4
    AND name NOT IN ('ReportServer','ReportServerTempDB')

--#endregion Select databases to DROP

--#region Drop databases

USE [MASTER];
SET NOCOUNT ON;
DECLARE @name nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

WHILE (EXISTS(SELECT *
    FROM #dbs))
BEGIN

    SELECT TOP 1 @name = name
    FROM #dbs;
    DELETE FROM #dbs
    WHERE
        @name = name;

    DECLARE @msg nvarchar(200) = '-- DROP Database : ' + @name;
    RAISERROR (@msg, 10, 1) WITH NOWAIT;

    SET @ToKill = '';
    SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok   to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
    FROM master..sysprocesses
    WHERE dbid = DB_ID(@name);

    RAISERROR (@tokill, 10, 1) WITH NOWAIT;
    EXEC (@tokill);

    SET @sql = 'ALTER DATABASE [' + @name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;'
    RAISERROR (@sql, 10, 1) WITH NOWAIT;
    EXEC (@sql);

    SET @sql = 'DROP DATABASE [' + @name + ']';
    RAISERROR (@sql, 10, 1) WITH NOWAIT;
    EXEC (@sql);

END

--#endregion Drop databases

sources:

http://stackoverflow.com/questions/349358/sql-server-print-output-doesnt-appear-immediately

http://stackoverflow.com/questions/5777483/drop-all-databases-from-server

How reset the ‘sa’ password of SQL Server:

Lire la suite de Recover ‘sa’ password

(11) General Interference with Organizations and Production

(a) Organizations and Conferences

(1) Insist on doing everything through "channels." Never permit short-cuts to be taken in order to, expedite decisions.

(2) Make "speeches." Talk as frequently as possible and at great length. Illustrate your "points" by long anecdotes and accounts of personal experiences. Never hesitate to make a few appropriate "patriotic" comments.

(3) When possible, refer all matters to committees, for "further study and consideration." Attempt to make the committees as large as possible - never less than five.

(4) Bring up irrelevant issues as frequently as possible.

(5) Haggle over precise wordings of communications, minutes, resolutions.

(6) Refer back to matters decided upon at the last meeting and attempt to reopen the question of the advisability of that decision.

(7) Advocate "caution." Be "reasonable" and urge your fellow-conferees to be "reasonable" and avoid haste which might result in embarrassments or difficulties later on.

(8) Be worried about the propriety of any decision -raise the question of whether such action as is contemplated lies within the jurisdiction of the group or whether it might conflict with the policy of some higher echelon.

(b) Managers and Supervisors

(1) Demand written orders.

(2) "Misunderstand" orders. Ask endless questions or engage in long correspondence about such orders. Quibble over them when you can.

(3) Do everything possible to delay the delivery of orders. Even though parts of an order may be ready beforehand, don't deliver it until it is completely ready.

(4) Don't order new working materials until your current stocks have been virtually exhausted, so that the slightest delay in filling your order will mean a shutdown.

(5) Order high-quality materials which are hard to get. If you don't get them argue about it. Warn that inferior materials will mean inferior work.

(6) In making work assignments, always sign out the unimportant jobs first. See that the important jobs are assigned to inefficient workers of poor machines.

(7) Insist on perfect work in relatively unimportant products; send back for refinishing those which have the least flaw. Approve other defective parts whose flaws are not visible to the naked eye.

(8) Make mistakes in routing so that parts and materials will be sent to the wrong place in the plant.

(9) When training new workers, give incomplete or misleading instructions.

(10) To lower morale and with it, production, be pleasant to inefficient workers; give them undeserved promotions. Discriminate against efficient workers; complain unjustly about their work.

(11) Hold conferences when there is more critical work to be done.

(12) Multiply paper work in plausible ways. Start duplicate files.

(13) Multiply the procedures and clearances involved in issuing instructions, pay checks, and so on. See that three people have to approve everything where one would do.

(14) Apply all regulations to the last letter.

(c) Office Workers

(1) Make mistakes in quantities of material when you are copying orders. Confuse similar names. Use wrong addresses.

(2) Prolong correspondence with government bureaus.

(3) Misfile essential documents.

(4) In making carbon copies, make one too few, so that an extra copying job will have to be done.

(5) Tell important callers the boss is busy or talking on another telephone.

(6) Hold up mail until the next collection.

(7) Spread disturbing rumors that sound like inside dope.

(d) Employees

(1) Work slowly. Think out ways to increase the number of movements necessary on your job: use a light hammer instead of a heavy one, try to make a small wrench do when a big one is necessary, use little force where considerable force is needed, and so on.

(2) Contrive as many interruptions to your work as you can: when changing the material on which you are working, as you would on a lathe or punch, take needless time to do it. If you are cutting, shaping or doing other measured work, measure dimensions twice as often as you need to. When you go to the lavatory, spend a longer time there than is necessary. Forget tools so that you will have to go back after them.

(3) Even if you understand the language, pretend not to understand instructions in a foreign tongue.

(4) Pretend that instructions are hard to understand, and ask to have them repeated more than once. Or pretend that you are particularly anxious to do your work, and pester the foreman with unnecessary questions.

(5) Do your work poorly and blame it on bad tools, machinery, or equipment. Complain that these things are preventing you from doing your job right.

(6) Never pass on your skill and experience to a new or less skillful worker.

(7) Snarl up administration in every possible way. Fill out forms illegibly so that they will have to be done over; make mistakes or omit requested information in forms.

(8) If possible, join or help organize a group for presenting employee problems to the management. See that the procedures adopted are as inconvenient as possible for the management, involving the presence of a large number of employees at each presentation, entailing more than one meeting for each grievance, bringing up problems which are largely imaginary, and so on.

(9) Misroute materials.

(10) Mix good parts with unusable scrap and rejected parts.

(12) General Devices for Lowering Morale and Creating Confusion

(a) Give lengthy and incomprehensible explanations when questioned.

(b) Report imaginary spies or danger to the Gestapo or police.

(e) Act stupid.

(d) Be as irritable and quarrelsome as possible without getting yourself into trouble.

(e) Misunderstand all sorts of regulations concerning such matters as rationing, transportation, traffic regulations.

(f) Complain against ersatz materials.

(g) In public treat axis nationals or quislings coldly.

(h) Stop all conversation when axis nationals or quislings enter a cafe.

(i) Cry and sob hysterically at every occasion, especially when confronted by government clerks.

(j) Boycott all movies, entertainments, concerts, newspapers which are in any way connected with the quisling authorities.

(k) Do not cooperate in salvage schemes.

Source: This is sections 11,12 of the OSS's Simple Sabotage Field Manual, a 1944 document that has been declassified. The OSS became the CIA after WWII. The full document is here.

Perso...

My Movies

Lire la suite de Perso

Unused indexes should be deleted, but you would not drop all the unused indexes without deep analyse.

This a script delete all unused indexes.

- Nonclustered indexes
- Non primary keys
- Non unique
- Non unused  (#Total reads < 100)

Finding Unused Indexes -- Only Nonclustered

SELECT
     db_name() as DatabaseName
    ,sys.schemas.NAME AS ShemaName
    ,sys.tables.NAME AS TableName
    ,sys.indexes.NAME AS IndexName
    ,8 * SUM(sys.allocation_units.used_pages) AS IndexSize
    ,sys.dm_db_index_usage_stats.user_updates AS 'Total Writes'
    ,sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups AS 'Total Reads'
    ,sys.dm_db_index_usage_stats.user_updates - (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) AS 'Difference'
    ,'USE '+ db_name()+';' + 'DROP INDEX '+QUOTENAME(sys.schemas.NAME)+ '.' + QUOTENAME(sys.tables.NAME) + '.' +QUOTENAME(sys.indexes.NAME) + ';' + CHAR(13) + CHAR(10) + 'GO'
FROM sys.partitions
INNER JOIN sys.indexes ON sys.partitions.index_id = sys.indexes.index_id
    AND sys.partitions.object_id = sys.indexes.object_id
INNER JOIN sys.schemas
INNER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id ON sys.partitions.object_id = sys.tables.object_id INNER JOIN sys.allocation_units ON sys.partitions.partition_id = sys.allocation_units.container_id INNER JOIN sys.dm_db_index_usage_stats ON sys.indexes.object_id = sys.dm_db_index_usage_stats.object_id
    AND sys.indexes.index_id = sys.dm_db_index_usage_stats.index_id
WHERE
        sys.indexes.is_primary_key = 0   -- non primary key
    AND sys.indexes.is_unique = 0        -- non unique
    AND sys.indexes.type in (2, 6, 7)     -- Only Nonclustered -- https://msdn.microsoft.com/en-us/library/ms173760.aspx
    AND (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) < 100  -- unused !!!
GROUP BY
     sys.schemas.NAME
    ,sys.tables.NAME
    ,sys.indexes.NAME
    ,sys.dm_db_index_usage_stats.user_seeks
    ,sys.dm_db_index_usage_stats.user_scans
    ,sys.dm_db_index_usage_stats.user_lookups
    ,sys.dm_db_index_usage_stats.user_updates
ORDER BY IndexSize DESC;

Lire la suite de Unused indexes

Script to show all opened transactions:

SELECT d.name as DatabaseName
, ses.host_name as [host_name]
, ses.login_time AS session_login_time
, ses.last_request_start_time
, ses.last_request_end_time
, ses.login_name
, ses.nt_user_name
, ses.STATUS
, tst.session_id as SPID
, tst.enlist_count AS nr_active_reqs_in_session
, tst.is_user_transaction
, CASE tst.is_user_transaction WHEN 1 THEN 'Transaction initiated by user request'
                               WHEN 0 THEN 'System transaction' END AS tran_status
, tst.is_local
, CASE tst.is_local WHEN 1 THEN 'Local transaction'
                    WHEN 0 THEN 'Distributed transaction' END AS is_local_description
, tat.NAME
, tat.transaction_begin_time
, DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS Duration
, CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
                            WHEN 2 THEN 'Read-only transaction'
                            WHEN 3 THEN 'System transaction'
                            WHEN 4 THEN 'Distributed transaction' END AS tran_type_description
, tat.transaction_state
, CASE tat.transaction_state WHEN 0 THEN 'The transaction has not been completely initialized yet'
                             WHEN 1 THEN 'The transaction has been initialized but has not started'
                             WHEN 2 THEN 'The transaction is active'
                             WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
                             WHEN 4 THEN 'The commit process has been initiated on the distributed transaction'
                             WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'
                             WHEN 6 THEN 'The transaction has been committed'
                             WHEN 7 THEN 'The transaction is being rolled back'
                             WHEN 8 THEN 'The transaction has been rolled back' END AS transaction_state_description
, tat.dtc_state
, CASE tat.dtc_state WHEN 1 THEN 'ACTIVE'
                     WHEN 2 THEN 'PREPARED'
                     WHEN 3 THEN 'COMMITTED'
                     WHEN 4 THEN 'ABORTED'
                     WHEN 5 THEN 'RECOVERED' END AS dtc_state_description
, tst.is_enlisted
, tst.is_bound AS bound_status
, CASE tst.is_bound WHEN 1 THEN 'active via bound sessions'
                    WHEN 0 THEN 'not active via bound sessions' END AS bound_status_description
, est.[text] [StatementText]
, 'BEGIN TRY kill '+CAST(tst.session_id AS VARCHAR(5))+'; END TRY BEGIN CATCH END CATCH;' as SqlScriptToKillTransaction
FROM            sys.dm_exec_sessions                              AS ses
INNER JOIN      sys.dm_tran_session_transactions                  AS tst ON ses.session_id = tst.session_id
INNER JOIN      sys.dm_tran_active_transactions                   AS tat ON tst.transaction_id = tat.transaction_id
INNER JOIN      sys.dm_tran_database_transactions                 AS tdt ON tat.transaction_id = tdt.transaction_id
INNER JOIN      sys.databases                                     AS d   ON tdt.database_id = d.database_id
LEFT OUTER JOIN sys.dm_exec_requests                              AS er  ON ses.[session_id] = er.[session_id]
LEFT OUTER JOIN sys.dm_exec_connections                           AS ec  ON ec.[session_id] = ses.[session_id]
OUTER APPLY     sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) AS est
WHERE d.database_id > 4

Lire la suite de Opened transactions - Sql Server

We can easily concatenate column values into a comma-separated list with a variable, but this solution we can avoid to use a variable.

PRINT '-- Concatenating Column Values into a Comma-Separated List'

GO

--#region Create my test table

begin try drop table YourTable ; end try begin catch end catch -- begin try catch

create table YourTable (colKey nvarchar(50), colVal nvarchar(1), colOrderBy int);

insert YourTable values ('a',3, 1), ('a', 1, 2), ('b',8, 3), ('b',7, 1), ('b',6, 2);

--#endregion Create my test table

 

 

GO

SELECT DISTINCT colKey

,STUFF((

       SELECT (', ' + CAST(tbDetail.colVal AS NVARCHAR))  -- you do not neew CAST if you use a string type.

       FROM YourTable tbDetail(NOLOCK)

       WHERE tbDetail.colKey = o.colKey

       ORDER BY tbDetail.colOrderBy  -- if you want, you can order by your columns

       FOR XML PATH('')

       ), 1, 2, '') MyStuffList

FROM YourTable o(NOLOCK)

Source : http://stackoverflow.com/questions/1048209/concatenating-column-values-into-a-comma-separated-list

Des gens d’une mauvaise foi absolue, vous en avez forcément dans votre entourage. En général, ceux-ci sont prêts à user de toutes les malhonnêtetés argumentatives pour tenter de vous convaincre… ce qui est souvent très énervant! Mais voyez plutôt le bon côté des choses, ces abrutis vous offrent sur un plateau un véritable terrain d’expérimentations qui vous permettra de devenir expert ès sophisme.
C’est parti pour une visite guidée des arguments les plus fallacieux qu’on peut vous opposer!

Lire la suite de 15 Techniques infaillibles pour prendre votre interlocuteur pour un con

A config file for a .NET application is a text file that has a name of myapplication.exe.config.

The Windows CE does not make things easy for you!
It allows you to add a file named “App.config” to your project and it will copy it to the appropriate bin directory and rename it to myapplication.exe.config, but you cannot access it.

App.condig is nothing more than XML. Here's my version to access config file by Windows CE Application :

Lire la suite de Writing to Config file WITHOUT "System.Configuration" (Windows CE)