Syntax error on the stored procedure

Question

Environment: SQL Server 2008 R2.

Here is a stored procedure that is being called by an APP to copy data from a set of tables from database to another database where they both have the same schema. It doesn't seem to copy the data. when I try to run it manually on query analyser.

exec dbo.sp_Copy_DB_Subset_Tables ('Server1\Instance','MainDB','Server1\Instance','MiniDB')

But I get this error, when I run it:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Server1\Instance'.

Here is the stored procedure, I can't see how this could error. The databases are on the same server. Why am I getting the error?

CREATE  PROCEDURE [dbo].[sp_Copy_MYDB_Subset_Tables](
    @vSourceServer      varchar(255)
    ,@vSourceDatabase   varchar(255) = 'MYDB'
    ,@vDestinationServer    varchar(255)
    ,@vDestinationDatabase  varchar(255) = 'MYDB'
    ,@vIsServerOnDomain     BIT = 1 --
    ,@TargetDBUserName  varchar(255) = ''
    ,@TargetDBPassword  varchar(255) = ''
    )  
AS
BEGIN 
    Declare 
    @vSourceTable   varchar(255)
    ,@vSourceSelectQuery    varchar(255)
    ,@vDestinationTable     varchar(255)
    ,@vReturn               int 
    ,@vReturnMessage        varchar(max) 
    ,@vPeriodtoArchive      int
    ,@ColumnMappings        varchar(4000)


BEGIN TRY
    if (@vSourceServer is null or @vSourceServer = '')
        set @vSourceServer = @@servername

    if object_id('tempdb..#TempTableCopyList') is not null
        drop table #TempTableCopyList

    Create Table #TempTableCopyList
    (
        id [int] NOT NULL  primary key clustered
        ,TableName      varchar(100)
        ,ColumnMappings varchar(4000)
        ,DateCopied     datetime
    )

    insert into #TempTableCopyList
        Select id, TableName, ColumnMappings, DateCopied
        from dbo.fn_Get_MYDB_Subset_TableList()

    declare c cursor for 
    Select TableName, ColumnMappings 
        from #TempTableCopyList
           order by id desc
    open c

    fetch next from c into @vSourceTable, @ColumnMappings

    While @@fetch_status = 0 
    BEGIN
       print 'Start Copying table: ' + @vSourceTable + ' at ' + convert(varchar(30),getdate(),120)

       Set @vSourceSelectQuery = 'Select * from ' + @vSourceTable + ' with (nolock) '

       IF @vIsServerOnDomain = 0
        BEGIN
                exec master.dbo.usp_BulkCopy 
                     @vSourceServer
                    ,@vSourceDatabase
                    ,@vSourceSelectQuery
                    ,@vDestinationServer
                    ,@vDestinationDatabase
                    ,@vSourceTable
                    ,1
                    ,1
                    ,true
                    ,false
                    ,''
                    ,''
                    ,@TargetDBUserName  
                    ,@TargetDBPassword
                    ,@ColumnMappings
        END
        ELSE BEGIN

                exec master.dbo.usp_BulkCopy 
                     @vSourceServer
                    ,@vSourceDatabase
                    ,@vSourceSelectQuery
                    ,@vDestinationServer
                    ,@vDestinationDatabase
                    ,@vSourceTable
                    ,1
                    ,1
                    ,true
                    ,true
                    ,''
                    ,''
                    ,''
                    ,''
                    ,@ColumnMappings
        END
                UPDATE #TempTableCopyList
                    set DateCopied = GETDATE()
                WHERE TableName = @vSourceTable




        fetch next from c into @vSourceTable, @ColumnMappings

    END

    close c
    deallocate c
END TRY
BEGIN CATCH
    close c
    deallocate c
    DECLARE @ErrorMessage VARCHAR(MAX)
    SET @ErrorMessage = error_message()
    print @vSourceTable + '; '+ @vSourceServer+ '; '+  @vSourceDatabase+ '; '+ @vDestinationServer+ '; '+ @vDestinationDatabase+ '; '+ @vDestinationTable
    Print @ErrorMessage
    RAISERROR (@ErrorMessage, 0, 1)
END CATCH

    --INFORMATIONAL
    SELECT * FROM #TempTableCopyList

    drop table #TempTableCopyList

   return 
END

Answer 1

exec dbo.sp_Copy_DB_Subset_Tables 'Server1\Instance','MainDB','Server1\Instance','MiniDB'

Try it with out the brackets ?