MSSQL:Nozioni varie

Acquisizioni

Utility BCP

Il file è presente al seguente percorso:

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

Fonti:

• red-gate.com
• microsoft

Bulk Copy Program, sintassi:

bcp [database_name.] schema.{table_name | view_name | "query"}
    {in data_file | out data_file | queryout data_file | format nul}
                                                                                                         
    [-a packet_size]
    [-b batch_size]
    [-c]
    [-C { ACP | OEM | RAW | code_page } ]
    [-d database_name]
    [-D]
    [-e err_file]
    [-E]
    [-f format_file]
    [-F first_row]
    [-h"hint [,...n]"]
    [-i input_file]
    [-k]
    [-K application_intent]
    [-l login_timeout]
    [-L last_row]
    [-m max_errors]
    [-n]
    [-N]
    [-o output_file]
    [-P password]
    [-q]
    [-r row_term]
    [-R]
    [-S [server_name[\instance_name]]
    [-t field_term]
    [-T]
    [-U login_id]
    [-v]
    [-V (80 | 90 | 100 | 110 | 120 | 130 ) ]
    [-w]
    [ -x]

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
Alcuni argomenti importanti!
-c
Esegue l'operazione utilizzando un tipo di dati carattere. Questa opzione non determina la visualizzazione di una richiesta per ogni campo, ma usa char come tipo di archiviazione, senza prefissi e con il carattere di tabulazione ( \t ) come separatore di campo e il carattere di nuova riga ( \r\n ) come carattere di terminazione della riga. -c non è compatibile con -w.

Per altre informazioni, vedere Usare il formato carattere per importare o esportare dati (SQL Server).

-T
Specifica che l'utilità bcp si connette a SQL Server con una connessione trusted che usa la sicurezza integrata. Non è necessario specificare le credenziali di sicurezza dell'utente di rete, ovvero login_ide password . Se non si specifica -T , è necessario specificare -U e -P per eseguire correttamente l'accesso.

-w
Esegue l'operazione di copia bulk utilizzando caratteri Unicode. Questa opzione non visualizza una richiesta per ogni campo, ma usa nchar come tipo di archiviazione, il carattere di tabulazione ( \t ) come separatore dei campi e il carattere di nuova riga ( \n ) come carattere di terminazione della riga e non usa alcun prefisso. -w non è compatibile con -c.

Per altre informazioni, vedere Usare il formato carattere Unicode per importare o esportare dati (SQL Server).

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!

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

Ecco cos'è sul DB origine:

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

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

Oppure

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

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

Dopo la cura, ridotto a 20 Mbyte

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

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.

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

Creazione del Job per eseguire il pacchetto SSIS.

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

SSIS Package

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

Gestione Allarmi

SNMP

Ottime descrizioni: [3], [4]
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:

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

Andare sul tab Agent

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:

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:

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

• Monitor SNMP Trap

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).

• High availability Wikipedia.org

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:

• DB Mirroring con MS SQL 2005 html.it
• Always on

Tool:

• LifeKeeper

Invio Email

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

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

Segue una configurazione operativa

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:

• docs.microsoft.com OLE automation procedure (italiano)
• zealousweb.com

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:

• ! sp_OACreate
• ! sp_OAMethod
• ! sp_OADestroy
• sp_OASetProperty
• sp_OAGetErrorInfo
• sp_OAStop

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

Differenziale

• Doc: mssqltips

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

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

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

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

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:

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:

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

Configurandola come segue:

Quindi aprire SSMS ed aggiungere il Linked Server come segue:

Quindi configurarlo in 2 passi, il primo:

ed il secondo:

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

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.
Si adotterà EXECUTE .. AT vedi qui.
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';

Caso con parametri di input e di OUTPUT:

SET @SQLQuery = 'EXEC [' + @DestDBName + '].[dbo].[SP_DB_Alignment_ProjectData] '
				+ '@SrcDBName=?, @DestDBName=?, @ProjectSetID=?, '
				+ '@OriginalExcelFileName=?, '
				+ '@ResultMsg_OUT=? OUTPUT';

EXEC (@SQLQuery, @SrcDBName, @DestDBName, @ProjectSetID_OUT
		, @OriginalExcelFileName, @ResultMsg_OUT OUTPUT) AT [NomeLnkSrv,__nrPorta__];

IF ISNULL(@ResultMsg_OUT, '') <> ''
BEGIN
	Print 'Si èv erificato un errore: ' + @ResultMsg_OUT
	GOTO emailOnExit
END

Come da sqlauthority.com si può anche:

EXEC [HostSrv_Name\IST_DB,_portNr_].[RS_SRC_XXX].[dbo].[SP_Clean_T_D_CAMPAIGN]

Caso di studio

STORE PROCEDURE REMOTA

CREATE PROCEDURE [dbo].[Test_SP_rpt_Donations_aggr_hourly]
	@IsTest				AS bit = 0,
	@ResultMsg_OUT		AS nvarchar(256) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @LogDescription			AS nvarchar(max) = '',
			@c_SP_Version			AS varchar(10) = '1.00',
			@c_CR					AS nvarchar(10) = CHAR(13),		--Corrisponde a \r
			@c_LF					AS nvarchar(10) = CHAR(10),		--Corrisponde a \n		è il carattere separatore dell'output dela Web API
			@c_TAB					AS nvarchar(10) = CHAR(9)
 
	SET @LogDescription = '--| SP REMOTA: ' + OBJECT_NAME(@@PROCID) + ' Ver.: ' + @c_SP_Version + '"' + @c_CR
							+ '@IsTest = "' + CAST(@IsTest AS varchar)+ '"'
	RAISERROR(@LogDescription,0,1) WITH NOWAIT; --Serve a scaricare la coda dei messaggi per il Log!
 
 
	SET @ResultMsg_OUT = '';
 
	SET XACT_ABORT ON;
	BEGIN TRY		/*		AGGIORNAMENTO DATI ORARI	-	in Transazione		*/			
		BEGIN TRANSACTION;
 
		--Test verifica errore
		IF @IsTest = 1
		BEGIN
			SET @LogDescription = 'Test x verifica in caso di eccezione';
			THROW 51000, @LogDescription, 1;  
		END
 
		COMMIT TRANSACTION;
 
		SET @LogDescription = 'TRANSAZIONE CONCLUSA positivamente'
		RAISERROR(@LogDescription,0,1) WITH NOWAIT;
	END TRY
	BEGIN CATCH
	BEGIN	
		IF @@TRANCOUNT > 0
		BEGIN
			RAISERROR('Eccezione: ROLLBACK della TRANSAZIONE!',0,1) WITH NOWAIT;
			ROLLBACK TRANSACTION;
		END
		DECLARE	@ERROR_MESSAGE		NVARCHAR(MAX); 
 
		SELECT @ERROR_MESSAGE  = ERROR_MESSAGE(); 
 
		SET @ResultMsg_OUT = @ERROR_MESSAGE;
		RAISERROR(@ResultMsg_OUT,0,1) WITH NOWAIT;
	END
	END CATCH
 
	SET @LogDescription = '--| SP REMOTA ' + OBJECT_NAME(@@PROCID) + ' CONCLUSA!' + @c_CR
	RAISERROR(@LogDescription,0,1) WITH NOWAIT;
END

STORE PROCEDURE LOCALE

ALTER PROCEDURE [dbo].[Test_RunRemote_SP]
	@IsTest			AS bit = 0
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @DestDBName			AS varchar(128) = 'RS_SRC_Digitalk',			
			@SQLQuery 			AS nvarchar(max) = '',
			@SP_ResultMsg		AS nvarchar(256),
			@LogDescription		AS nvarchar(max) = '',
			@c_SP_Version		AS varchar(10) = '1.00',
			@c_CR				AS nvarchar(10) = CHAR(13),		--Corrisponde a \r
			@c_LF				AS nvarchar(10) = CHAR(10),		--Corrisponde a \n		è il carattere separatore dell'output dela Web API
			@c_TAB				AS nvarchar(10) = CHAR(9)
 
	SET @LogDescription = 'SP ' + ISNULL(OBJECT_NAME(@@PROCID), 'null') + ' Ver.: ' + @c_SP_Version + '"' + @c_CR
	RAISERROR(@LogDescription,0,1) WITH NOWAIT; --Serve a scaricare la coda dei messaggi per il Log!
 
	SET @SQLQuery = 'EXEC [' + @DestDBName + '].[dbo].[Test_SP_rpt_Donations_aggr_hourly] '
                + '@IsTest=?, '
                + '@ResultMsg_OUT=? OUTPUT';
 
	EXEC (@SQLQuery, @IsTest, @SP_ResultMsg OUTPUT) AT [_HOST_NAME_\ISTANZA_db,__PORTA__];
 
	IF ISNULL(@SP_ResultMsg, '') <> ''
	BEGIN
		Print '(Ritorno) Si è verificato un errore: ' + @SP_ResultMsg
	END
	ELSE
		Print '(Ritorno) Esecuzione remota conclusa POSITIVAMENTE.'
END

Esecuzione del test:

[Test_RunRemote_SP] 1

Output:

SP Test_RunRemote_SP Ver.: 1.00"

--| SP REMOTA: Test_SP_rpt_Donations_aggr_hourly Ver.: 1.00"
@IsTest = "1"
Eccezione: ROLLBACK della TRANSAZIONE!
Test x verifica in caso di eccezione
--| SP REMOTA Test_SP_rpt_Donations_aggr_hourly CONCLUSA!

(Ritorno) Si è verificato un errore: Test x verifica in caso di eccezione

Completion time: 2022-11-25T19:20:10.0214545+01:00

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:

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

OLEDB e ODBC Drivers

Per visualizzare l'elenco dei provider installati:

EXECUTE MASTER.dbo.xp_enum_oledb_providers

Esempio di output 1:

Esempio di output 2:

Oracle

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

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