May 14, 2003 at 10:15 am
Can anyone offer any tips for handling the following scenario ?
We have a 2 node cluster which is running S2K and there are therefore 2 instances of SQL. We are considering splitting our many application databases across both instances, but have just uncovered a coding error which results in the message "Could not locate entry in sysdatabases for database 'XDB'. No entry found with that name. Make sure that the name is entered correctly."
This is because our stored procedures do not specify any instance name.
What is the best way of implementing a multiple instance environment without hardcoding the instance names into stored procedures etc. ?
May 14, 2003 at 11:34 am
Can you please post your stored procedure here ? Make sure you connect to the correct instance from QA and the database is in this instance to run the sp.
Edited by - Allen_Cui on 05/14/2003 11:35:41 AM
May 14, 2003 at 11:52 am
Procedure is as follows
=====================================
CREATE PROCEDURE [dbo].[usp_SBIR_GetUnReconciledInvoiceCustomerName]
@RequestingUser NVARCHAR(50), @ApplicationID INT
AS
BEGIN
SET NOCOUNT ON
/*
' usp_SBIR_GetUnReconciledInvoiceCustomerName
'----------------------------------------------------------------
' Description:
' Returns All Customer Names who have Unreconciled invoices
'
' In:
' @RequestingUser NVARCHAR(50), @ApplicationID INT
'----------------------------------------------------------------
*/
DECLARE @ErrorNb INT, @rv INT, @PermissionCheckResult INT, @SPName NVARCHAR(100)
SET @ErrorNb = 0
SET @SPName = OBJECT_NAME(@@PROCID)
/* TRANSACTION SECURITY CHECK */
EXEC @PermissionCheckResult = EDB.dbo.usp_EDB_TransactionSecurityCheck @RequestingUser, @ApplicationID, @SPName
IF (@PermissionCheckResult = -1)
BEGIN
SET @ErrorNb = 50005
GOTO ErrorProcess
END
ELSE
IF (@PermissionCheckResult > 0)
BEGIN
SET @ErrorNb = @PermissionCheckResult
GOTO ErrorProcess
END
/* INPUT PARAMETERS TESTING */
IF (@RequestingUser IS NULL)
BEGIN
SET @ErrorNb = 50001
GOTO ErrorProcess
END
IF (@ApplicationID IS NULL)
BEGIN
SET @ErrorNb = 50001
GOTO ErrorProcess
END
/* BODY OF THE SP */
SELECT DISTINCT tblEDBCustomer.CustomerName AS CustomerName
FROM EDB.dbo.tblEDBCustomer AS tblEDBCustomer
INNER JOIN dbo.tblSBInvoiceHeader AS tblSBInvoiceHeader
ON tblEDBCustomer.CustomerID = tblSBInvoiceHeader.CustomerID
WHERE tblSBInvoiceHeader.IsReconciled = 0
/* ERROR CHECKING */
SET @ErrorNb = @@ERROR
IF @ErrorNb <> 0
GOTO ErrorProcess
/* FINAL PROCESSES & ERROR HANDLING */
-- Final Processes
SET NOCOUNT OFF
RETURN 0
-- Error Handling
ErrorProcess:
SET @rv = -99
IF (@ErrorNb = 50005)
BEGIN
SET @rv = -1
RAISERROR(@ErrorNb,14,1)
END
ELSE
BEGIN
SET @rv = -99
RAISERROR(@ErrorNb,16,1)
END
SET NOCOUNT OFF
RETURN @rv
END
GO
=============================================
The EDB database is in the other instance of SQL . This is just one of many 'cross instance' procedures
May 14, 2003 at 12:22 pm
In order to call SPs in another instance, you have to setup either linked server or remote server and use four-part names in your SPs.
You may consider to use Dynamic SQL statements to store Instance Name in program variables that can be modified at run time. It does require lots of changes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply