MSSQL:Script Utili2
From Aino Wiki
Contents
Ricerche
Testo in Stored Procedure e Funzioni
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%''MGM''%'
Oggetti: tabelle, etc
IF OBJECT_ID ('Contacts1', 'U') IS NOT NULL DROP TABLE dbo.Contacts1;
Conversioni
Numeri da base a base
Da base N a decimale
CREATE FUNCTION dbo.fn_FromBasenTo10(@String varchar(20),@N int) RETURNS BIGINT AS BEGIN DECLARE @Result AS BIGINT = 0, @basestring AS VARCHAR(100) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', @digit AS CHAR, @pow AS BIGINT = 0 WHILE @pow < len(@String) BEGIN set @digit = substring(reverse(@String), @Pow+1,1) set @Result = @Result + (charindex(@Digit, @baseString) - 1) * power(@N, @pow) set @pow = @pow + 1 END RETURN @Result END GO --------------------- Uso -------------------- select dbo.fn_FromBasenTo10('001L9', 36) --Restituirà: 2061
Da base decimale a base 32
CREATE FUNCTION dbo.fn_FromBase10To36 ( @Val BIGINT ) RETURNS VARCHAR(9) AS BEGIN DECLARE @Result VARCHAR(9) = '' IF (@Val <= 0) BEGIN RETURN '0' END WHILE (@Val > 0) BEGIN SELECT @Result = CHAR(@Val % 36 + CASE WHEN @Val % 36 < 10 THEN 48 ELSE 55 END) + @Result, @Val = FLOOR(@Val/36) END RETURN @Result END GO --------------------- Uso -------------------- select dbo.fn_FromBase10To36(2061) --Restituirà: '001L9'
Templates
Script in transazione
Esempio di come costruire uno script che, usando una transazione,
-- DEVELOPER NAME – REFERENCE GAP/BUG SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- Code goes here COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ERROR_SEVERITY INT, @ERROR_STATE INT, @ERROR_NUMBER INT, @ERROR_LINE INT, @ERROR_MESSAGE NVARCHAR(4000); SELECT @ERROR_SEVERITY = ERROR_SEVERITY(), @ERROR_STATE = ERROR_STATE(), @ERROR_NUMBER = ERROR_NUMBER(), @ERROR_LINE = ERROR_LINE(), @ERROR_MESSAGE = ERROR_MESSAGE(); RAISERROR('Msg %d, Line %d, :%s', @ERROR_SEVERITY, @ERROR_STATE, @ERROR_NUMBER, @ERROR_LINE, @ERROR_MESSAGE); /* ATTENZIONE l'esecuzione comunque continuerà sino alla fine */ END CATCH
Relazioni e Constraint
Gerarchia relazioni
Assegna un livello più basso alle tabelle che hanno meno relazioni esterne, sono le tabelle di lookup ovvero quelle che son più referenziate dalle altre
WITH FK_TABLES AS (
SELECT S1.NAME AS FROM_SCHEMA
, O1.NAME AS FROM_TABLE
, S2.NAME AS TO_SCHEMA
, O2.NAME AS TO_TABLE
FROM SYS.FOREIGN_KEYS FK
INNER JOIN SYS.OBJECTS O1
ON FK.PARENT_OBJECT_ID = O1.OBJECT_ID
INNER JOIN SYS.SCHEMAS S1
ON O1.SCHEMA_ID = S1.SCHEMA_ID
INNER JOIN SYS.OBJECTS O2
ON FK.REFERENCED_OBJECT_ID = O2.OBJECT_ID
INNER JOIN SYS.SCHEMAS S2
ON O2.SCHEMA_ID = S2.SCHEMA_ID
/*FOR THE PURPOSES OF FINDING DEPENDENCY HIERARCHY
WE'RE NOT WORRIED ABOUT SELF-REFERENCING TABLES*/
WHERE NOT ( S1.NAME = S2.NAME
AND O1.NAME = O2.NAME)
)
,ORDERED_TABLES AS
( SELECT S.NAME AS SCHEMANAME
, T.NAME AS TABLENAME
, 0 AS LEVEL
FROM ( SELECT *
FROM SYS.TABLES
WHERE NAME <> 'SYSDIAGRAMS') T
INNER JOIN SYS.SCHEMAS S
ON T.SCHEMA_ID = S.SCHEMA_ID
LEFT OUTER JOIN FK_TABLES FK
ON S.NAME = FK.FROM_SCHEMA
AND T.NAME = FK.FROM_TABLE
WHERE FK.FROM_SCHEMA IS NULL
UNION ALL
SELECT FK.FROM_SCHEMA
, FK.FROM_TABLE
, OT.LEVEL + 1
FROM FK_TABLES FK
INNER JOIN ORDERED_TABLES OT
ON FK.TO_SCHEMA = OT.SCHEMANAME
AND FK.TO_TABLE = OT.TABLENAME
)SELECT DISTINCT OT.SCHEMANAME
, OT.TABLENAME
, OT.LEVEL
FROM ORDERED_TABLES OT
INNER JOIN (
SELECT SCHEMANAME
, TABLENAME
, MAX(LEVEL) MAXLEVEL
FROM ORDERED_TABLES
GROUP BY SCHEMANAME,TABLENAME
) MX
ON OT.SCHEMANAME = MX.SCHEMANAME
AND OT.TABLENAME = MX.TABLENAME
AND MX.MAXLEVEL = OT.LEVEL
AND OT.TABLENAME LIKE '%[_]S[_]%'
or OT.TABLENAME LIKE 'SY%'
ORDER BY OT.LEVEL DESC , OT.TABLENAME
Constraint Toglierli
Per singola tabella
ALTER TABLE tableName NOCHECK CONSTRAINT ALL
Per TUTTE le tabelle in automatico
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
Constraint Ristabilirli
Per singola tabella
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
Per TUTTE le tabelle in automatico
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
Per tutte le tabelle senza interruzione al primo errore:
Progressivo per Identity
Come resettare l'Identity seed? Trattasi spesso di campo identità chiave primaria con autoincremento.
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO
Operazioni Massive
Bulk Insert
(riportato da altra sezione non manutenibile)
bulkinsert
Attenzione a che SQL SERVER abbia i diritti necessari nella cartella in cui si accede e che possa vederla.
Attenzione nel caso ci siano colonne campi con identity (es la prima colonna) occorre prevedere anche il contenuto per quel campo ovviamente lasciandolo vuoto.
Es. file CSV da importare
CAMPO_1,CAMPO_2,CAMPO_3 1,393403939199,390110682865 2,393487017598,3905711605793 3,393404554674,3905221403508 4,393402640286,390249787047 5,393453396121,3908281840785 6,393421639555,390445526150 7,393408589408, 8,393458389120,39078466315 9,393421424335,390875872441 10,393421437504,390957695669 11,393426631023,3909321942107 12,393452210188,3903451821441
BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = ',')
Altro esempio:
BULK INSERT [VF_MSIDN_GN] FROM 'D:\Test\TxtFile1.txt' WITH (FIRSTROW=2, --Per scartare l'intestazione CHE è AL RIGO\RECORD 1 FIELDTERMINATOR=',', ROWTERMINATOR='\n');
Soluzione sul terminatore
Da mssqltips.com Tenuto conto che senza alcuna indicazione il valore predefinito è \n (carattere di nuova riga).
Potrebbe darsi che non funziona l'identificazione del carattere terminarore di riga, del parametro "ROWTERMINATOR".
Un altro modo per identificarlo è usando il codice esadecimale del carattere:
(LF) = '0x0a' Ad es. per files importati da UNIX
(CR) = '0x0d' Ad es. per files importati da MAC
Quindi nel caso si importi un file da UNIX si userà:
BULK INSERT [VF_MSIDN_GN] FROM 'D:\Da Linux.txt' WITH (FIRSTROW=1, --Per scartare l'intestazione FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a'--Equivalente di \n ovvero (LF) = Line Feed );
TABELLA terminatori con codifica esadecimale:
Shortcut Codice Esadecimale Codice mnemonico Descrizione \n 0x0a LF Line feed = nuova linea \r 0x0d CR Carriage return = ritorno accapo
Bulk insert dinamica
DECLARE @PathToCheck VARCHAR(512) = 'D:\Documenti\DOC Files\Vodafone\_Attivita\Migrazione TTU Premium PIN ver 2.0', @FileName VARCHAR(256) = 'Segrate-dati_Landolfi-Recorder.txt', @PathFull VARCHAR(512), @SQLCommand AS NVARCHAR(MAX), @TableName AS NVARCHAR(50) = '[LANDOLFI_RECORDER]' SET @PathFull = @PathToCheck + '\' + @FileName SET @SQLCommand = 'BULK INSERT [MVNOTeleTu].[dbo].' + @TableName + ' FROM ''' + @PathFull + ''' WITH (FIRSTROW=2, --Per scartare intestazione FIELDTERMINATOR='',''); ' Print 'Exec: ' + @SQLCommand EXECUTE sp_executesql @SQLCommand
Utility BCP
Link interno: BCP
Backup \ Restore
Backup
Restore
Anziché usare SSMS da script si può:
--Imposta il Database a "single user Mode" ALTER DATABASE [RS_Control_VNO] SET SINGLE_USER WITH ROLLBACK IMMEDIATE --Ripristino: RESTORE DATABASE [RS_Control_VNO] FROM DISK = 'D:\SQL_DB_Backup\RS_Control_backup_2019_09_12_181215_5991987.bak' WITH MOVE 'RS_Control_VNO' TO 'D:\SQL_DB_Data\RS_Control_VNO.mdf', --MDF LogicalName MOVE 'RS_Control_VNO_log' TO 'D:\SQL_DB_Log\RS_Control_VNO_log.ldf' --LDF LogicalName /* Se non si verificano errori nello statement precedente il database sarà ora in modalità multiuser. Se si verifica un errore andrà comunque eseguito il seguente comando per ripristinare la modalità multiutente.*/ ALTER DATABASE [RS_Control_VNO] SET MULTI_USER
Che nome dare a MDF e LDF logical name:
Esecuzione SSIS packages
Esempi su come usare istruzioni Transact-SQL per eseguire un pacchetto SSIS archiviato nel catalogo SSIS.
Materiale:
- Da docs.microsoft.com
- Better ways: timmitchell.net
Il seguente script E' in modo ASINCRONO!
DECLARE @execution_id AS bigint, @var0 AS smallint = 1 EXEC [SSISDB].[catalog].[create_execution] --SP che prepara l'esecuzione ma non lancia il SSIS @package_name = N'SP_ES_Project Import.dtsx', @execution_id = @execution_id OUTPUT, @folder_name = N'CC_CV', @project_name = N'Project ES', @use32bitruntime= True, -- Caso esecuzione con Driver a 32 bit !!! @reference_id = Null, @useanyworker = True, @runinscaleout = False--True !!!!!!!!!!!!!!!! SELECT @execution_id EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 50, @parameter_name = N'LOGGING_LEVEL', --Usare "SYNCHRONIZED'" x esecuzione SINCRONA! @parameter_value= @var0 EXEC [SSISDB].[catalog].[start_execution] --SP che da il via alla esecuzione del SSIS @execution_id, @retry_count=0 GO --ATTENZIONE attendere qualche secondo per avere il risultato perché è una esecuzione NON SINCORNA! --Dà comunque esito positivo a prescindere se il SSIS fallisce!!!
Purtroppo per verificare se l'esecuzione è realmente andata a buon fine aggiungere sotto il seguente script
-- Check package status, and fail script if the package failed IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id) RAISERROR('Esecuzione del SSIS fallita.', 16, 1)
Lo script di sopra può essere generato automaticamente eseguire i passi indicati qui sotto:
Soluzione problemi
Resettare campo password in una tabella
Capita di dover resettare un campo password di un record in tabella con una di default conosciuta. Il problema è nel fatto che il campo password è crittografato quindi non si può nè copiare ed incollare semplicemente e nè decodificare quindi si può copiare la password usata in un record per impostare quella di un altra, ecco la tecnica
-- Per prova metto a confronto in un unico rigo la password del record da cambiare -- con quella del record da usare come copia template SELECT am_dest.Password P_Dest, am_dest.PasswordSalt P_Dest_salt, am_source.Password P_Source, am_source.PasswordSalt P_Source_salt FROM [FoxCCAccounts].[dbo].[aspnet_Membership] as am_dest, [FoxCCAccounts].[dbo].[aspnet_Membership] as am_source where am_source.UserId = '6D005825-8DE3-4690-B521-E2D5B1F047AC' and am_dest.UserId = '4D750AA6-C87A-4FDA-A9A4-C5D66016A251' -- Infine l'update: UPDATE am_dest SET am_dest.Password = am_source.Password, am_dest.PasswordSalt = am_source.PasswordSalt FROM [FoxCCAccounts].[dbo].[aspnet_Membership] as am_dest, [FoxCCAccounts].[dbo].[aspnet_Membership] as am_source where am_source.UserId = '6D005825-8DE3-4690-B521-E2D5B1F047AC' and am_dest.UserId = '4D750AA6-C87A-4FDA-A9A4-C5D66016A251'
Esecuzione di una store con parametri funzioni
Se si vuol eseguire una store procedure da Management studio passando, ad es, in alcuni parametri la data odierna con la funzione GetDate() occorrerà eludere il problema come segue, usando la valorizzazione esterna dei parametri.
EXEC [dbo].[p_CU_B_ACC_TRACK_EXT_NZL_Insert] @COMPANY_CODE = '071', @DIVISION_CODE = '001', @CUSTOMER_CODE = '075900MP', @DT_CLAIM_LODGED = @data, @DT_QUESTIONNAIR_COMPLETE = @data, @DT_ENT_APPOINTMENT_SCHEDULED = @data, @DT_AUDIOGRAM_SENT = @data, @DT_ENT_APPOINTMENT = @data, @DT_NEXT_RECALL = @data, @FLG_FUNDING_APPROVED = '0', @FLG_FUNDING_COMPETITOR = '0', @FLG_FUNDING_DECLINED = '0', @FLG_NOT_MOTIVATED = '0', @FLG_NOT_CONTACT = '0', @FLG_NOT_COMPETITOR = '0', @FLG_ACC_NEW = '0', @FLG_ACC_REAID = '0', @USERINSERT = 'SPXMIW2869\giuseppe.aino', @ROWGUID = '00000000-0000-0000-0000-000000000000'
Correzione di errori
Autenticazione
Se da una una applicazione WEB .Net non si riesce ad accedere al DB, usando l'autenticazione integrata di Windows, a causa del seguente errore:
Login failed for user 'IIS APPPOOL\DefaultAppPool'
Ciò è dovuto al fatto che l'utente IIS non ha diritti per acedere al DB neanche in sola lettura. Il seguente script, da lanciare sul DB Master, crea una nuova Login e nuovo utente da associare all'aplication Pool, ATTENZIONE che dopo averlo fatto occorrerà finalmente associare i diritti\ruoli che si vogliono all'utente sul DB su cui si vuol lavorare.
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\DefaultAppPool') BEGIN CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] END GO CREATE USER [WebDatabaseUser] FOR LOGIN [IIS APPPOOL\DefaultAppPool] GO EXEC sp_addrolemember 'db_owner', 'WebDatabaseUser' GO
Funzionali
Nr chiamate contemporanee
Sulla tabella [ag_cdr] son memorizzati i records relativi alle chiamate telefoniche, ogni chiamata inizia all'istante memorizzato nel campo adetdate.
Si vuole determinare il numero di chiamate intervenute nell'arco di 10" restringendo la selezione in un intervallo temporale.
SELECT --adetdate, LEFT(CONVERT(VARCHAR, adetdate, 120), 18), COUNT(*) AS TotChiamate FROM [ag_cdr] WHERE adetdate BETWEEN '2018-06-01 00:00:00.000' AND '2018-06-30 23:59:59.999' GROUP BY LEFT(CONVERT(VARCHAR, adetdate, 120), 18) ORDER BY TotChiamate DESC
In pratica si raggruppano tutti i records che hanno data trasformata in stringa in cui si son tolte le unità decimali dei secondi. La LEFT estrae una sottostringa di n caratteri (18 nel nostro caso) a partire dalla sinistra. 120 è il codice di conversione per ottenere una stringa nel formato: yyyy-mm-dd hh:mm:ss
Mappa e Link
Parole chiave:
