Menu
I am executing a stored proc from SSMS and I get this error message:
Msg 295, Level 16, State 3, Procedure spEdiCreateOrders, Line 80 [Batch Start Line 2]
Conversion failed when converting character string to smalldatetime data type.
Of course I could try and err, but I'd like to be able to take advantage of the message, and I am a bit perplexed by the message.
'line 80': starting from where ? From the BEGIN clause of the Alter procedure ? Or does it really refer to the line 80 I see when I click Modify ?
Also, what does Batch Start Line 2 mean ?
Thx !
edit: note that I am NOT interested in help solving the issue (it is already solved). I just want a clear guidelines about how to interpret 'line 80' and 'Batch Start Line 2', so that next time I immediately know where to look at.
971
Overview. This SQL error generally means that somewhere in the query, there is invalid syntax. Some common examples: Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD)
In the Object Explorer, expand Management → SQL Server Logs. Choose the error log you want to see, for example the current log file.
The SQL LIKE Operator There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Using @@ERROR to conditionally exit a procedure. The following example uses IF...ELSE statements to test @@ERROR after an DELETE statement in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.
About Line n
N it is refer to the number of line of object considering any spaces with in it , or before its body.
Example 1
Assume the next is SQLQuery window after clicking New Query button in SSMS
1. Create proc spTest1
2. As
3. Begin
4. Select 1/0
5. End
after executing spTest1, you will get the next message
Msg 8134, Level 16, State 1, Procedure spTest1, Line 4
Example 2
Assume the next is SQLQuery window after clicking New Query button in SSMS
1.
2.
3.
4.
5.
6. Create proc spTest2
7. As
8. Begin
9. Select 1/0
10. End
after executing spTest2, you will get the next message
Msg 8134, Level 16, State 1, Procedure spTest2, Line 9
Conclusion how get the accurate number
For getting the accurate number use the system stored procedure sp_helptext as next
Exec sp_helptext spName
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
