INSERT INTO OPENROWSET Syntax with Dynamic T-SQL

Question

I feel like I have read every single page online about how to dynamically create an Excel output from a T-SQL script. Here is what I have:

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

USE CommercialLending
GO
DECLARE @LoopCounter TINYINT = 1
DECLARE @LoopMaxCount TINYINT = (SELECT COUNT(DISTINCT OFFICER)
                                FROM CommercialLending.dbo.CMLTrial)
WHILE (1=1)
BEGIN
DECLARE @OfficerName VARCHAR(4000) = (
                                    SELECT OFFICER 
                                    FROM (SELECT DISTINCT OFFICER, ROW_NUMBER() OVER (ORDER BY OFFICER) AS rownumber 
                                            FROM CommercialLending.dbo.CMLTrial GROUP BY Officer) AS OFFICER 
                                    WHERE rownumber = @LoopCounter)
DECLARE @FileName varchar(400) = @OfficerName+ '.xlsx'
DECLARE @FullFileName varchar(400) = 'O:\MIS\Python\Programs\CommercialLending\'[email protected]
DECLARE @CopyFile varchar(800) = 'copy O:\MIS\Python\Programs\CommercialLending\Template.xlsx copy O:\MIS\Python\Programs\CommercialLending\' + @FileName
EXEC xp_cmdshell @CopyFile
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',Excel 12.0;Database='[email protected]+';'',''SELECT * FROM [Sheet1$])'' SELECT * FROM dbo.CMLTrial WHERE Officer='''[email protected]+''
EXEC (@sql)
SET @LoopCounter = @LoopCounter+1
IF (@LoopCounter > @LoopMaxCount)
    BREAK;
END

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'ad hoc distributed queries', 0
RECONFIGURE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0
GO

I know that my problem is in the line that assigns the INSERT INTO OPENROWSET string to the @sql variable:

SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',Excel 12.0;Database='[email protected]+';'',''SELECT * FROM [Sheet1$])'' SELECT * FROM dbo.CMLTrial WHERE Officer='''[email protected]+''

For the life of me I cannot figure out where my syntax error is. Can someone help me figure it out, and also, if possible, provide a guide for figuring out the standard syntax for using the INSERT INTO OPENROWSET as a string in a variable?

Solution

Try this:

    SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='[email protected]+''',''SELECT * FROM [Sheet1$]'') SELECT * FROM dbo.CMLTrial WHERE Officer LIKE '''[email protected]+''''

I always do a PRINT @sql when working with dynamic scripts to see how the actual query will look like. It's easier to troubleshoot and saves me plenty of grief later on.