Solved

Cannot get SQL return message in asp website

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This is a fairly complicated script that will install the required prerequisites to install SCCM 2012 R2 on a server.  It was designed under the functional model in order to compartmentalize each step required, reducing the overall complexity.  The …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

862 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

23 Experts available now in Live!

Get 1:1 Help Now