Login Login
MORE

WIDGETS

Widgets

Wanted articles
Who is online?
Article tools

MSSQL:Nozioni varie

From Aino Wiki

Jump to: navigation, search

Contents

Acquisizioni

Utility BCP

Il file è presente al seguente percorso:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

Fonti:

Bulk Copy Program, sintassi:

bcp [database_name.] schema.{table|view|"query"}
    {out|queryout|in|format}
    {data_file|nul}
    {[optional_argument]...}
BCP help usage.png

Al path: C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

NOTA per risolvere problemi con la conversione dei caratteri, per evitare caratteri imprevisti suggerisco l'opzione -w
Esempio realistico, esportazione su un file CSV:

bcp "exec dbName.dbo.storedProcedureEstrazioneDati" queryout "fileName.csv" SQLServerMachineName -S -c -U "userName" -P "userPassword"

Da SQL management Studio si userà il comando 'BCP' da FileSystem usando la SP 'xp_c mdshell':

DECLARE @SQLServerName    AS nvarchar(max),
        @StoredProcName   AS varchar(100) = 'NomeStoredDiEstrazioneDati',
        @DBName           AS varchar(100) = 'NomeDelDataBase',
        @DOS_BCPCommand   AS nvarchar(max)
 
SET @SQLServerName = convert(nvarchar(max), SERVERPROPERTY('MachineName'));
 
SET @SQLCommand = 'bcp "exec ' + @DBName + '.dbo.' + @StoredProcName + '" queryout "fileName.csv" ' 
                  + @SQLServerName + ' -S -c -U "userName" -P "userPassword"';
 
EXEC master..xp_cm dshell @DOS_BCPCommand

ATTENZIONE al parametro della stored procedure 'xp_cm dshell', NON deve essere un varchar(max) ma varchar(2000) in quanto ha un baco e non riuscirebbe la conversione nel tipo atteso come parametro dalla SP.
NOTA questa Stored procedure esegue uno shell script (DOS o di comando terminale) pertanto può essere usato per svariate funzioni da implementare opportunamente. Però per essere usato occorre abilitarlo perchè per sicurezza è disabilitato di default vedere su questa pagina

Esportare dati

Es. 1) Aprire una finestra di prompt comandi e scrivere:

X:>bcp MVNOPoste.dbo.pp_cdr OUT D:\Tmp\pp_cdr.txt -c -T -S SPXMIW2869
  1. MVNOPoste.dbo.pp_cdr è il database.schema.tabella;
  2. OUT specifica la direzione della copia bulk, OUT indica esportazione dati da DB verso file; i valori possibili sono: [in], [out], [queryout], [format]
  3. D:\Tmp\pp_cdr.txt è il file su cui saranno copiati i dati;
  4. -c (opzione di tipo formattazione dei dati) usa il tipo char come tipo di archiviazione, con il carattere di tabulazione ( \t ) come separatore di campo e il carattere di nuova riga ( \r\n ) come carattere di terminazione della riga; altri valori possibili: [-n] (native format), [-N] (unicode native format), [-w] (unicode character format)
  5. -T indica l'uso della Windows autentication per l'accesso al DB
  6. -S è usato per specificare il nome del server SQL, ovvero: SPXMIW2869.

Es. 2) Si specifica con -t il carattere di separazione tra campi (sovrascrive il default che indica -c):

X:>bcp MVNOPoste.dbo.pp_cdr OUT D:\Tmp\pp_cdr.txt -c -t, -T -S SPXMIW2869
NOTA NON ESISTE MODO per far fare operazioni al BCP come TRIM() sui campi, eliminazione dei NULL etc, queste però si posson fare sulle query di estrazione!
BCP example 01.png

Il file prodotto è il seguente, notare la codifica dei caratteri NULL e gli spazi vuoti che però rispecchiano il contenuto della tabella origine:

BCP example 02.png

Ecco cos'è sul DB origine:

BCP example 03.png

Es. 3) Si specifica la query di estrazione dati queryout:

X:>bcp "SELECT TOP 10 * FROM MVNOPoste.dbo.pp_cdr ORDER BY id" queryout D:\Tmp\pp_cdr.txt -c -t, -T -S SPXMIW2869

Uso di un Format file

L'operazione si conclude in due momenti:

  1. si crea il file di formato (*.fmt o *.xml), che potrà anche essere un XML
  2. si usa il file creato prima indicando questa opzione nel comando di importazione
FMT
X:>bcp MVNOPoste.dbo.pp_cdr format null -n -t, -f D:\Tmp\pp_cdr.fmt -T -S SPXMIW2869

infine si può esportare su output file usando il file, di formato, creato

X:>bcp MVNOPoste.dbo.pp_cdr out D:\Tmp\pp_cdr.txt -f D:\Tmp\pp_cdr.fmt -S SPXMIW2869 -T

Esempio:

13.0
146
1       SQLCHAR             0       12      ","      1     a_qos                                                                ""
2       SQLCHAR             0       50      ","      2     a_tclass                                                             SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       50      ","      3     acarrier                                                             SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       50      ","      4     acarrier_tariff                                                      SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       50      ","      5     acarrier_tclass                                                      SQL_Latin1_General_CP1_CI_AS


146     SQLCHAR             0       24      "\r\n"   146   writetime                                                            ""
XML

Da SQL Server 2005 si possono usare files XML per definire il formato dell'esportazione. Occorre includere l'argomento -x

X:>bcp MVNOPoste.dbo.pp_cdr format null -n -t, -f D:\Tmp\pp_cdr.xml -T -S SPXMIW2869 -x

Ecco:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="64" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <!-- etc -->
  <FIELD ID="146" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="a_qos" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="a_tclass" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="acarrier" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="acarrier_tariff" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="acarrier_tclass" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="acarrier_totchg" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="7" NAME="acarrier_zone" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="acause" xsi:type="SQLINT"/>
  <COLUMN SOURCE="9" NAME="account" xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="10" NAME="acondate" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="11" NAME="ada" xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="12" NAME="ada_domain" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="13" NAME="adetdate" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="14" NAME="adur" xsi:type="SQLINT"/>
  <COLUMN SOURCE="15" NAME="adur_rnd" xsi:type="SQLINT"/>
  <COLUMN SOURCE="16" NAME="aenddate" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="17" NAME="agent_id" xsi:type="SQLINT"/>
  <COLUMN SOURCE="18" NAME="aleg_id" xsi:type="SQLVARYCHAR"/>
  <!-- etc -->
  <COLUMN SOURCE="146" NAME="writetime" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>
FOR XML

Oppure da SQL Server Management Studio:

SET @QrySQL = 'SELECT [ID_CAMPAIGN],[DDI],[FEE_1],[FEE_2],[ONLUS],[START_CAMPAIGN],[END_CAMPAIGN]'
			+ ',[CAMPAIGN_OPERATOR],[CAMPAIGN_TYPE],[FUNC_MODE_VALUE],[UPDATE_DATA]'
			+ ',[UPDATE_LOGIN],[CAMPAIGN_STATUS] '
			+ 'FROM [MNOV].[dbo].[T_D_CAMPAIGN]';
SET @PathOutFile = 'D:\Tmp\bcptest.xml';
SET @Cmd = 'bcp "' + @QrySQL + ' FOR XML PATH(''Record''), ROOT(''Table'')" queryout "' + @PathOutFile + '" -T -c -t -r '
 
Print @Cmd
EXEC xp_cm dsh ell @Cmd

Purtroppo il file XML scritto non ha il tipico primo rigo di direttiva (XML declaration) che talvolta ha l'encoding per il parser XML:

<?xml version='1.0'?>
<!-- oppure: -->
<?xml version="1.0" standalone="yes"?>
<!-- oppure: -->
<?xml version=“1.0” encoding=“utf-8”?>

un modo per ovviare è quello di aggiungere al file ASCII XML la testa XML declaration mediante un batch DOS che lo aggiunge copiandolo da un apposito file ASCII allo scopo, es:

copy fileXMLDeclaration.txt + fileDaTabellaDB.xml fileXMLFinale.xml
Scrittura su File

Importare dati

 

Upload file binary in tabella e viceversa

Usando una applicazione implementata in C#, DOC stackoverflow.com

Method to put file into database from drive

public static void databaseFilePut(string varFilePath) {
    byte[] file;
    using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read)) {
        using (var reader = new BinaryReader(stream)) {
            file = reader.ReadBytes((int) stream.Length);       
        }          
    }
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
        sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
        sqlWrite.ExecuteNonQuery();
    }
}

This method is to get file from database and save it on drive

public static void databaseFileRead(string varID, string varPathToNewLocation) {
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write)) 
                    fs.Write(blob, 0, blob.Length);
            }
    }
}

Esempio completo

Dato un file lo carica su una tabella del DB e poi lo scarica assegnandogli un nuovo nome e secondo il percorso indicato nel path. Il nome del file uplodato finisce nella tabella di memorizzazione e funge da chiave per il download.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace WA_OccorrenzeTraDueFile
{    
    public partial class FrmMain : Form
    {
        private static string m_connectionString;
 
        private static bool m_Debug_Captive2
        {
            get
            {
                string strBool = ConfigurationManager.AppSettings["Debug_Captive2"].ToString();
                return strBool == "1";
            }
        }
 
        public static string ConnectionString
        {
            get
            {
                if (string.IsNullOrWhiteSpace(m_connectionString))
                {
                    m_connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
                }
                return m_connectionString;
            }
        }
 
        public FrmMain()
        {
            InitializeComponent();
        }
        private void Btn_FileDB_UpDw_Click(object sender, EventArgs e)
        {
            string errorMessage = string.Empty;
            string fullPath_File1 = string.Empty;
            string fullPath_File2 = string.Empty;
            string DBFileNameKey = string.Empty;
 
            try
            {
                Cursor.Current = Cursors.WaitCursor;
                fullPath_File1 = TxtFileToDBUploadPath.Text;
                fullPath_File2 = TxtFileToDBDownloadPath.Text;
                DBFileNameKey = Path.GetFileName(fullPath_File1);
 
                DBFileUpload(fullPath_File1);
 
                DBFileDownload(DBFileNameKey, fullPath_File2);
 
                Cursor.Current = Cursors.Default;
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;
                errorMessage = string.Format("Error: {0}\r\n", ex.Message);
                RcTxt_Log.Text += errorMessage;
                MessageBox.Show(errorMessage, "Upload and Download DB in the middle"
                                , MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        public static void DBFileUpload(string fullFilePathToUp)
        {
            string fileName = Path.GetFileName(fullFilePathToUp);
            byte[] file;
            using (var stream = new FileStream(fullFilePathToUp, FileMode.Open, FileAccess.Read))
            {
                using (var reader = new BinaryReader(stream))
                {
                    file = reader.ReadBytes((int)stream.Length);
                }
            }
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (var sqlWrite = new SqlCommand(
"INSERT INTO FileToTransferLocally " +
"(FileByteStream" +
", FileName) " +
"Values" +
"(@FileByteStream" +
", @FileName)", cn))
                    {
                        sqlWrite.Parameters.Add("@FileByteStream", SqlDbType.VarBinary, file.Length).Value = file;
                        sqlWrite.Parameters.Add("@FileName", SqlDbType.VarChar, fileName.Length).Value = fileName;
                        sqlWrite.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    if (cn != null
                        && cn.State == ConnectionState.Open)
                    {
                        cn.Close();
                    }
                }
            }
        }
 
        public static void DBFileDownload(string DBFileNameKey, string fullFilePathToDown)
        {
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (var sqlQuery = new SqlCommand(
@"SELECT [FileByteStream] 
FROM [dbo].[FileToTransferLocally] 
WHERE [FileName] = @FileName", cn))
                    {
                        sqlQuery.Parameters.AddWithValue("@FileName", DBFileNameKey);
                        using (var sqlQueryResult = sqlQuery.ExecuteReader())
                            if (sqlQueryResult != null)
                            {
                                sqlQueryResult.Read();
                                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                                using (var fs = new FileStream(fullFilePathToDown, FileMode.Create, FileAccess.Write))
                                    fs.Write(blob, 0, blob.Length);
                            }
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    if (cn != null
                        && cn.State == ConnectionState.Open)
                    {
                        cn.Close();
                    }
                }
            }
        }
    }
}

SQL Server Engine

Porta di default: 1433.
Consiglio: cambiare il numero della porta per motivi di sicurezza non serve peché il protocollo di trasporto TCP prevede il port scanning per cui qualsiasi sia la porta verrà intercettata.

Articolo! Meccanismo di comunicazione di SQL Server

Riavviare il servizio

Per più informazioni per la versione 2014: da technet.microsoft.
In pratica si stoppa e poi si riavvia con due comandi DOS differenti.
Aprire una finestra DOS e digitare come segue:

> net stop MSSQLSERVER
> net start MSSQLSERVER

Per riavviare SQL Server EXPRESS (usare i doppi apici):

> net start "SQL Server (SQLEXPRESS)"

Oppure

Servizio SQL Server.png

Oppure

SQL Server stop and restart.png

Istanze di SQL server

E' possibile avere fino a 50 istanze contemporanee di SQL Server sulla stessa macchina.

Instance stacking

Articolo generale: docs.microsoft.com

Riporto alcune info ottenute da qs sito brentozar.com
Questa soluzione corrisponde all'Instance stacking che è una tecnica di installaizonedi istanze multiple di SQL Server sulla stessa istanza Windows. Per esempio, si può avere una VM o server chiamato SQLPROD1, ed avere:

  • SQLPROD1 – default instance of SQL Server, say SQL 2016
  • SQLPROD1\SQL2014 – for our older apps
  • SQLPROD1\SharePoint – because it’s supposed to be on its own instance
  • SQLPROD1\development – for our QA & testing


The Benefits of Instance Stacking

  • Lower SQL licensing costs – you only have to pay for one license, and then even Standard Edition lets you install dozens of instances on the same Windows base.
  • Lower Windows licensing costs – you only have to pay for one Windows.
  • Easier Windows patching – since you only have to patch one OS install.


Controindicazioni
Much harder performance tuning – all of the instances share the same CPU, memory, network pipe, and storage pipe. While SQL Server does offer tricks like affinity masking and memory settings to alleviate the first two, it’s got no answers for the second two. A backup on one instance will knock out performance on the other instances regardless of how much tuning work you put in. If none of the instances are performance-sensitive, this doesn’t matter – but how often does that happen? And how do you figure out what the “right” memory or CPU settings are? It takes so much human work and experimentation that it really only makes sense when you have plenty of free time per DBA per server.

Much harder reboot planning – you have to get all of the customers on all of the instances to agree on a time to patch Windows.

Security challenges – sometimes, we get those awful folks who insist on being able to RDP into the Windows instance that hosts their databases. If they insist on being sysadmin on the box altogether, then they can make changes that wreak havoc on the other running instances.

Aperture

Affinché possa essere raggiungibile il DB Server al di là della stessa macchina dove è installato occorrono delle aperture si di Firewall che di abilitazioni a protocolli.
Un test è la risposta positiva del seguente comando dando per scontato che l'istanza del DB sia sulla porta standard 1433:

> telnet DBHostNameInstance 1433

Seguono gli step da compiere nel caso il telnet non funzioni

SQL Server Enable to be find.png

Migrazione

E' possibile portare una DB su un server aggiornato e farlo funzionare in modalità di compatibilità, ad esempio si può fare il backup di un DB da un Server con SQL Server 2008 RS2 e restorarlo su SQL Server 2017, in automatico sarà impostato il funzionamento in compatibilità con "SQL Server 2008 (100)".
Quanto spiegato NON significa che il DB è stato migrato e non è esente da problemi ma è considerato come un primo passo, un passaggio precauzionale prima della conversione vera e propria, in merito leggere l'articolo: spiria.com.
Non tutte le versioni si possono "trasportare" su un server più aggiornato infatti per ogni vecchia versione è associato un livello di compatibilità. Riporto la seguente tabella presa dal link Microsoft docs.microsoft.com:

Product Database Engine Version Default Compatibility Level Designation Supported Compatibility Level Values
SQL Server 2019 (15.x) 15 150 150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x) 14 140 140, 130, 120, 110, 100
Azure SQL Database single database/elastic pool 12 150 150, 140, 130, 120, 110, 100
Azure SQL Database managed instance 12 150 150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x) 13 130 130, 120, 110, 100
SQL Server 2014 (12.x) 12 120 120, 110, 100
SQL Server 2012 (11.x) 11 110 110, 100, 90
SQL Server 2008 R2 10.5 100 100, 90, 80
SQL Server 2008 10 100 100, 90, 80
SQL Server 2005 (9.x) 9 90 90, 80
SQL Server 2000 (8.x) 8 80 80

Per eseguire l'aggiornamento vero e proprio si può invece usare un tool Microsoft di migrazione che ha anche la funzione di effettuare prima un "assessment" (una valutazione) prima della migrazione vera e propria. Questo tool si chiama "Microsoft Data Migration Assistant" ed è scaricabile al seguente link: (DMA).

Ridurre il file di LOG

Nozioni: ugiss.org
Ho scelto di passare alla modello "SIMPLE" e stroncare la dimensione del file di log a 20 Mbyte, da una nuova query digitare in sequenza:

ALTER DATABASE [RS_SRC_XXX] SET RECOVERY SIMPLE
DBCC SHRINKFILE (RS_SRC_XXX_Log, 20);

Col seguente effetto

Shrink DB Log file.png

Dopo la cura, ridotto a 20 Mbyte

Shrink DB Log file dopo.png

Funzionalità specifiche

Esecuzione comandi DOS

In altri termini la SP xp_c mdshell esegue un comando shell Windows l'output è restituito come righe ti testo. Doc qui

xp_c mdshell { 'command_string' } [ , no_output ]  

NOTE

  • Di default per ragioni di sicurezza l'esecuzione di questa stored procedure è disabilitata, segue script di riabilitazione.
  • Il parametro di input, "command_string", DEVE essere un nvarchar\varchar ma non si può assegnare MAX ovvero occorre definire una dimensione numerica nei limiti accettati ovvero 8000 per varchar e 4000 per nvarchar

Abilitazione

Di default non verrà eseguito senza che la si sblocchi come segue

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_c mdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Esempio

Il seguente esegue una copia di file da un percorso ad un'altro, si controlla anche l'esito dell'esecuzione dello script.

DECLARE @RootPath			AS varchar(2000) = 'F:\Backup\DB\RS Bkp\Tests',
		@ScrRelativePath	AS varchar(2000) = '\WS_B',
		@DestRelativePath	AS varchar(2000) = '\WS_CopyResult',
		@DosCmd				AS varchar(2000) = 'xcopy',						--'Dir',
		@DosCmdParams		AS varchar(2000) = '/S /E /F /Y',
		@DosCmdParamsToFile	AS varchar(2000),
		-- Non usare varchar(max) --v	!!!
		@CmdShellScript		AS varchar(4000) = '',
		@result				int
 
SET @DosCmdParamsToFile	= '> "' + @RootPath + '\CopyResultOut.txt"'
SET @CmdShellScript = @DosCmd 
					+ ' "' + @RootPath + @ScrRelativePath + '" ' 
					+ ' "' + @RootPath + @DestRelativePath + '" ' 
					+ @DosCmdParams
					+ ' ' + @DosCmdParamsToFile
PRINT @CmdShellScript
 
EXEC @result = master..xp_c mdshell @CmdShellScript --!!!
 
IF (@result = 0)  
   PRINT 'Script Success'  
ELSE  
   PRINT 'Script Failure';

NOTA nel caso in cui si volesse catturare l'output dell'esecuzione dello script si può usare '>' nel comando DOS per ridirezionare l'output ad es su di un file. Vedi seguente esempio:

DECLARE @cmd sysname, 
        @var sysname;  
SET @var = 'dir/p';  
SET @cmd = @var + ' > dir_out.txt'; 
 
EXEC master..xp_c mdshell @cmd;

Criptazione

Esistono funzioni di Criptaggio, le accenno ma son da approfondire:

open symmetric key sk_customers decryption by password = ''
 
SELECT
	CONVERT(nvarchar(100), DECRYPTBYKEY([FIRSTNAME])) AS [FIRSTNAME]
FROM		CNV_CU_B_ADDRESS_BOOK
 
close symmetric key sk_customers

Export su Excel file

Un buon post sull'argomento è reperibile qui: sqlteam.com
Il risultato atteso può essere ottenuto anche da Management Studio ma siamo interessati da query T-SQL. Qui è affrontato l'argomento da cui ho estratto qualche utile soluzione: [1]

Esportazione dati in Excel esistente

Con driver OLEDB

La seguente presuppone che il file Excel "Estrazione.xls" (compatibile con Excel 97-2003) esista già ed abbia come primo rigo esattamente l'intestazione con colonne come sono estratte dalla query SQL di sotto.

  • NOTA 1: il foglio su cui si andrà a scrivere si chiami esattamente "DatiEstratti".
  • NOTA 2: ad ogni esecuzione della seguente query i dati saranno aggiunti in coda a quanto già contenuto nel file.
  • NOTA 3: 'Microsoft.Jet.OLEDB.4.0' è un provider di dati basato su driver 32 bit e funziona solo su file con estensione .xls, Excel 8.0. Nel caso si volesse usare una connessione a file excel più recenti occorre usare provider basati su driver a 64 bit ovvero 'Microsoft.ACE.OLEDB.12.0', Excel 12.0 (il codice di versine non conta realmente).
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\Estrazione.xls;', 
    'SELECT * FROM [DatiEstratti$]') SELECT * FROM miaTabella

Si può anche indicare se il foglio Excel ha al primo rigo l'intestazione o meno, nel seguente esempio l'excel ha l'intestazione:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;HDR=YES;Database=C:\Estrazione.xls;', 
    'SELECT * FROM [DatiEstratti$]') SELECT * FROM miaTabella
Errori e soluzioni
Ad Hoc Distributed Queries
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' 
because this component is turned off as part of the security configuration for this server.

Si risolve cambiando una impostazione di sicurezza che per default è disabilitata, si esegue una SP di sistema che funziona in 2 step, il primo imposta il valore da cambiare in configurazione e nel secondo col comando reconfigure lo si rende effettivo.
Prima impostazione per abilitare le impostazioni avanzate:

sp_configure 'show advanced options',1  
reconfigure

Infine si cambia il parametro per il nostro scopo:

sp_configure 'Ad Hoc Distributed Queries',1  --Che produce in output: Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.
reconfigure

OK, fatto, per vedere i cambiamenti apportati ed anche tutto il resto:

sp_configure
OLE DB provider for linked server null
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either 
the object has no columns or the current user does not have permissions on that object.
????

SP sp_makewebtask

Si cita come esempio ma questa SP è DEPRECATA!!!

EXEC sp_makewebtask 
    @outputfile = 'C:\Estrazione.xls', 
    @query = 'SELECT * FROM mioDB..miaTabella', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Dettagli prova'

Elaborazioni su file Excel esistenti

Aggiornamento di una cella

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99

Dove 'b7' è la cella del foglio 'Sheet1' (b7:b7 è la sintassi corretta), la locazione di colonna 'f1' (e parametro 'hdr=no') occorre lasciarli tali.

Aggiunta di una formula

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Prova.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') SET f1 = '=a7+c7'

Dove 'b7' è la cella del foglio 'Sheet1' (b7:b7 è la sintassi corretta), la locazione di colonna 'f1' (e parametro 'hdr=no') occorre lasciarli tali.

Esportazione su nuovo file Excel

Nella seguente soluzione si creerà un file ASCII con estensione XLS ma di comodo perchè in realtà resta un CSV.
Si fa uso dell'applicazione BCP.exe (link interno [2]) che si trova generalmente in un percorso simile:

C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

La seguente Stored Procedure crea un file csv con intestazione sul FileSystem:

/*
	Crea un file Excel da una tabella.
	Usa un file dummy per generare il fiel Excel
 
	Es.:
		EXEC SP_ExportTo_excel 'your dbname', 'your table name','your file path'
*/
CREATE PROCEDURE SP_ExportTo_excel
(
    @db_name		AS varchar(100),
    @table_name		AS varchar(100),   
    @file_name		AS varchar(100)
)
AS
BEGIN 	
	--Genera l'intestazione di colonna prendendole dai nomi campi della tabella
	DECLARE @columns	AS varchar(8000), 
			@QrySQL		AS varchar(8000), 
			@data_file	AS varchar(100)
 
	SET @QrySQL = 'USE ' + @db_name + ';'
	EXEC (@QrySQL)
 
	SELECT 
		@columns = COALESCE(@columns+',','')+column_name+' as ' + column_name 
	FROM information_schema.columns
	WHERE table_name = @table_name
		AND TABLE_CATALOG = @db_name
 
	SELECT @columns=''''''+REPLACE(REPLACE(@columns,' as ',''''' as '),',',',''''')
 
	--Create a dummy file to have actual data
	SELECT @data_file = SUBSTRING(@file_name,1, LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)))
			+'\data_file.xls'
 
	--Generate column names in the passed EXCEL file
	SET @QrySQL='EXEC master..xp_c mdshell ''bcp " SELECT * FROM (SELECT '+@columns+') as t" queryout "'+@file_name+'" -c -U "sa" -P "sa" '''
	--Print @QrySQL
	EXEC (@QrySQL)
 
	--Generate data in the dummy file
	SET @QrySQL='EXEC master..xp_c mdshell ''bcp "SELECT * FROM '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -U "sa" -P "sa" '''
	--Print @QrySQL
	EXEC (@QrySQL)
 
	--Copy dummy file to passed EXCEL file
	SET @QrySQL= 'EXEC master..xp_c mdshell ''type '+@data_file+' >> "'+@file_name+'"'''
	--Print @sql
	EXEC (@QrySQL)
 
	--Delete dummy file 
	SET @QrySQL= 'EXEC master..xp_c mdshell ''del '+@data_file+''''
	EXEC (@QrySQL)
END

Dopo aver creato la StoredProcedure eseguire il seguente comando fornendo nome del DB, tabella e path:

EXEC SP_ExportTo_excel 'your dbname', 'your table name','your file path'

Job Schedulato su SSIS Package

Dato un database si esegue prima un export verso foglio Excel, si salva la procedura come SSIS package su fileSystem. Infine ottenuto il file DTSX (del pacchetto SSIS) si schedula un Job specifico che produca il foglio Excel.
P.S. Non funziona la creazione di una query custom per l'estrazione dei dati il problema è eludibile con una vista quindi sarà quest'ultimo oggetto ad esser esportato su file Excel.

Job export to Excel 01.png
Job export to Excel 02.png
Job export to Excel 03.png
Job export to Excel 04.png
Job export to Excel 05.png

Salvataggio su File system del pacchetto SSIS, il file *.dtsx è un file XML, facilmente editabile.

Job export to Excel 06.png

Creazione del Job per eseguire il pacchetto SSIS.

Job export to Excel 07.png

Selezionare le due connessioni alla fonte dat, SQL server e alla destinazione, foglio Excel.

Job export to Excel 08.png

SSIS Package

Vedi guida in codesta guida interna: SSIS packege export in Excel

Gestione Allarmi

SNMP

Il protocollo di comunicazione SNMP è un protocollo per il monitoraggio delle risorse e dispositivi di rete. L'SNMP raccoglie informazioni da dispositivi IP o Sistemi in collaborazione con un agente SNMP.
Le informazioni raccolte ed elaborate, basate da uno specifico evento o alert sono SNMP traps che possono essere inviate, ricevute e analizzate.
Un importante elemento del SNMP è il software di SNMP management software attraverso il quale l'utente può determinare cosa esattamente è accaduto quando è stato inviato un SNMP trap.

Configurazione

Idealmente si avrà il supporto al protocollo SNMP già installato su Windows ancor prima di installare SQL Server.
Basta la presenza del seguente servizio:

Servizio SNMP di Windows.png

così configurato affinché possa essere interrogato dalle applicazioni con IDE

Servizio SNMP di Windows cfg.png

Andare sul tab Agent

Servizio SNMP di Windows cfg 02.png

The Applications, and especially End-to-end options are the most important to check, because the Applications option allows sending the SNMP traps from the software solutions, in collaboration with SNMP service, and End-to-end option sets the local machine to act like an IP host and listener (localhost), which will be used in setting the SNMP trap nel software di monitoraggio delle trap SNMP.

Select the Traps tab and input the default community name string, public (it is case-sensitive), and then click on the Add to list button:

Servizio SNMP di Windows cfg 03.png

If the Add to list button remains disabled, the string is added. When input, a community name string identifies the IP host, and validates the SNMP trap request from its source (software di monitoraggio).

In the Security tab, select the option Accept SNMP packets from any host:

Servizio SNMP di Windows cfg 04.png

Descrizione componenti

Il setup di MS SQL copierà i files necessari: MSSQL.MIB e SQLSNMP.DLL. Se l'SNMP non è installato prima di SQL Server lo si potrà fare successivamente. I files del SNMP per SQL server saranno stati copiati nella cartella ~MSSQL\BINN e quel di cui avrai bisogno è avviare il Server network utility e selezionare "Enable SNMP" check box.
L'architettura SNMP può essere suddivisa in due parti:

  • La macchina di management SNMP;
  • L'agente SNMP (il sistema da monitorare, che in questo caso è SQL Server)

L'agente SNMP risponderà alle richieste di informazione da parte della macchina di management, ma può anche notificare delle trap quando certi eventi critici accadono. Under Windows, the data being accessed by SNMP is held in the Registry, and the agent service makes this information available in a form readable by SNMP monitors. The events that elicit SNMP traps are not defined in an ad hoc way by users, but rather through a Management Information Base (MIB). The MIB is a data file that has all the details about the objects available to be managed via SNMP. Different services provide different MIBs; for example, Internet Information Server (IIS) has an INETSRV.MIB file that is implemented using the IIS.DLL file.

Security for SNMP is implemented using a shared password (the community name) so that only systems with the correct name can manage associated agents. To further increase the security of your SNMP installation, you can configure an SNMP packet filter so that control packets are accepted only from certain host systems.

When the SNMP service is started on the SQL Server machine, the SQLSNMP.DLL file is loaded. This process contains a trap mechanism for raising SQL Server alerts and is what coordinates SQL Server with the NT-based SNMP service.

On the Windows NT Server Resource Kit CD-ROM, you will find a number of SNMP utilities, one of which will allow you to carry out simple management tasks (snmputil). Using other utilities, you can compile new MIBs to allow monitoring of other performance counters.

snmputil allows you to easily test your installation, but you will need a full management tool for use in your production environment, such as HP OpenView, Unicenter TNG, SunNet Manager, Advent, or Intraspection. Each of these provides a different variety of graphical tools, add-ons, and foundation objects for developing tools in Visual Basic, Java, and many other development languages.

Varie

EXEC msdb.dbo.sp_add_alert  
     @name = N'Test Alert',  
     @message_id = 55001,   
     @severity = 0,   
     @notification_message = N'Error 55001 has occurred. The database will be backed up...',   
     @job_name = N'Back up the AdventureWorks2012 Database' ;

Alta affidabilità

L'HA è una caratteristica di un sistema che mira a garantire un livello concordato di prestazioni operative, solitamente uptime (tempo di funzionamento), per un periodo superiore al normale.
(uptime = è una misura dell'affidabilità del sistema, espressa come percentuale di tempo in cui una macchina o servizio, è stata disponibile e correttamente funzionante).

Info

Il DB mirroring è la creazione una copia byte per byte di un database in una posizione diversa. Il mirroring è diverso dalla copia o dal backup in quanto un DB mirror viene aggiornato contemporaneamente al database originale (in modo sincrono) o il più rapidamente possibile dopo l'aggiornamento del database originale (asincrono). La funzionalità di "Mirroring" è ormai obsoleto e superato con l'avvento dell'"Always on".
Altri concetti utili:

Tool:

Invio Email

Primo step è la configurazione del server con i parametri di invio email, definendo un server SMTP da utilizzare. (Fonte sqlshack.com)

MSSQLSrv Invio email 01.png

A questo punto si apre popup con radio button che guidano verso operazioni di prima configurazione o modifica di quella esistente.

MSSQLSrv Invio email 02.png

Segue una configurazione operativa

MSSQLSrv Invio email 03.png

Script per inviare email su un profilo "SMTP_MyProviderProfile" già configurato:

DECLARE @c_CR		AS nvarchar(10) = CHAR(13),
	@bodyCustom	AS nvarchar(max) = '',
	@subjectCustom	AS nvarchar(max) = ''
 
SET @subjectCustom = 'Job KO - '
		+ CONVERT(varchar, GETDATE(), 120)
SET @bodyCustom = 'Job or some foundamental step was failed! See Log file for detail (fromvirtual machine).' + @c_CR
		+ 'Log path:' + @c_CR
		+ 'D:\Job_Log\MyJob'
 
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'SMTP_MyProviderProfile',
	@recipients = 'bubu@xxx.it;,
	@body = @bodyCustom,
	@subject = @subjectCustom;

Invio di files

Per inviare più di un file

Declare @filenames varchar(max)
Set @filenames = 'C:\Testfiles\Test1.csv;C:\Testfiles\Test2.csv'
 
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email', 
  @body = @body1,
  @file_attachments = @filenames;

Per inviare l'intero contenuto di una cartella (da stackoverflow), stored procedure:

ALTER PROCEDURE [dbo].[GetFullPathFilesList]
	@DirectoryPath	AS nvarchar(500) = N'D:\Tmp',
	@Out_CSVFileList	AS nvarchar(500) OUTPUT
AS
BEGIN
	SET @Out_CSVFileList = ''
	DECLARE @FileList TABLE
		(
			[FileName] NVARCHAR(500)
		  , [depth] INT
		  , [file] INT
		); 
/* Using xp_DirTree:
		Parameters:
			directory - This is the directory you pass when you call the stored procedure; for example 'D:\Backup'.
			depth  - This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
			isfile - This will either display files as well as each folder.  The default of 0 will not display any files.
*/
	--This gets a list of ALL files in your directory
	INSERT INTO @FileList (  [FileName]
							, [depth]
							, [file]    )
	EXEC [master].[sys].[xp_dirtree] @DirectoryPath, 1, 1;
	--Add this code inside your cursor to filter on only those files that contain the @PatientId and build out the string of files to attach.
	SELECT @Out_CSVFileList = @Out_CSVFileList + @DirectoryPath + '\' + [FileName] + ';'
	FROM   @FileList
	WHERE [file] = 1 	
	--Print 'In uscita: ' + isnull(@Out_CSVFileList, 'null')
        SET @Out_CSVFileList = SUBSTRING(@Out_CSVFileList, 1, LEN(@Out_CSVFileList)-1)
END

Esecuzione chiamata a Web API

Fonti:

PREMESSA, occorre abilitare l'OLE automation procedure disabilitato per default.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Stored Procedure coinvolte:

GET

Via HTTP se si deve chiamare il seguente:

http://localhost/WebAPISrv_455_Simulator/ivr_donate?msisdn=393478165901&amount_id=5&short_number=45510&user_check_id=don
DECLARE @URL NVARCHAR(MAX) = 'http://localhost/WebAPISrv_455_Simulator/ivr_donate?msisdn=393478165901&amount_id=5&short_number=45510&user_check_id=don';
 
Declare @Object as Int,
		@ResponseText as Varchar(8000);
 
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod 
       @Object, 
       'open', 
       NULL, 
       'get',
       @URL,
       'False'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF ((Select @ResponseText) <> '')
BEGIN
	PRINT '@ResponseText = 
' + @ResponseText
	Select @ResponseText
 
     --DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
     --SELECT *
     --FROM OPENJSON(@json)
     --     WITH (
     --            EmployeeName NVARCHAR(30) '$.employeeName',
     --            Title NVARCHAR(50) '$.title',
     --            BirthDate NVARCHAR(50) '$.birthDate',
     --            HireDate NVARCHAR(50) '$.hireDate',
     --            Address NVARCHAR(50) '$.address',
     --            City NVARCHAR(50) '$.city',
     --            Region NVARCHAR(50) '$.region',
     --            PostalCode NVARCHAR(50) '$.postalCode',
     --            Country NVARCHAR(50) '$.country',
     --            HomePhone NVARCHAR(50) '$.homePhone'
     --          );
END
ELSE
BEGIN
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
     Print @ErroMsg;
END
Exec sp_OADestroy @Object

Come si può intuire nella parte commentata nel caso l'output sia un JSON è possibile deserializzarlo.

POST

Il seguente è copiato "a man bassa"

DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/employees/updateemployee';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
   "employeeId": 1,
   "firstName": "Nancy",
   "lastName": "Davolio",
   "title": "Sales Representative",
   "birthDate": "2020-08-18T00:00:00.000",
   "hireDate": "2020-08-18T00:00:00.000",
   "address": "507 - 20th Ave. E. Apt. 2A",
   "city": "Seattle",
   "region": "WA",
   "postalCode": "98122",
   "country": "USA",
   "homePhone": "(206) 555-9857"
}'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post',
                 @URL,
                 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF CHARINDEX('false',(SELECT @ResponseText)) > 0
BEGIN
 SELECT @ResponseText As 'Message'
END
ELSE
BEGIN
 SELECT @ResponseText As 'Employee Details'
END
EXEC sp_OADestroy @Object

Procedure

Backup

SQL Server BackupTypes.png

Differenziale

Si riferiscono all'ultimo Backup full (completo).
Saranno backappate le "Extends" che son cambiate dall'ultimo full backup. Un extent è fatto da otto pagine da 8KB, così un extent è di 64KB di data. Ogni volta che un dato è stato cambiato un flag viene cambiato per consentire a SQL Server di stabile che un "Differential" backup creato deve includere dati da extent. Quando un "Full" backup è creato questo flag è posto ad off.

Così se prima si fa un full backup e poi un differenziale questo conterrà le ultime modifiche rispetto al full. Se nel frattempo intervengono nuove modifiche e si fa un ulteriore backup differenziale questo conterrà sempre gli ultimi cambiamenti rispetto all'ultimo full backup.
In altri termini i backup differenziali conterranno sempre tutte le modifiche contenendo i dati dei backup differenziali precedenti. Un Restore si fa con l'ultimo full backup a cui si aggiunge il backup differenziale più recente. Tutti i backup differenziali precedenti si posson ignorare.

Ripristino dall'IDE

SQL Server Bakup Differential 01.png
SQL Server Bakup Differential 02.png

Job

Definizione

Sono azioni che possono essere eseguite in base a schedulazioni, tali azioni posson esser ad esempio: esecuzioni di query, esecuzione di StoredProcedure, etc.
Possono essere eseguiti in base ad una schedulazione oppure direttamente, in quest'ultimo caso posson esser eseguiti manualmente dall'IDE di SQL Management studio o mediante l'uso di una StoredProcedure di sistela la sp_start_job che risiede sul DB "msdb".

sp_start_job 
  [@job_name] or [@job_id ]
  [,@error_flag ] 
  [,@server_name] 
  [,@step_name ] 
  [,@output_flag 

Arguments:

[@job_name] | [@job_id ] Is the name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.
[@error_flag =] error_flag Reserved.
[@server_name =] 'server_name' Is the target server on which to start the job. server_name is nvarchar(30), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.
[@step_name =] 'step_name' Is the name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL
[@output_flag =] output_flag Reserved.

When a job run it will have one of two return codes:

  • 0 (success)
  • 1 (failure)

Job con interazioni vs eseguibili

SQL Jobs Cmd Execute.png

Nel caso si debba eseguire un Job in cui si deve lanciare un eseguibile, una applicazione console occorre:

  • abilitare il Server all'esecuzione di "command shell".
  • assegnare le Grant necessarie al motore dei Jobs di SQL Server per lavorare con la cartella del File System dove è collocato l'eseguibile.

Log

E' possibile creare un file di LOG con la data nel nome del file, questo usando dei token prestabiliti.
Ecco cosa specificare nel nome del file nella apposita opzione

file_name_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).Estensione
SSMS JobLog fileNameWithDateTime.png

Report job e step schedulati

Fonte: mssqltips
SELECT
     J.[name]
    ,J.[enabled]
    ,J.[description]
FROM		msdb.dbo.sysjobs	AS J
WHERE 1=1
	AND J.[enabled] = 1

Rapporto più complesso

SELECT 
	J.[enabled] AS JobEnabled
	,J.[name] AS JobName
	,J.[description] AS JobDescription
    --,JSP.[step_id]
    ,JSP.[step_name]
	--,JSP.[subsystem], JSP.[command],JSP.[flags],JSP.[additional_parameters]
    --,JSP.[cmdexec_success_code]
    --,JSP.[on_success_action],JSP.[on_success_step_id],JSP.[on_fail_action]
    --,JSP.[on_fail_step_id],JSP.[server]
    ,JSP.[database_name]
    --,JSP.[database_user_name],JSP.[os_run_priority]
    --,JSP.[output_file_name]
    --,JSP.[last_run_outcome]
    ,JSP.[last_run_duration]
    ,JSP.[last_run_date],JSP.[last_run_time]
    ,JS.[next_run_date]	AS JobNextRunDT
    ,JS.[next_run_time] AS JobNextRunTM
	,S.[enabled] AS ScheduleEnabled
	,S.[name] AS ScheduleName 
	--,S.[freq_type], S.[freq_interval], S.[freq_subday_type], S.[freq_subday_interval], S.[freq_recurrence_factor]
FROM		[msdb].[dbo].[sysjobs]			AS J
LEFT JOIN	[msdb].[dbo].[sysjobschedules]	AS JS	ON	J.[job_id]		 = JS.[job_id]
LEFT JOIN	[msdb].[dbo].[sysschedules]		AS S	ON	JS.[schedule_id] = S.[schedule_id]
LEFT JOIN	[msdb].[dbo].[sysjobsteps]		AS JSP	ON	J.[job_id]		 = JSP.[job_id]
WHERE 1=1
ORDER BY J.[enabled] DESC, J.[job_id], JSP.step_id

Etc

SELECT
	 J.name job_name
	,J.enabled job_enabled
	,S.name schedule_name
	,S.freq_recurrence_factor
	,CASE WHEN freq_type = 4 THEN 
		'Daily'
	 END frequency
	,'every ' + CAST(freq_interval as varchar(3)) + ' day(s)'  [Days]
	,CASE 
		WHEN freq_subday_type = 2 THEN 
			' every ' + cast(freq_subday_interval as varchar(7)) 
			 + ' seconds' + ' starting at '
			 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
		WHEN freq_subday_type = 4 THEN 
			' every ' + cast(freq_subday_interval as varchar(7)) 
			 + ' minutes' + ' starting at '
			 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
		WHEN freq_subday_type = 8 THEN 
			' every ' + cast(freq_subday_interval as varchar(7)) 
			 + ' hours'   + ' starting at '
			 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
		ELSE 
			' starting at ' 
			+stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
	END AS [time]
FROM		msdb.dbo.sysjobs			AS J
INNER JOIN	msdb.dbo.sysjobschedules	AS JS	ON J.job_id = JS.job_id
INNER JOIN	msdb.dbo.sysschedules		AS S	ON JS.schedule_id = S.schedule_id
WHERE freq_type = 4
 
UNION
 
-- jobs with a weekly schedule
SELECT
	 sysjobs.name job_name
	,sysjobs.enabled job_enabled
	,sysschedules.name schedule_name
	,sysschedules.freq_recurrence_factor
	,case
	 when freq_type = 8 then 'Weekly'
	end frequency
	,
	replace
	(
	 CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
	+CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
	+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
	+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
	+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
	+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
	+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
	,', '
	,''
	) Days
	,
	case
	 when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7)) 
	 + ' seconds' + ' starting at '
	 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 
	 when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7)) 
	 + ' minutes' + ' starting at '
	 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
	 when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7)) 
	 + ' hours'   + ' starting at '
	 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
	 else ' starting at ' 
	 + stuff(stuff(RIGHT(replicate('0', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
	end time
FROM		msdb.dbo.sysjobs
INNER JOIN	msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
INNER JOIN	msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 8
ORDER BY job_enabled DESC

Errori e soluzioni

Login failed, error 18456

Uno step di un Job eseguendo una stored che lavora in linked server può capitare di avere il seguente errore:

Message
Executed as user: NT SERVICE\SQLAgent$__istanza__
Login failed for user '__dominio\macchina__
La stessa store eseguita da Management Studio non da alcun errore ma eseguendo il 
<br />
Una soluzione è fare precedere l'esecuzione della store dalla seguente istruzione che farà eseguire la store con le credenziali di un utenza abilitata. Sarà eseguita con credenziale adatta al linked server e crea o aggiorna una mappatura tra il login dell'istanza locale di SQL Server e l'account che eseguirà in sicurezza la SP sul server remoto.
<syntaxhighlight lang="tsql">
 EXEC sp_addlinkedsrvlogin 'macchina\dominio,nrPorta', 'false', NULL, 'utenteAbilitato', 'password'
</syntaxhighlight>
Per saperne di più [https://docs.microsoft.com/it-it/sql/relational-databases/system-stored-procedures/sp-addlinkedsrvlogin-transact-sql?view=sql-server-ver15 doc]<br />
Quella appena vista è una soluzione ad hoc ma se si volesse una soluzione generale e "definitiva" (ma per ogni Job):
# Run "SQL Server Configuration Manager"
# Select "SQL Server Services"
# Right Click "SQL Server Agent" and choose properties
# On the "log on " tab ,choose "this account".
# Click "Browse" button , then Click "Advanced" button.
# Click "find now" button . Now find your account in the below table and select it.
# When you selected proper account (With sufficient permission) , return to "log on" tab and enter your password
# Restart service and try again.
.
(Error 18456)

La stessa store eseguita da Management Studio non da alcun errore ma eseguendo il
Una soluzione è fare precedere l'esecuzione della store dalla seguente istruzione che farà eseguire la store con le credenziali di un utenza abilitata. Sarà eseguita con credenziale adatta al linked server e crea o aggiorna una mappatura tra il login dell'istanza locale di SQL Server e l'account che eseguirà in sicurezza la SP sul server remoto.

 EXEC sp_addlinkedsrvlogin 'macchina\dominio,nrPorta', 'false', NULL, 'utenteAbilitato', 'password'

Per saperne di più doc
Quella appena vista è una soluzione ad hoc ma se si volesse una soluzione generale e "definitiva" (ma per ogni Job):

  1. Run "SQL Server Configuration Manager"
  2. Select "SQL Server Services"
  3. Right Click "SQL Server Agent" and choose properties
  4. On the "log on " tab ,choose "this account".
  5. Click "Browse" button , then Click "Advanced" button.
  6. Click "find now" button . Now find your account in the below table and select it.
  7. When you selected proper account (With sufficient permission) , return to "log on" tab and enter your password
  8. Restart service and try again.

Installazione

Su Linux Red Hat 8.3

Vadi: SQL Server su Linux

Varie

Occupazione di una tabella

sp_spaceused 'Nome della tabella'

SERVERPROPERTY

Per avere informazioni specifiche del Server dove è in esecuzione il DB Server. Ad es. per avere il nome dell'Host . Doc: microsoft.com

Print 'Nome macchina: ' + CONVERT(nvarchar(max), SERVERPROPERTY('MachineName'))

Visibilità dei network path

Per raggiungere da SQL Server i path di rete di cartelle condivise, non solo occorrerà prima sharare tali cartelle direttamente sul server remoto (condividendole con gli utenti voluti ed in particolare con l'utente con il quale si raggiungeranno via SQL Server) ma occorrerà mapparle da SQL Server.
Leggere: mssqltips.com

  1. Sul server Remoto sharare le cartelle (shared folders) dei drive da raggiunvere via SQL;
  2. Sul Server del DB mappare le cartelle remote del punto precedente (ne basta una);
  3. Da SSMS (management Studio) lanciare la seguente prima query usando la SP di sistema XP_CM DSHELL (<--togliere lo spazio)
EXEC XP_CM DSHELL 'net use G: \\172.168.1.69\cartellaCondivisa passwordUtente /USER:Dominio_o_nomeMacchina\NomeUtente_SO'
 
--Se tutto è andato bene, il seguente funzionerà:
EXEC XP_CM DSHELL 'Dir G:'

NOTA il drive G:, stranamente, non sarà visibile da sistema operativo, non occorre mappare tutte le cartelle che si vuol raggiungere ormai sarà raggiungibile l'intera risorsa: "\\172.168.1.69"!!!
ATTENZIONE l'impostazione data dal comando net use NON E' DEFINITIVA ovvero al riavvio della macchina o al log off si perderà ma c'è il modo di renderla permanente, accertarsi prima che tutto sia OK.
Per un HELP del comando net use vedere qui

Relazioni tra Server

SQL Server Change Tracking

E' un sistema "leggero" che consente di tenere allineati,  due DB SQL Server.
Qui ulteriori informazioni: msdn.microsoft

Non tutte le tabelle sono interessate a questa sincornizzazionie ma solo quelle esplicitamente indicate infatti gli sviluppatori avranno la possibilità di implementare un meccanismo custom di tracciamento delle modifiche.

Applications can use change tracking to answer the following questions about the changes that have been made to a user table:

What rows have changed for a user table?

Only the fact that a row has changed is required, not how many times the row has changed or the values of any intermediate changes.

The latest data can be obtained directly from the table that is being tracked.

Has a row changed?

    • The fact that a row has changed and information about the change must be available and recorded at the time that the change was made in the same transaction.

DB bilanciati

Nell'uso di DB bilanciati nel momento in cui uno fallisce, per vari motivi, la risposta è attivata nell'l'altro che è automaticamente attivato per rispondere alla richiesta. I dati sono duplicati sugli n DB allineati come anche gli indici, trigger etc tranne che per linked server.
Es. per DB bilanciati stringa di connessione C#

Linked server

Creazione

SQL Server Linked 01.png

Mediante connessione ODBC

Guida interna ODBC
Segue esempio esempio realistico verso DB Caché di InterSystems. ATTENZIONE quanto segue vale per l'installazione del Driver a 32 bit, su SQL Server 2017 ha funzionato solo la configurazione del driver a 64 bit!
Sul Centralino Avaya Aura Contact Center, le chiamate sono registrate su database Caché a cui si accedrà via ODBC. Ci sono due driver forniti in dotazione come da seguente cartella:

Cache ODBC SSMS Linked srv 01.png

Installare dal file 'ODBCDriver_2007.1_x86.exe' che è relativo ai driver a 32 bit. L'installazione produrrà la seguente cartella:
C:\Program Files (x86)\Common Files\InterSystems\Cache contenente la seguente DLL: CacheODBC.dll
Purtroppo non è possibile procedere dal Pannello di controllo\Strumenti di amministrazione\Origini dati (ODBC) perchè non risulterebbe il driver appena installato.
NOTA

  • Il tool di configurazioen a 32bit si trova nella cartella:
%windir%\syswow64\odbcad32.exe
  • Il tool di configurazioen a 64bit si trova nella cartella:
%windir%\system32\odbcad32.exe

Eseguire con diritti di amministratore il seguente eseguibile:

Cache ODBC SSMS Linked srv 02.png

Aggiungere la nuova origine dei dati selezionando il driver "InterSystems ODBC"

Cache ODBC SSMS Linked srv 03.png

Configurandola come segue:

Cache ODBC SSMS Linked srv 04.png

Quindi aprire SSMS ed aggiungere il Linked Server come segue:

Cache ODBC SSMS Linked srv 05.png

Quindi configurarlo in 2 passi, il primo:

Cache ODBC SSMS Linked srv 06.png

ed il secondo:

Cache ODBC SSMS Linked srv 07.png

Ed a questo punto è possibile esplorare e lavorare sul DB linkato, questa è una vista sul DB:

Cache ODBC SSMS Linked srv 08.png

Script T-SQL

Alla fine quanto impostato nella GUI si traduce nel seguente script:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'AACC_DB', 
                       @srvproduct=N'AACC_Cache_Prod', @provider=N'MSDASQL', 
                       @datasrc=N'AACC_Cache_Prod'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AACC_DB',
                       @useself=N'False',@locallogin=NULL,
                       @rmtuser=N'########',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AACC_DB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Esecuzione remota di StoredProcedure

Da un Server SQL si vuole eseguire una SP che risiede su altro DB Server collegato in linked server. Supponendo il LinkedServer sia "[OM1N\IST_xxx,1444]" (si stà indicando non solo il nome del server quanto l'istanza e la porta) e la SP "DataProcessing_4cc_cps" a cui si passa anche un parametro di input:

DECLARE @RunStoredProcSQL	AS VARCHAR(1000),
	@MonthTopToSelect	AS int = (DATEPART(MONTH, GETDATE()) - 1)
SET @RunStoredProcSQL = 'EXEC [RS_SRC_Digitalk].[dbo].DataProcessing_4cc_cps ' + CAST(@MonthTopToSelect AS varchar);

--SELECT @RunStoredProcSQL --Debug
EXEC (@RunStoredProcSQL) AT [OM1N\IST_xxx,1444];
Print 'Procedure Executed';

Elenco tabelle

Per avere l'elenco degli oggetti contenuti nel DB puntato dal LinkedServer

EXEC sp_tables_ex @table_server = 'AACC_DB',  
				--@table_catalog = '???',   
				@table_schema = 'cbc',   
				@table_type = 'TABLE'; 

Output:

Cache ODBC SSMS Linked srv 09.png

Open query (OPENQUERY)

Link interno Comandi di Query
Esegue la query pass-through specificata nel server collegato specificato. Il server è un'origine dei dati OLE DB. È possibile fare riferimento alla funzione OPENQUERY nella clausola FROM di una query come se fosse un nome di tabella. È inoltre possibile fare riferimento alla funzione OPENQUERY come tabella di destinazione di un'istruzione INSERT, UPDATE o DELETE, a seconda delle capacità del provider OLE DB. Anche quando la query restituisce più set di risultati, la funzione OPENQUERY restituisce solo il primo set.
Sintassi:

OPENQUERY ( linked_server ,'query' )

linked_server
Identificatore che rappresenta il nome del server collegato.
query
Stringa della query eseguita nel server collegato. La lunghezza massima della stringa è pari a 8 KB. Attenzione la sintassi è la stessa del SQL del DB sorgente, quindi se ad es si è configurato un linked server ad un DB Oracle la sintassi da usare deve essere secondo PL-SQL e non T-SQL !!!

Linkedserver diretto

nell'es. che segue:

  • [LNESITCHEFTEST] è il link creato al server esterno
  • [ITCOWTEST] è il nome del database
  • itcr è lo schema
  • vwGetArticoli è una vista
SELECT *
FROM [LNESITCHEFTEST].[ITCOWTEST].itcr.vwGetArticoli

Casi particolari

Nel seguente caso, il server linkato a cui accedere NON E' sul DB Server da cui si accede, con l'istruzione EXECUTE si riuscirà ... non ho altri particolari per ora... ma è un caso reale

EXECUTE ('
select * from [NomeServerEsternoLinkato].Schema.NomeFunzioneRichiesta(''Parametro1'',''Parametro2'',''Parametro3'') 
order by codicearticolo, costoarticoloimpianto asc
') AT [LNESITCHEFTEST]

Elenco dei Linked Server

EXEC sp_linkedservers
-- Oppure:
SELECT sr.name		AS LinkedServerName 
      ,is_linked
      ,sr.provider	AS ProviderName
      ,sr.product	AS ProductName        
      ,sr.data_source AS SourceServerName
FROM sys.servers sr
WHERE is_linked = 1

Oracle

Panoramica OraOLEDB

Oracle Provider per OLE DB
Vedi: docs.oracle.com

Configurare il Linked Server

ToDo

etc backup

Dopo aver installato il driver per il collegamento, Linked Server, al DB Oracle un ruolo fondamentale ce l'ha il file di configurazione "tnsnames.ora" che conterrà le informazioni per la connessione, IP del DB, credenziali di accesso.

Seguire la guida: logicalread.com

Oracle SSMS LinkedServer Provider.png

OLEDB e ODBC Drivers

Per visualizzare l'elenco dei provider installati:

EXECUTE MASTER.dbo.xp_enum_oledb_providers

Esempio di output 1:

Driver xOffice 01.png

Esempio di output 2:

Driver xOffice 02.png

Oracle

  • Driver 32-bit Oracle Data Access Components (ODAC): [3]
  • Driver 64-bit Oracle Data Access Components (ODAC): [4]

MySQL

Driver ODBC

Esecuzione di script remoti

Quando si eseguono operazioni il cui effetto (Export o modifica di dati) è su un server remoto purtroppo non è possibile utilizzare agevolmente le TRANSAZIONI. Questo problema è risolvibile usando sul server remoto una stored procedure che utilizzi una transazione e localmente eseguendo dinamicamente uno script con l'opzione AT [NomeServerRemoto\NomeIstanza,porta].
Nel seguente esempio comporremo una variabile con uno script (una StoredProcedure di nome 'TestRemoteExec') e a cui passeremo anche un parametro e l'esecuzione sarà sul server remoto 'Cippalippa', istanza 'pippo' e porta 1340 (che si può omettere se fosse la porta di default)

/*=============================================
Author:		Giuseppe AINO
Create date:	28/10/2021
Description:	Test esecuzione StoredProcedure remota con simulazione 
				di errore e Rollback REMOTO!
Es.:
EXEC [ExecRemote_SP_Test] 'Errore indotto per verificare rollback', 'Prova'
===============================================*/
CREATE PROCEDURE [dbo].[ExecRemote_SP_Test]
	@GenError4Test_Msg	AS nvarchar(1000) = '',
	@Text			AS varchar(100)
AS
BEGIN
	SET NOCOUNT ON;   
 
	DECLARE @RunStoredProcSQL   AS NVARCHAR(2000),
		@DtStartDate	AS datetime = DATEADD(DAY, -7, CAST(GETDATE() AS datetime)),			
		@ReturnMsg_OUT	AS NVARCHAR(2000)
 
	EXEC ('EXEC [4Tests].[dbo].[LocalExec_FromRemote] @DtStartDate=?, @GenError4Test_Msg=?, @Text=?, @ReturnMsg_OUT=? OUTPUT'
		, @DtStartDate, @GenError4Test_Msg, @Text, @ReturnMsg_OUT OUTPUT) AT [Cippalippa\pippo,1340];
	Print 'Back to local Stored Procedure';
	Print 'Di ritorno della SP: ' + ISNULL(@ReturnMsg_OUT, 'NULL')
END

Mentre sul server remoto 'Cippalippa' avremo lo script eseguito in TRANSAZIONE (quindi al sicuro mediante rollback per evitare inconsistenze):

/*=============================================
Author:		Giuseppe AINO
Create date:	28/10/2021
Description:	Test esecuzione codesta StoredProcedure locale da Server remoto
			    con simulazione di errore e Rollback
===============================================*/
ALTER PROCEDURE [dbo].[LocalExec_FromRemote]
		 @DtStartDate	AS datetime = NULL		--Simulazione uso di un parametro di input
		,@GenError4Test_Msg AS nvarchar(max) = NULL	--Simulazione errore indotto, testo del msg di errore da generare qui
		,@Text		AS varchar(100)
		,@ReturnMsg_OUT	AS nvarchar(max) OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;
 
	DECLARE @c_SP_Version	AS varchar(10) = '0.00',	--Codice di versione di questa Stored Procedure
		@c_CR	AS nvarchar(10) = CHAR(13),	--non andava: CHAR(10)
		@c_TAB	AS nvarchar(10) = CHAR(9)
 
	Print 'SP Start "' + OBJECT_NAME(@@PROCID) + '",		Ver: ' + @c_SP_Version + @c_CR
			+ ', @DtStartDate = ' + ISNULL(CAST(@DtStartDate AS varchar), 'NULL') 
 
	SET XACT_ABORT ON;
 
	BEGIN TRY
		BEGIN TRANSACTION;
 
		INSERT INTO [4Tests].[dbo].[RS_AlignementLog]
			   ([AlignementId]
			   ,[ResultDescription]
			   ,[DtUpdate])
		 VALUES
			   (1
			   ,'Prova in transazione'
			   ,@DtStartDate);			--Variabile in input
 
		--La seguente query restituirà il record inserito prima. 
		--NOTA il (nolock) perché nonostante la transazione non confermata farà 
		--		visualizzare il contenuto della tabella
		SELECT * FROM [4Tests].[dbo].[RS_AlignementLog] (nolock);
 
		IF ISNULL(@GenError4Test_Msg, '') <> ''
		BEGIN
			Print 'Si è richiesto esplicitamente di generare una eccezione indotta qui!';
			SET @GenError4Test_Msg = 'Eccezione per annullare la INSERT precedente. ' + @c_CR 
				+ @GenError4Test_Msg;
			--La seguente eccezione 
			THROW 51000, @GenError4Test_Msg, 1;  
		END
		Print 'Nessuna eccezione indotta, operazione eseguita correttamente!'
 
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		Print 'Rollback, si annulla l''effetto della insert'
		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
 
	--Dopo il rollback la seguente farà vedere il contenuto della tabella VUOTO!
	SELECT * FROM [4Tests].[dbo].[RS_AlignementLog];
	Print 'Testo in input: ' + @Text
	SET @ReturnMsg_OUT = 'In output il messaggio: ' + ISNULL(@GenError4Test_Msg, 'NULL');
END

Mappa e Link


MS SQL | Tips


Chiavi di ricerca: Restart SQL Server

Author