Metti offline/online un database

Prima di mettere un DB offline, è necessario KILLare tutti gli utenti collegati:

USE master
GO

PRINT N'

**** KILL
'

DECLARE @kill varchar(8000) ; SET @kill = ''

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid IN 
  (
     db_id('dabase_name')
  )

IF @kill = ''
 PRINT 'No connection found.'
ELSE
 EXEC (@kill)


-------------------------------------------------------------------------------

PRINT N'
**** TRY TO OFFLINE DATABASE
'

ALTER DATABASE dabase_name SET OFFLINE
GO
PRINT 'Done.'

Mettere online un database:

use master
GO

ALTER DATABASE dabase_name SET ONLINE
GO

Testato su SQL 2008 R2.

Installare font da VB/VBScript

Prende tutti i font in una cartella su un server e li installa sul client, se:
- non esistono sul client
- il file ha estensione .ttf o .otf

function GetOsName

 Dim objWMI, objItem, colItems
 Dim strComputer, VerBig, OSystem

 ' Here is where we interrogate the Operating System
 ' On Error Resume Next

 ' Get the computer name dot = this computer.
 strComputer = "."
 ' This is where WMI interrogates the operating system
 Set objWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

 Set colItems = objWMI.ExecQuery("Select * from Win32_OperatingSystem",,48)

 ' Here we filter Version from the dozens of properties
 For Each objItem in colItems
  VerBig = Left(objItem.Version,3)
 Next

 ' Spot VerBig variable in previous section
 ' Note the output variable is called OSystem

 Select Case VerBig
  Case "6.2" OSystem = "8"
  Case "6.1" OSystem = "7"
  Case "6.0" OSystem = "Vista"
  Case "5.2" OSystem = "2003"
  Case "5.1" OSystem = "XP"
  Case "5.0" OSystem = "2000"
  Case "4.0" OSystem = "NT"
  'Case Else OSystem = "Unknown - probably Win 9x"
  case Else OSystem = ""
 End Select

 Set objWMI = nothing
 Set colItems = nothing

 GetOsName = OSystem

end function


function installaFont()

 ' http://www.bohack.com/2012/09/installing-fonts-on-windows-7-from-a-vbscript/

 const fileCopyOverwrite = true

 dim wShell
 dim clientDir, serverDir
 dim fs, fsoFolder, file
 dim wApp, appFolder, currentFile
 dim ext
 dim wFrom, wTo
 dim appClientFolder
 dim hoFattoQualcosa
 dim os
 dim s

 installaFont = false
 
 hoFattoQualcosa = false

 Set wShell = Wscript.CreateObject("Wscript.Shell")
 Set fs = CreateObject("Scripting.FileSystemObject")
 Set wApp = CreateObject("Shell.Application")


 clientDir = wShell.SpecialFolders("Fonts")
 serverDir = "\\your\server\path\to\Fonts"


 Set appFolder = wApp.Namespace(serverDir)
 Set fsoFolder = fs.GetFolder(serverDir)

 os = GetOsName

 For each file In fsoFolder.Files
  ' -- INSTALLA SOLO I FONT MANCANTI
  If Not fs.FileExists(clientDir & "\" & file.Name) Then
   
   Set currentFile = appFolder.ParseName(file.name)
   ext = lcase(right(currentFile, 4))

   if ext = ".ttf" or ext = ".otf" then
    select case os
     case "7"
      currentFile.InvokeVerb("Install")
      hoFattoQualcosa = true

     case "XP"
      wFrom = serverDir & "\" & currentFile
      wTo = clientDir & "\" & currentFile
  
      fs.CopyFile wFrom, wTo, fileCopyOverwrite
      hoFattoQualcosa = true

     case else
      s = ""
      s = s & "Impossibile installare il font ''" & ucase(replace(file.name, ext, "")) & "'': "
      s = s & "sistema operativo ''" & os & "'' non supportato dall'installazione."

      msgbox s, vbInformation, titoloApp
      installaFont = false
      exit function

    end select
   end if
  End If
 Next


 if hoFattoQualcosa then
  select case os
   case "XP"
   ' -- APRE LA DIRECTORY DEI FONT, ALTRIMENTI NON SONO "VISTI" DALLE APPLICAZIONI:
   ' -- FACENDO COSI', FA UN "REFRESH"
   set appClientFolder = wApp.Namespace(clientDir).self
   appClientFolder.invokeVerb("open")
   set appClientFolder = nothing
 
  end select
 end if



 Set currentFile = Nothing
 set wShell = nothing
 Set appFolder = Nothing
 Set wApp =  Nothing
 Set fsoFolder = Nothing
 Set fs = Nothing

 installaFont = true

End function

Note:
- su Win7 basta richiamare il verbo "installa"
- su WinXP non c'è tale verbo, quindi copio il file a mano nella cartella di sistema e poi la apro, per "forzare" una sorta di refresh dei font, altrimenti le applicazioni (es: Office2010) non "vedono" i nuovi font



Farina del mio sacchetto, ma basato sullo script trovato qui.

MSSQL: cercare gli ultimi oggetti modificati

Query per cercare gli ultimi n oggetti modificati in un database:

SELECT TOP 10 *
FROM sys.all_objects
ORDER BY modify_date DESC

Query per cercare in tutti i database:

EXEC sp_MSForEachDB N'
USE [?]; 

SELECT DB_NAME();

SELECT TOP 10 *
FROM sys.all_objects
ORDER BY modify_date DESC
'

SQL Server - Knowing the Use of Deprecated or Discontinued Features

Thinking Simple

There are multiple options and we need a systematic way to solve this problem. We will start by doing some simple queries to DMVs to understand if we are using any deprecated features.

SELECT OBJECT_NAME,
counter_name,
instance_name AS 'Deprecated Feature',
cntr_value AS 'Number of Times Used'
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%:Deprecated%'
AND cntr_value > 0
ORDER BY 'Number of Times Used' DESC
GO

I told my DBA friend to run the above query to find out if anything is still around. I always say to have a baseline trace to rerun on an upgraded test environment to know if there are still features we need to work on. This is always not simple but this is same as what we get from Perfmon counters. These two must match. If you are not aware, here are the steps:

Open up Performance Monitor (Perfmon) and under the SQL Server counters add the Deprecated Features / Usage for all counters by selecting all and Clicking ADD.

Via.