SQL Server 2008 How to return Errors to web pages for development

Question

Since I have moved my SQL Server 2008 R2 to another server I have not been getting detailed errors from SQL Server on my development ASP pages. SQL Server use to return a detail error if a stored procedure was missing a parameter or a column did not exist. Now I just get a blank page with no information directing me to the source of the error. Is there something I am missing? I have look through the options in Management Studio but have found nothing. I tried the help but nothing relating to my problem.

Maybe there is system stored procedure that raises errors globally?

 

SQL Server 2008 R2

Microsoft SQL Server Management Studio             10.50.1617.0
Microsoft Analysis Services Client Tools                  10.50.1617.0
Microsoft Data Access Components (MDAC)           3.86.3959
Microsoft MSXML                                                     2.6 3.0 6.0
Microsoft Internet Explorer                                     8.0.6001.18702
Microsoft .NET Framework                                      2.0.50727.3625
Operating System                                                  5.2.3790

Answer 1

Could you compare the database options and server configuration to see if something is different?  For server configuration:

-- Server configuration
-- Assuming a Remote linked server

select c.name, c.value_in_use, rc.value_in_use 
from master.sys.configurations c -- New Server
   left outer join 
        remote.master.sys.configurations rc -- Old Server
   on c.name = rc.name
where c.value_in_use <> rc.value_in_use

For database configurations you can scan the results

DECLARE @DBNAME NVARCHAR(128) SET @DBNAME = N'YourDBName' SELECT N'Local' AS Server, * FROM master.sys.databases WHERE name = @DBNAME UNION ALL SELECT 
N'Other', * FROM remote.master.sys.databases WHERE name = @DBNAME ORDER BY name, Server

This you will have to scan by scrolling the results sidewise looking for differences.

Just a start.

RLF

Answer 2

The old server died and I lost everything on it save the data which was on backup tapes. 

 

Thanks for the reply.

Answer 3

Hi JPGrajek,

Regarding to your old server is down, you can try to restore a database from backup tapes.  SQL server will create one new database from that backup files so you will have the data at the time of backup happened on source database. You don't need to check that as the backup file includes all data at the point of backup operation completes, and you get exact the same data after restore.

One more thing you may want to check is that make sure you have a valid backup file and the logical and physical integrity after restore, you can use RESTORE VERIFYONLY and DBCC CHECKDB commands.

Additional, How to: Restore a Database Backup (SQL Server Management Studio), please refer to this article

---

Regards, Amber zhang

Answer 4

Have you been trying to capture the bad command and then execute in SSMS directly? Does it return Error messages?

Also, this may be a bit irrelevant, but this blog post may be of some help

How to return error messages from the SQL Server to the client using SQLDataSource

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

I get no error message on my ASP pages. So in order to track down a bug I need use manually go through the ASP page code, set Response.write "Debug 1", Response.write "Debug 2"... to find which stored proc or SQL statement is causing the page to be Blank. 

 

I will try the link though thanks

Answer 6

How are you executing the code? May be you have some try/catch and catch block doesn't report the error?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog