Open rowset and cursor

Question

I created some codes to retrieve passed excel file sheet and column names. Then I want to insert data in each sheet-name into a table with a cursor because there might be more than one sheet. I think the problem is that cursor doesn't set variable @sheetname properly. What is the problem? Note: below query is modified and is not the one I execute in SQL. I have problem with below code:

----------------Create Excel info table------------------------
    DECLARE @SheetNames TABLE (
        TABLE_CAT NVARCHAR(50) NULL
       ,TABLE_SCHEM NVARCHAR(50)
       ,table_name NVARCHAR(50)
       ,TABLE_TYPE NVARCHAR(50)
       ,REMARKS NVARCHAR(50)
    )

    DECLARE @ColNames TABLE (
        TABLE_CAT NVARCHAR(30)
       ,TABLE_SCHEM NVARCHAR(30)
       ,table_name NVARCHAR(30)
       ,column_name NVARCHAR(30)
       ,data_type SMALLINT
       ,type_name NVARCHAR(30)
       ,COLUMN_SIZE INT
       ,BUFFER_LENGTH INT
       ,DECIMAL_DIGITS SMALLINT
       ,NUM_PREC_RADIX SMALLINT
       ,NULLABLE SMALLINT
       ,REMARKS NVARCHAR(254)
       ,COLUMN_DEF NVARCHAR(254)
       ,SQL_DATA_TYPE SMALLINT
       ,SQL_DATETIME_SUB SMALLINT
       ,CHAR_OCTET_LENGTH INT
       ,ORDINAL_POSITION SMALLINT
       ,IS_NULLABLE NVARCHAR(254)
       ,SS_DATA_TYPE TINYINT
    )


    DECLARE @ExcelInfo TABLE (
        SheetName NVARCHAR(50)
       ,ColName NVARCHAR(50)
    )

    DECLARE @FILEPATH NVARCHAR(500) = @Path + '\' + @File;

    DECLARE @linkedServerName NVARCHAR(30) = 'x'


    -- Remove existing linked server (if necessary)
    IF EXISTS (SELECT
                NULL
            FROM sys.servers
            WHERE Name = @linkedServerName)
    BEGIN
        EXEC sp_dropserver @server = @linkedServerName
                          ,@droplogins = 'droplogins'
    END



    EXEC sp_addlinkedserver @server = @linkedServerName
                           ,@srvproduct = 'ACE 12.0'
                           ,@provider = 'Microsoft.ACE.OLEDB.12.0'
                           ,@datasrc = @FILEPATH
                           ,@provstr = 'Excel 12.0;HDR=Yes'

    -- Grab the current user to use as a remote login
    DECLARE @suser_sname NVARCHAR(256) = 'SA'

    -- Add the current user as a login
    EXEC sp_addlinkedsrvlogin @rmtsrvname = @linkedServerName
                             ,@useself = 'false'
                             ,@locallogin = @suser_sname
                             ,@rmtuser = NULL
                             ,@rmtpassword = NULL

    -- Return the table/column info

    INSERT INTO @SheetNames
    EXEC sp_tables_ex @linkedServerName

    INSERT INTO @ColNames EXEC sp_columns_ex @linkedServerName
    ;

    WITH a
    AS
    (SELECT
            *
        FROM @SheetNames 
        
    INSERT INTO @ExcelInfo

        SELECT
            a.table_name
           ,sn.column_name
        FROM @ColNames sn
        JOIN a
            ON a.table_name = sn.table_name


----------------End of Excel info table creation------------------------
SELECT * FROM @ExcelInfo 
WHERE SheetName LIKE '''Er%'
    DECLARE @Ericsson_Availability TABLE (
        [Date] DATETIME
       ,ElID NVARCHAR(50)
       ,ElID1 NVARCHAR(50)
       ,[TCH NVARCHAR(10)
       ,[TCH2 NVARCHAR(50)
    );
    DECLARE @SHEETNAME NVARCHAR(500)
    BEGIN TRY 
    DECLARE FetchNames CURSOR LOCAL FOR
    
    SELECT DISTINCT
        SheetName
    FROM @ExcelInfo
    WHERE SheetName LIKE '''Er%'
    OPEN FetchNames;
    FETCH NEXT FROM FetchNames INTO
    @SHEETNAME
    WHILE @@fetch_status = 0
    BEGIN
    
    INSERT INTO @Availability
    EXEC ('
SELECT  *
FROM  Openrowset(''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0 Xml;HDR=YES;Database=' + @FILEPATH + ''',
''
SELECT     
      *

FROM    [' + @SHEETNAME + ']
'') oro')

 
    FETCH NEXT FROM FetchNames INTO
    @SHEETNAME
    END
    CLOSE FetchNames
    DEALLOCATE FetchNames

    
    
    
        SELECT
            *
        FROM  @Availability;
        END TRY
        BEGIN CATCH
        PRINT ERROR_LINE()
        PRINT ERROR_MESSAGE()
        PRINT ERROR_STATE()
        END CATCH
END;

Answer 1

I found out how to fix it! its about string concatenation concepts. before insert into (openrowset) part of cod i should use a replace function in order to change the value of @sheetname in the way that have single quotation before and after when openrowset part wants to run. something like this:

SET @SHEETNAME = REPLACE(@SHEETNAME, '''', '''''')