Cross-database execution of Stored Procedure

300 views Asked by At

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.) enter image description here

enter image description here

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.

0

There are 0 answers