Condividi tramite

Programmazione motore di database stored procedure estese

  • Articolo

Si applica a: SQL Server

Importante

Questa funzionalità verrà rimossa nelle versioni future di SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Usare invece l'integrazione CLR.

Funzionamento delle stored procedure estese

Il funzionamento di una stored procedure estesa è il seguente:

  1. Quando un client esegue una stored procedure estesa, la richiesta viene trasmessa in formato TDS (Tabular Data Stream) o Simple Object Access Protocol (SOAP) dall'applicazione client a SQL Server.

  2. SQL Server cerca la DLL associata alla stored procedure estesa e carica la DLL se non è già caricata.

  3. SQL Server chiama la stored procedure estesa richiesta (implementata come funzione all'interno della DLL).

  4. La stored procedure estesa passa set di risultati e restituisce parametri al server mediante l'API Stored procedure estesa.

In passato i servizi ODS (Open Data Services) venivano utilizzati per scrivere applicazioni server, come gateway per ambienti di database non SQL Server. SQL Server non supporta le parti obsolete dell'API Open Data Services. L'unica parte dell'API Open Data Services originale ancora supportata da SQL Server sono le funzioni di stored procedure estese, quindi l'API è stata rinominata nell'API Stored procedure estesa.

Con l'emergere di query distribuite e integrazione con CLR, la necessità di applicazioni API stored procedure estese è stata ampiamente sostituita.

Se si dispone di applicazioni gateway esistenti, non è possibile usare l'oggetto opends60.dll fornito con SQL Server per eseguire le applicazioni. Le applicazioni gateway non sono più supportate.

Stored procedure estese e integrazione CON CLR

L'integrazione con CLR offre un'alternativa più affidabile alla scrittura di logica lato server difficile da esprimere o impossibile scrivere in Transact-SQL. Nelle versioni precedenti di SQL Server, le stored procedure estese (XP) hanno fornito l'unico meccanismo disponibile per gli sviluppatori di applicazioni di database per scrivere tale codice.

Con l'integrazione con CLR, la logica usata per essere scritta sotto forma di stored procedure è spesso espressa meglio come funzioni con valori di tabella, che consentono di eseguire query sui risultati della funzione nelle SELECT istruzioni incorporandole nella FROM clausola .

Per altre informazioni, vedere Panoramica dell'integrazione con CLR.

Caratteristiche di esecuzione delle stored procedure estese

L'esecuzione di una stored procedure estesa presenta tre caratteristiche:

  • La funzione stored procedure estesa viene eseguita nel contesto di sicurezza di SQL Server.

  • La funzione stored procedure estesa viene eseguita nello spazio di elaborazione di SQL Server.

  • Il thread associato all'esecuzione della stored procedure estesa è lo stesso utilizzato per la connessione client.

Importante

Prima di aggiungere stored procedure estese al server e concedere autorizzazioni di esecuzione ad altri utenti, l'amministratore di sistema deve esaminare attentamente ogni stored procedure estesa per assicurarsi che non contenga codice dannoso o dannoso.

Dopo il caricamento della DLL della stored procedure estesa, la DLL rimane caricata nello spazio indirizzi del server finché SQL Server non viene arrestato o l'amministratore scarica in modo esplicito la DLL tramite DBCC <DLL_name> (FREE).

La stored procedure estesa può essere eseguita da Transact-SQL come stored procedure usando l'istruzione EXECUTE :

EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;

Parametri

@ retval

Valore restituito.

@ param1

Parametro di input.

@ param2

Parametro di input/output.

Attenzione

Le stored procedure estese offrono miglioramenti delle prestazioni ed estendono le funzionalità di SQL Server. Tuttavia, poiché la DLL della stored procedure estesa e SQL Server condividono lo stesso spazio di indirizzi, una procedura di problema può influire negativamente sul funzionamento di SQL Server. Anche se le eccezioni generate dalla DLL della stored procedure estesa vengono gestite da SQL Server, è possibile danneggiare le aree dati di SQL Server. Come precauzione di sicurezza, solo gli amministratori di sistema di SQL Server possono aggiungere stored procedure estese a SQL Server. È consigliabile testare completamente tali procedure prima dell'installazione.

Inviare set di risultati al server con l'API Stored procedure estesa

Quando si invia un set di risultati a SQL Server, la stored procedure estesa deve chiamare l'API appropriata come indicato di seguito:

  • La srv_sendmsg funzione può essere chiamata in qualsiasi ordine prima o dopo tutte le righe (se presenti) con srv_sendrow. Tutti i messaggi devono essere inviati al client prima che lo stato di completamento venga inviato con srv_senddone.

  • La srv_sendrow funzione viene chiamata una volta per ogni riga inviata al client. Tutte le righe devono essere inviate al client prima che tutti i messaggi, i valori di stato o gli stati di completamento vengano inviati con srv_sendmsg, l'argomento srv_status di srv_pfieldo srv_senddone.

  • L'invio di una riga che non ha tutte le colonne definite con srv_describe fa sì che l'applicazione generi un messaggio di errore informativo e restituisca FAIL al client. In questo caso, la riga non viene inviata.

Creare stored procedure estese

Una stored procedure estesa è una funzione C/C++ con un prototipo:

SRVRETCODE xp_extendedProcName ( SRVPROC *);

L'uso del prefisso xp_ è facoltativo. I nomi delle stored procedure estese fanno distinzione tra maiuscole e minuscole quando si fa riferimento nelle istruzioni Transact-SQL, indipendentemente dalla tabella codici o dall'ordinamento installato nel server. Quando si compila una DLL:

  • Se è necessario un punto di ingresso, scrivere una DllMain funzione.

    Questa funzione è facoltativa. Se non viene specificato nel codice sorgente, il compilatore collega la propria versione, che non esegue alcuna operazione ma restituisce TRUE. Se si specifica una DllMain funzione, il sistema operativo chiama questa funzione quando un thread o un processo si collega o scollega dalla DLL.

  • Tutte le funzioni chiamate dall'esterno della DLL, ovvero tutte le funzioni Efunction delle stored procedure estese, devono essere esportate.

    È possibile esportare una funzione elencandone il EXPORTS nome nella sezione di un .def file oppure è possibile anteporre il nome della funzione nel codice sorgente con __declspec(dllexport), un'estensione del compilatore Microsoft (__declspec() inizia con due caratteri di sottolineatura).

I file seguenti sono necessari per la creazione della DLL di una stored procedure estesa.

File Descrizione
srv.h File di intestazione dell'API della stored procedure estesa
opends60.lib Importare la libreria per opends60.dll

Per creare la DLL di una stored procedure estesa, creare un progetto di tipo DLL. Per ulteriori informazioni sulla creazione di una DLL, vedere la documentazione dell'ambiente di sviluppo.

Tutte le DLL di stored procedure estese devono implementare ed esportare la funzione seguente:

__declspec(dllexport) ULONG __GetXpVersion()
{
   return ODS_VERSION;
}

__declspec(dllexport) è un'estensione del compilatore specifica di Microsoft. Se il compilatore non supporta questa direttiva, è necessario esportare questa funzione nel file nella DEF EXPORTS sezione .

Quando SQL Server viene avviato con il flag -T260 di traccia o se un utente con privilegi di amministratore di sistema esegue DBCC TRACEON (260)e se la DLL della stored procedure estesa non supporta __GetXpVersion(), il messaggio di avviso seguente viene stampato nel log degli errori (__GetXpVersion() inizia con due caratteri di sottolineatura).

Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().

Se la DLL della stored procedure estesa esporta __GetXpVersion(), ma la versione restituita dalla funzione è minore della versione richiesta dal server, viene stampato un messaggio di avviso che indica la versione restituita dalla funzione e la versione prevista dal server nel log degli errori. Se viene visualizzato questo messaggio, si restituisce un valore non corretto da __GetXpVersion()o si esegue la compilazione con una versione precedente di srv.h.

Nota

SetErrorMode, una funzione Win32, non deve essere chiamata nelle stored procedure estese.

Le stored procedure estese a esecuzione prolungata devono chiamare srv_got_attention periodicamente, in modo che la routine possa terminare se la connessione viene terminata o il batch viene interrotto.

Per eseguire il debug di una DLL di stored procedure estesa, copiarla nella directory di SQL Server \Binn . Per specificare il file eseguibile per la sessione di debug, immettere il percorso e il nome file del file eseguibile di SQL Server, ad esempio C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe. Per informazioni sugli sqlservr argomenti, vedere sqlservr Application.

Aggiungere una stored procedure estesa a SQL Server

Una DLL che contiene funzioni stored procedure estese funge da estensione per SQL Server. Per installare la DLL, copiare il file in una directory, ad esempio quella che contiene i file DLL standard di SQL Server (C:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn per impostazione predefinita).

Dopo che la DLL della stored procedure estesa viene copiata nel server, un amministratore di sistema di SQL Server deve registrarsi a SQL Server ogni funzione stored procedure estesa nella DLL. Questa operazione viene eseguita usando la stored procedure di sp_addextendedproc sistema.

Importante

L'amministratore di sistema deve esaminare attentamente una stored procedure estesa per assicurarsi che non contenga codice dannoso o dannoso prima di aggiungerlo al server e concedere autorizzazioni di esecuzione ad altri utenti. Convalidare sempre input di tutti gli utenti. Non concatenare l'input dell'utente prima di convalidarlo. Non eseguire mai un comando creato dall'input dell'utente non convalidato.

Il primo parametro di sp_addextendedproc specifica il nome della funzione e il secondo parametro specifica il nome della DLL in cui risiede tale funzione. Specificare il percorso completo della DLL.

Nota

Le DLL esistenti non registrate con un percorso completo non funzionano dopo l'aggiornamento a SQL Server 2005 (9.x) o a una versione successiva. Per risolvere il problema, usare sp_dropextendedproc per annullare la registrazione della DLL e quindi registrarla nuovamente specificando sp_addextendedproc, il percorso completo.

Il nome della funzione specificato in sp_addextendedproc deve corrispondere esattamente a quello presente nella DLL, anche nell'uso di maiuscole e minuscole. Ad esempio, questo comando registra una funzione xp_hello, che si trova in una DLL denominata xp_hello.dll, come stored procedure estesa di SQL Server:

sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';

Se il nome della funzione specificata in sp_addextendedproc non corrisponde esattamente al nome della funzione nella DLL, il nuovo nome viene registrato in SQL Server, ma il nome non è utilizzabile. Ad esempio, anche se xp_Hello è registrato come stored procedure estesa di SQL Server che si trova in xp_hello.dll, SQL Server non riesce a trovare la funzione nella DLL se si usa xp_Hello per chiamare la funzione in un secondo momento.

-- Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';

-- Use the newly registered name to call the function
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

Ecco il messaggio di errore:

Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).

Se il nome della funzione specificata in sp_addextendedproc corrisponde esattamente al nome della funzione nella DLL e le regole di confronto dell'istanza di SQL Server non fanno distinzione tra maiuscole e minuscole, l'utente può chiamare la stored procedure estesa usando qualsiasi combinazione di lettere minuscole e maiuscole del nome.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example succeeds in calling xp_hello
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HelLO @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Quando le regole di confronto dell'istanza di SQL Server fanno distinzione tra maiuscole e minuscole, SQL Server non può chiamare la stored procedure estesa se la stored procedure viene chiamata con un caso diverso. Questo vale anche se è stato registrato con esattamente lo stesso nome e le regole di confronto della funzione nella DLL.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example results in an error
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Ecco il messaggio di errore:

Server: Msg 2812, Level 16, State 62, Line 1

Non è necessario arrestare e riavviare SQL Server.

Eseguire query sulle stored procedure estese installate in SQL Server

Un utente autenticato di SQL Server può visualizzare le stored procedure estese attualmente definite e il nome della DLL a cui appartengono eseguendo la sp_helpextendedproc procedura di sistema. Ad esempio, l'esempio seguente restituisce la DLL a cui xp_hello appartiene:

sp_helpextendedproc 'xp_hello';

Se sp_helpextendedproc viene eseguito senza specificare una stored procedure estesa, vengono visualizzate tutte le stored procedure estese e le relative DLL.

Rimuovere una stored procedure estesa da SQL Server

Per eliminare ogni funzione stored procedure estesa in una DLL di stored procedure estesa definita dall'utente, un amministratore di sistema di SQL Server deve eseguire la sp_dropextendedproc stored procedure di sistema, specificando il nome della funzione e il nome della DLL in cui risiede tale funzione. Ad esempio, questo comando rimuove la funzione xp_hello, che si trova in una DLL denominata xp_hello.dll, da SQL Server:

sp_dropextendedproc 'xp_hello';

sp_dropextendedproc non rilascia stored procedure estese del sistema. L'amministratore di sistema deve invece negare EXECUTE l'autorizzazione per la stored procedure estesa al ruolo pubblico .

Scaricare una DLL di stored procedure estesa

SQL Server carica una DLL di stored procedure estesa non appena viene effettuata una chiamata a una delle funzioni della DLL. La DLL rimane caricata finché il server non viene arrestato o finché l'amministratore di sistema non usa l'istruzione DBCC per scaricarla. Ad esempio, questo comando scarica , xp_hello.dllconsentendo all'amministratore di sistema di copiare una versione più recente di questo file nella directory senza arrestare il server:

DBCC xp_hello(FREE);

Contenuto correlato