0

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.

Karlomanio
  • 344
  • 4
  • 11
  • A few things. 1) The link doesn't work. 2) Is the "Parameter Count Mismatch" an SQL exception, or the client application exception, can you provide more context? 3) Can share what you are doing in the stored procedure? – SteveD Nov 14 '18 at 22:36
  • 1) I fixed the link. 2) Parameter count mismatch is coming from the application, but that is what it is getting from the Database. I reproduced the error in the Database by logging in as user Home1 into Home2. 3) Please see above – Karlomanio Nov 14 '18 at 22:41
  • Please show how code on how you execute the SP – Squirrel Nov 15 '18 at 00:56
  • Is what possible? All the database objects in your question refer to Home2. The question link is not relevant regardless because you can access objects in different databases from a single proc. The parameter count mismatch error is returned the client API, not by SQL Server. – Dan Guzman Nov 15 '18 at 01:12
  • @DanGuzman Is it possible to do a cross-database execution of a Stored Procedure? – Karlomanio Nov 15 '18 at 15:27
  • @Squirrel This occurs from within the application. I can share a screenshot. It's from a rather old-style and unusual application interface. – Karlomanio Nov 15 '18 at 15:29
  • @Karlomanio, it is possible for a proc to access objects in different databases. Is that what you mean by cross-database execution? It is still unclear to me how database Home1 is involved. All the code you posted is for Home2. – Dan Guzman Nov 15 '18 at 17:16
  • The application uses Home 1 as the default login and is coming from that database to execute in Home2 – Karlomanio Nov 15 '18 at 17:20
  • @Karlomanio, is the Home1 login a user in the Home2 database? That will be required along with execute permissions on the proc. `VIEW DEFINITION` may or may not be needed depending on the application. – Dan Guzman Nov 15 '18 at 17:53
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/183743/discussion-between-karlomanio-and-dan-guzman). – Karlomanio Nov 15 '18 at 17:53

0 Answers0