reading the error messages - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

reading the error messages

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-22 : 02:05:42

when a statement fails and it tell me for example

Server: Msg 137, Level 15, State 2, Procedure spinsertnew, Line 266
Must declare the variable '@'.

How can I find which line it is using query manager?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 02:11:19

traps errors using @@Error

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-22 : 02:17:23

not sure what you mean - can you elaborate -- I want to know the best way to debug as i'm coding stored procedures

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 02:34:52

Try to use Error handling in your stored procedure. Make use of RaiseError and @@Error.

I dont whether this is the best technique to debug, but when i stuck up i use to the print statements after every few line of code, so that i come to know where exactly the error is. and then try to fix it.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-22 : 02:42:09

so a statement like this Server: Msg 137, Level 15, State 2, Procedure spinsertnew, Line 266 doesn't really tell me where the error is?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 02:53:46

No it will not tell you @ which line the error is

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-22 : 08:51:13

In spinsertnew procedure, you might be using dynamic sql or at line 266 of that procedure, it encountered a @ that is considered undeclared variable (there might have been a typo error where a space was inserted). Goto your spinsertnew procedure and check the line 266 or use the PRINT/RAISERROR to show the content of the SQL variable before executing it. The Level 15 error means it is an error that can be corrected by the user.

Use the query analyzer (2000) or Enterprise Manager (2005).

May the Almighty God bless us all!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-22 : 10:56:30

Basically, you are using a variable somewhere in your code which was not declared.

Or which you declared but didnt put the datatype and length

paste your code here

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-22 : 11:00:43

Oh to find out which line, open your code in QA, and press Ctrl + F and enter the line number

Note, it would take you to a line above the error line, due to the extra codes on top

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-22 : 12:36:29

I am passing in a variable called @mytype and @state

is there anything wrong with the follwoing statement

if @state='IL' and @type='abc'
@type='def'

anythign wrong with the above??

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-22 : 12:43:58

nevermind i found the bug -- thanks for your help

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-22 : 17:03:00

more like


if @state='IL' and @type='abc'
begin
select @type='def'
end

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-23 : 01:16:04

no it works the other way - the statement was on a different line -- any reason to use the begin and end if i'm only changing one thing?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-23 : 02:04:23

Basically to assign a value to a variable, you use the the key word select as above or set

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources