Probabilmente tutti sapranno già che BCP e Stored Procedure in certi casi non vanno molto daccordo.
Io invece l'ho scoperto solo di recente! ed ovviamente spiaccicandomi brutalmente con il dannato caso reale.
Scenario: Un'applicazione lato server in SQL 2000 deve esportare dati in file di testo su base schedulata. Niente di meglio che usare DTS e stored procedure a manetta: causa altre considerazioni che rimando ad un prossimo post, si è realizzato un DTS che lancia una shell con BPC con l'opzione queryout. Il comando BCP viene composto dinamicamente, seguendo apposite strutture di configurazione e morale l'OUTPUT di una stored procedure viene sbattuto nel file di output.
Dopo un lungo periodo di sereno esercizio del sistema, metto mano ad una delle stored procedure che generano l'output.
La procedura precedente viene riscritta ed ora utilizza delle tabelle temporanee. Viene messo in piedi un meccanismo di logging dei record destinati all'output, per tracciare la corrispondenza tra file in uscita e record utilizzati, in modo che uno stesso record non possa finire in due file distinti.
Ovviamente quando si esegue la procedura direttamente da QA tutto funziona per il verso giusto.
Quando la si fa eseguire dal DTS ovviamente NO! Armati di santa pazienza e di Profiler scopriamo che riceviamo una caterva di errori di tipo 208
Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name #...
Il DTS si suicida e non è affatto chiaro né il come né il perchè. Le tabelle temporanee vengono create rispettando le regole di scope (uso stored procedure annidate e le temp table vengono create nella sp chiamante e referenziate in quelle chiamate), tant'è che da QA tutto è OK.
Forse ci sono dei problemi con l'ottimizzatore di SQL, magari non si tratta di veri e propri errori ma solo di Warning.
Riscrivo comunque la procedura, sostituendo le tabelle temporanee con base table e faccio attenzione a gestire possibili esecuzioni concorrenti della stessa stored procedure, utilizzando gli SPID e marcando i record coinvolti.
Questo work-around risolve i problemi degli errori 208, ma il DTS fallisce ugualmente.
Anzi, seguendo la traccia ottenuta con il Profiler mi pare che il comportamento di SQL sia del tutto delirante: controlli (IF EXISTS) che dovrebbero fallire invece funzionano, UPDATE che dovrebbero agire su una riga non trovano nulla e così via! Un vero incubo.
Non volendo credere di aver a che fare con un server posseduto e non avendo un esorcista a portata di mano, guardo meglio il Profiler e scopro che la stored procedure viene invocata DUE VOLTE di fila!!!!
Non c'è dubbio! Aggiungo istruzioni per loggare le esecuzioni e vedo chiaramente che il DTS, lanciando una BCP via shell di sistema operativo, ESEGUE DUE VOLTE la mia stored procedure.
Ecco spiegato il comportamento delirante a run-time: i controlli logici, dei quali il codice T-SQL era imbottito per garantire la consistenza dei dati, fallivano alla seconda esecuzione, perchè era giusto fosse così. Stolto io a non sapere che, quando BCP scarica su file l'output di una stored procedure con l'opzione QUERYOUT, esegue due volte la stored stessa: la prima per ricavare il formato di uscita, la seconda per ottenere i dati veri e propri.
Finora non ce ne eravamo mai accorti perchè le esportazioni erano ripetibili, ma questa volta, avendo messo in pista un meccanismo di log, il giocattolo si è sfasciato per una delle solite features by design.
In proposito vi segnalo questo illuminante articolo di Nigel Rivett (se solo lo avessi letto prima, avrei rispamiato parecchio sudore freddo)
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
Quale soluzione adottare allora? per ragioni di tempi di sviluppo abbiamo allora preferito abbandonare il BCP e sostituirlo nel package con un ActiveX Script task che usa un banale recordset ado e lo scaraventa su file. Meno performante di sicuro, ma anche molto meno "sorprendente".
Non si finisce mai di imparere ed ora, con una mano sulla coscienza, quanti di voi conoscevano questa particolarità del BCP?