Login Login
MORE

WIDGETS

Widgets

Recent changes
Wanted articles
Who is online?
Article tools

MSSQL:Nozioni varie

From Aino Wiki

Jump to: navigation, search

Store procedure

Parametri

Come impostare un parametro di Default:

ALTER PROC [dbo].[p_CU_B_ADDRESS_BOOK_update]
    @COMPANY_CODE nvarchar(3) = '000',
    @DIVISION_CODE nvarchar(3) = '000',
    @CUSTOMER_CODE nvarchar(8)= '12345678',
    @FIRSTNAME nvarchar(50)= 'cognome',
    @MIDDLENAME nvarchar(50)= 'secondo nome'
-- etc

Notare l'Uguale a dx di ogni parametro, il valore di default DEVE essere una COSTANTE.

Stored Procedure con parametro di OUTPUT:

ALTER  PROCEDURE [dbo].[webVoucherRecharge]
	@account 		VARCHAR(20),
	@pin 			VARCHAR(20),
	@voucher_id 		INT 			= NULL, 		-- Optional serial no. of the card being used for the top up
	@voucher_value		FLOAT			= NULL	OUTPUT,
	@voucher_currency	VARCHAR(10)		= NULL	OUTPUT, -- Voucher currency
	@result_status		INT				= NULL	OUTPUT,
	@result_message		VARCHAR(255)	= NULL	OUTPUT,
	@show_output		BIT 			= 1	 			-- Flag used to determine if we're to return the status resultset
AS
BEGIN
-- etc ...
END
 
------------- Esempio di esecuzione senza valorizzare i parametri:
 
DECLARE	@return_value int,
		@voucher_value float,
		@voucher_currency varchar(10),
		@result_status int,
		@result_message varchar(255)
 
EXEC	@return_value = [dbo].[webVoucherRecharge]
		@voucher_value = @voucher_value OUTPUT,
		@voucher_currency = @voucher_currency OUTPUT,
		@result_status = @result_status OUTPUT,
		@result_message = @result_message OUTPUT
 
SELECT	@voucher_value as N'@voucher_value',
		@voucher_currency as N'@voucher_currency',
		@result_status as N'@result_status',
		@result_message as N'@result_message'
 
SELECT	'Return Value' = @return_value

SQL Server Engine

Porta di default: 1433

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

Funzionalità specifiche

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_cmdshell ''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_cmdshell ''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_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
	--Print @sql
	EXEC (@QrySQL)
 
	--Delete dummy file 
	SET @QrySQL= 'EXEC master..xp_cmdshell ''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

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

Varie

Occupazione di una tabella

sp_spaceused 'Nome della tabella'

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

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

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

Altro

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

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

Mappa e Link


MS SQL


Chiavi di ricerca: Restart SQL Server

Author