October 21, 2009 at 5:29 am
Hi there,
I have a colocated SQL server that I am accessing via our local intranet. Currently, if the link is down the web page gives an error message, so I am trying to make the stored procedure that returns information still work, even when the link is dead.
I have found this stored procedure: http://www.sqldbatips.com/showcode.asp?ID=38
When testing my stored procedure in SSMS it works fine, but as soon as I run it via ASP I get permission errors. The only way I have found to get around this is to put the user in the sysadmin server role, something I am not that happy doing.
Do I have any other options? Or should I do the error checking on the ASP page?
Cheers,
Steve
October 21, 2009 at 5:41 am
I don't like that script, does too many things behind the scenes.
Why not simply:
SELECT TOP 1 1 FROM remoteserver.dbname.dbo.sometable
or, even better if the remote server is SQL Server
SELECT TOP 1 1 FROM remoteserver.master.dbo.sysdatabases
and check for errors?
-- Gianluca Sartori
October 21, 2009 at 6:32 am
Maybe I am doing this wrong, but here is my test:
DECLARE @serverup int
SELECT @serverup = (SELECT TOP 1 1 FROM ACET.master.dbo.sysdatabases)
IF @@ERROR <> 0 SELECT @serverup = 0
IF @serverup = 1
BEGIN
SELECT COUNT(*) AS Total
FROM ACET.acet.dbo.returnrequest AS RR
WHERE RR.rrq_ran IS NULL AND RR.rrq_status = 'Pending'
END
ELSE SELECT 'ERROR'
This works with a good linked server. To test it, I deleted the linked server and recreated it with the wrong IP address. But when I run the code I get an SQL Server does not exist or access denied error, it doesn't just output 'ERROR'.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply