Solved

Cannot get SQL return message in asp website

Posted on 2012-03-21
2
319 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
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange users below are the steps you can follow to upgrade your Lync server to latest CU's or cumulative updates. Note: Perform it during non-production hours.   Step 1: Backup your lync and SQL server database. Follow below article: h…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now