Jak uzyskać rzeczywisty numer wiersza procedury składowanej z komunikatu o błędzie?

Kiedy używam SQL Server i pojawia się błąd, komunikat o błędzie podaje numer wiersza, który nie ma korelacji z numerami wierszy w procedurze składowanej. Zakładam, że różnica wynika z białych znaków i komentarzy, ale czy rzeczywiście?

Jak mogę powiązać te dwa zestawy numerów wierszy ze sobą? Gdyby ktokolwiek mógł mi przynajmniej wskazać właściwy kierunek, byłbym naprawdę wdzięczny.

Używam SQL Server 2005.

IIRC, zaczyna liczyć wiersze od początku partii, która utworzyła ten proc. Oznacza to albo początek skryptu, albo ostatnią instrukcję „GO” przed instrukcją create / alter proc.

Łatwiejszym sposobem sprawdzenia tego jest pobranie rzeczywistego tekstu, którego SQL Server użył podczas tworzenia obiektu. Przełącz wyjście w tryb tekstowy (CTRL-T z domyślnymi mapowaniami klawiszy) i uruchom

sp_helptext proc_name

Skopiuj, wklej wyniki do okna skryptu, aby uzyskać podświetlanie składni itp., I użyj funkcji linii goto (myślę, że CTRL-G), aby przejść do zgłoszonego wiersza błędu.

Z przyzwyczajenia umieszczam LINENO 0bezpośrednio po BEGINw moich procedurach składowanych. Spowoduje to zresetowanie numeru linii - w tym przypadku do zera. Następnie po prostu dodaj numer linii zgłoszony przez komunikat o błędzie do numeru linii w SSMS, w którym napisałeś LINENO 0i bingo - masz numer linii błędu przedstawiony w oknie zapytania.

Jeśli użyjesz Catch Block i użyjesz RAISERROR () do walidacji dowolnego kodu w Try Block, wówczas wiersz błędu zostanie zgłoszony tam, gdzie znajduje się Catch Block, a nie tam, gdzie wystąpił rzeczywisty błąd. Użyłem tego w ten sposób, aby to wyjaśnić.

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT 
     @ErrorMessage = ERROR_MESSAGE() + ' occurred at Line_Number: ' + CAST(ERROR_LINE() AS VARCHAR(50)),
     @ErrorSeverity = ERROR_SEVERITY(),
     @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, -- Message text.
     @ErrorSeverity, -- Severity.
     @ErrorState -- State.
  );

END CATCH

Właściwie to Error_number()działa bardzo dobrze.

Ta funkcja zaczyna zliczać od ostatniej instrukcji GO (Batch Separator), więc jeśli nie użyłeś żadnych spacji Go i nadal wyświetla nieprawidłowy numer wiersza - dodaj do niego 7, tak jak w procedurze składowanej w linii numer 7 separator wsadu jest używany automatycznie. Więc jeśli użyjesz select Cast (Error_Number () + 7 as Int) jako [Error_Number] - otrzymasz pożądaną odpowiedź.

W TSQL / procedurach składowanych

Możesz otrzymać błąd, taki jak:

Msg 206, poziom 16, stan 2, procedura myproc, wiersz 177 [Batch Start Line 7]

Oznacza to, że błąd występuje w linii 177 w partii. Nie 177 w SQL. Powinieneś zobaczyć, od jakiego numeru linii zaczyna się twoja partia, w moim przypadku [7], a następnie dodajesz tę wartość do numeru linii, aby dowiedzieć się, która instrukcja jest błędna

możesz tego użyć

CAST(ERROR_LINE() AS VARCHAR(50))

a jeśli chcesz utworzyć tabelę dziennika błędów, możesz użyć tego:

INSERT INTO dbo.tbname( Source, Message) VALUES ( ERROR_PROCEDURE(), '[ ERROR_SEVERITY : ' + CAST(ERROR_SEVERITY() AS VARCHAR(50)) + ' ] ' + '[ ERROR_STATE : ' + CAST(ERROR_STATE() AS VARCHAR(50)) + ' ] ' + '[ ERROR_PROCEDURE : ' + CAST(ERROR_PROCEDURE() AS VARCHAR(50)) + ' ] ' + '[ ERROR_NUMBER : ' + CAST(ERROR_NUMBER() AS VARCHAR(50)) + ' ] ' +  '[ ERROR_LINE : ' + CAST(ERROR_LINE() AS VARCHAR(50)) + ' ] ' + ERROR_MESSAGE())

Długa odpowiedź: numer wiersza jest liczony na podstawie CREATE PROCEDUREinstrukcji plus wszelkie puste wiersze lub wiersze komentarza, które mogły znajdować się nad nim, gdy faktycznie wykonywałeś CREATEinstrukcję, ale nie licząc żadnych wierszy przedGO instrukcją…

O wiele łatwiej było stworzyć przechowywany proces, aby się nim bawić, aby potwierdzić:

GO

-- =============================================
-- Author:          <Author,,Name>
-- Create date: <Create Date,,>
-- Description:     <Description,,>
-- =============================================
CREATE PROCEDURE ErrorTesting
       -- Add the parameters for the stored procedure here
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

       -- Insert statements for procedure here
       SELECT 1/0

END
GO

Po utworzeniu możesz go przełączyć na ALTER PROCEDUREi dodać puste wiersze nad komentarzami oraz nad i pod pierwszymGO instrukcji, aby zobaczyć efekt.

Jedną bardzo dziwną rzeczą, którą zauważyłem, było to, że musiałem uruchomić EXEC ErrorTestingw nowym oknie zapytania zamiast podświetlać je na dole tego samego okna i uruchamiać… Kiedy to zrobiłem, numery linii ciągle rosły! Nie wiem, dlaczego tak się stało ...

możesz otrzymać komunikat o błędzie i wiersz błędu w bloku catch w ten sposób:

'Ms Sql Server Error: - ' + ERROR_MESSAGE() + ' - Error occured at: ' + CONVERT(VARCHAR(20),  ERROR_LINE())