Solved

Cannot get SQL return message in asp website

Posted on 2012-03-21
2
326 Views
Last Modified: 2012-08-13
We have an asp.net 4 website and an SQL 2008 database. When we call our SQL stored procedures (SPs) from our website, we gwet the table of data correctly, according to the parameters, but we sometimes do not get the text return message. It works sometimes, so there is nothing wrong with the @ReturnMessage parameter sent by the website, and it works every time from SQL MS with the same parameters. But it usually fails when called by the website - we use a breakpoint bwefore disposing of the adaptor and the @ReturnMessage parameter is null. Any ideas?
0
Comment
Question by:SageNeil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
sachitjain earned 500 total points
ID: 37747172
I would suggest, run SQL profiler with RPC:Completed event when you access the site for transactions calling such stored procedures. SQL profiler is very easy to use. Select only RPC:Completed event in it with all columns and have correct filter for database name. Thus you would be able to capture RPC commands that are getting fired through application for transactions tested over site. This summary might help you analyze what the problem is; might be possible either of the parameters in your procedure calls are messing up somewhere.

But if you still do not figure out the difference and root cause of issue with this approach then another alternative way of getting return message in application code is through appending select @returnmessage statement at last of your stored proc. This way it won't require @returnmessage to be passed as out parameter. Moreover this statement's execution would append one more table to your dataset (at application's side) getting populated through adapter. This last table in your dataset would have only one row and one column and that would be your return message.
0
 

Author Comment

by:SageNeil
ID: 37891287
Thanks - sorry about the delay but I had not used profiler before and have only now had time to try this new approach - but it did help. The error was actually quite a simple one but it was impossible to track down until profiler showed me exactly what was being sent. Many thanks for your help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question