Debugging SQL Server 2005 Stored Procedures

Hi all,
I have an access query that is based on a view that has a Sql function as part of the output columns.
Now I wonder, how is it possible to step thru the function in order to debug it, the way I would do in access modules step by step using F8?
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale BurrellDirectorCommented:
Can you clarify, is this an Access database or an SQL Server database? They are very different...
0
bfuchsAuthor Commented:
SQL 2005 back end, function and view all in the back end, just use access as front end for reporting.
0
Dale BurrellDirectorCommented:
Well one way of debugging, is to run the query in SQL Query Analyser... Thats certainly how I build and debug my queries. Then once you have the database side of things working you can debug the front end as you normally would.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Kent DyerIT Security Analyst SeniorCommented:
Access front-end is OK..  IMHO, if you have ability to run SSMS, (SQL Server Management Studio) - you will want to use that.  It is much more powerful than Access.  One of the things Access does is really why I went away from it is the fact that when you query even one row in a multi-row table, it will pull all of the rows across.  It is not a very good database in an Enterprise environment.  The other thing that is frustrating is at my previous job, we had a lot of little Access databases, where everybody had their own thoughts on database design and as a consequence it was terrible!  Access is great for reporting, but you should really look into the use SSRS (SQL Server Reporting Services).  Funny thing, I don't recall the last time I have used MS Access.
0
bfuchsAuthor Commented:
hi, thanks for replying.
@ dale_burrell: as I am not so familiar with all sql tools and how they are being called, please excuse me if I am asking something too simple or perhaps wrong..
we have SSMS and for creating simple queries/views that's fine, however the situation here is as follows, we have a view that contains a custom function stored in sql, that includes some logic involving variables etc...now for some reason the results is not returning as expected...therefore I am looking for a way to run it step by step, and see where exactly the problem is laying, the way I would perform in Access...
@kdyer: you're correct, and that is the way we have our setup, BE Sql and FE MS Access.
0
Dale BurrellDirectorCommented:
Query Analyser is one of the tools in SSMS, you can do far more than simple queries in it :)

Run the query that is returning incorrect results. Have the function logic in a second window and run it using the results from a single incorrect row from the view and work from there.

You can add as many intermediate 'selects' as you like to see what is happening to the data along the way and where it is going wrong.
0
bfuchsAuthor Commented:
ok, let me give you some more details here,
the function is being called with about 30 parameters, and some logic is programmed to assign finally a string to the returning variable string, somewhere down the road it loses the correct value, not necessary by a select statement, so I'm not sure how can this be accomplished by the query analyzer.
0
Dale BurrellDirectorCommented:
All in a days work mate :) you copy the T-SQL from the function into an empty query window, assign the variables for a case that fails, then add a combination of select and print statements at various points in the logic until you narrow down where its going wrong.

However if thats not appealing it appears that Query Analyser has a built in debugger http://www.techrepublic.com/article/debugging-stored-procedures-in-sql-servers-query-analyzer/
0
bfuchsAuthor Commented:
hi, just tried the suggestion from the link you supplied and it says there:

3.Select the stored procedure of interest.
4.Right-click on the stored procedure's name and select Debug from the pop-up menu

however its not displaying by me the debug menu in as part of the choices.
Is it possible that this does not come with SQL Express version? or maybe there is something else I'm missing here?
0
bfuchsAuthor Commented:
Hi dale_burrell,
re your first suggestion, given the small example (see attached), would you be able to guide me how to apply it there?
10-9-13.txt
0
Dale BurrellDirectorCommented:
I've never used the debugger, I've written and debugged some highly complex T-SQL without ever having needed it.

Your code looks very simple, so it should be obvious what isn't working. However its not obvious to me because I don't know what its supposed to do.

My initial observations are:

1) The input value "@EmployeeId" is not used anywhere in the function - so you are always going to get the same value back.

2) You make use of '@Expired" without ever giving it a value, therefore the first statement will never run and nor will the second...

If you want to test it outside of the function do something like:

declare @EmployeeID int

set @EmployeeID = 1234 -- Change to value you wish to test

-- Function start

declare @MissingDocs varchar(2000), @Expired varchar(1000)

-- Something should initialise @MissingDocs & @Expired

select @MissingDocs, @Expired

if isnull(@Expired,'') <> '' begin
  select @MissingDocs = @MissingDocs + '---Expired: ' + @Expired
end

select @MissingDocs

IF LEN(@MissingDocs) > 0 begin
  set  @MissingDocs = SUBSTRING(@MissingDocs,3, LEN(@MissingDocs))
end

select @MissingDocs

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Hi dale_burrell,
The code was just a sample of what I am having to debug, in actuality the @employeeid is being used... however your second point about original value was the culprit (as I am used to VBA variables where this works without assigning anything).
Thanks a lot for your example of debugging, will be handy if I ever need it in the future..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.