I have an application (let's call it App1) whose home Database is Home1. I have been tasked to create a Stored Procedure (let's call it SP1) in Database Home2. Using a connection in the application to Database Home2, I'm supposed to execute the Stored Procedure, which does a series of simple updates on two tables in Home2 through the application that will update some tables in Home2.
I've tried modifying security for the Stored Procedure to grant permission for user Home1 to both execute and view the definition, but I still can't get it to run.
I get the following error:
Parameter Count Mismatch: App1 = 4 Stored Procedure = -1 Procedure name = SP1
Here is how the application sets up the Stored Procedure (it says PL/SQL, but it is for a SQL Server Database.)
Here is the connect string (see Connection ID on the first screenshot) from within the application: PROVIDER=SQLNCLI10;DATA SOURCE=Home2;INITIAL CATALOG=Home2;PERSIST SECURITY INFO=FALSE;
Here's what the code looks like:
CREATE PROCEDURE dbo.SP1
@pStatus varchar(6),
@pProcessQueueId numeric(10,0),
@pInvoiceNo varchar(30),
@pInvoiceSeqNo varchar(3)
BEGIN TRANSACTION
Begin Try
declare @pSyncDate datetime
if @pStatus is null
set @pSyncDate = null
else
set @pSyncDate = getdate()
if @pInvoiceNo is null
Begin
update dbo.TABLE1
set SYNC_DT = @pSyncDate, STATUS_CD = @pStatus
where PROCESS_QUEUE_ID = @pProcessQueueId
update dbo.TABLE2
set SYNC_DT = @pSyncDate, STATUS_CD = @pStatus
where PROCESS_QUEUE_ID = @pProcessQueueId
End
if @pInvoiceNo is not null and @pInvoiceSeqNo is null
Begin
update dbo.TABLE1
set SYNC_DT = @pSyncDate, STATUS_CD = @pStatus
where PROCESS_QUEUE_ID = @pProcessQueueId
and INVOICE_NO = @pInvoiceNo
update dbo.TABLE2
set SYNC_DT = @pSyncDate, STATUS_CD = @pStatus
where PROCESS_QUEUE_ID = @pProcessQueueId
and INVOICE_NO = @pInvoiceNo
End
if @pInvoiceNo is not null and @pInvoiceSeqNo is not null
Begin
update dbo.TABLE1
set SYNC_DT = @pSyncDate, STATUS_CD = @pStatus
where PROCESS_QUEUE_ID = @pProcessQueueId
and INVOICE_NO = @pInvoiceNo
and INVOICE_SEQ = @pInvoiceSeqNo
update dbo.TABLE2
set SYNC_DT = @pSyncDate, STATUS_CD = @pStatus
where PROCESS_QUEUE_ID = @pProcessQueueId
and INVOICE_NO = @pInvoiceNo
and INVOICE_SEQ = @pInvoiceSeqNo
End
COMMIT TRANSACTION
End Try
Is a cross-database execution of a Stored Procedure possible in SQL Server 2008? I read here that it is not possible or is this post referring to some other aspect of Cross-database connections.