How to know error happen on which line number SQL server 2012?

Question

0.00/5 (No votes)

See more:

Question

I have big stored procedure on sql server 2012 it compiled success when create it

when execute it by give it parameters

exec stored procedure parameter

it give me error

but problem i cannot know this error exist on which line

so that i need any thing tell me about error on which line

my error show when execute stored procedure and cannot understand it

Msg 121, Level 15, State 1, Line 1
i need message error as error exist on line number as example 45

because i work on stored procedure may be reach to 300 lines

so that my question what this mean

Msg 121, Level 15, State 1, Line 1

What I have tried:

exec storedprocedure paramtername

cannot give me error on which line
it give me error

Msg 121, Level 15, State 1, Line 1

Posted 29-Nov-19 3:45am

ahmed_sa

Updated 29-Nov-19 4:20am

Add a Solution

Comments

[no name] 29-Nov-19 9:49am

I would suggest to try it with the debugger.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2019-11-29T03:54:00

Solution 1

SQL Server Error Messages - Msg 121[^]
The text which matches this error number is:

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Posted 29-Nov-19 3:54am

phil.o

Comments

ahmed_sa 29-Nov-19 10:00am

thank you i mean error exist on which line not error message
as
1
2
3
4
5 error here
if i work on stored procedure have big amount of lines
i need if stored procedure have error tell me
error on line number 5 like that

Richard Deeming 29-Nov-19 10:58am

I can't create a stored procedure with an insert statement which would generate that error. The CREATE PROCEDURE throws an error, and provides the exact line number where the error was found.

I suspect you're using dynamic SQL within your procedure, which obviously won't have a line number.

OriginalGriff · Answer 2 · 2019-11-29T04:21:00

When I try it:

SQL

CREATE PROCEDURE ThrowError --1
AS                          --2
BEGIN                       --3
       SET NOCOUNT ON;      --4
       SELECT 1/0           --5
END                         --6

And run it:

SQL

USE [Testing]
PRINT 'Before...'
EXEC	[dbo].[ThrowError]
PRINT 'After...'

I get the line number in the SP:

Before...
Msg 8134, Level 16, State 1, Procedure ThrowError, Line 5
Divide by zero error encountered.
After...

So ... I'd guess that line one of your SP is an INSERT statement, and you aren't providing enough data.

I'm using SQL Server 2012 R2:

Product Version:          11.0.5388.0
Product Name:             SQL Server 2012
Product Level:            SP2 
Product Edition:          Express Edition (64-bit)

(Got via this script: TechNet Determining which version and edition of SQL Server Database Engine is running[^] )