Unable to import to Excel using OPENROWSET in SQL Server 2008 R2

Question

I have to copy an Excel template to another folder to be able to import the data. The followings are two SQL statements that I'm using:

Statement1:

exec xp_cmdshell 'copy C:\inetpub\wwwroot\app_data\export_templates\myExport.xlsx C:\inetpub\wwwroot\app_data\tmp\myExport130301172218.xls'

Statement2:

insert into 
OPENROWSET('Microsoft.ACE.OLEDB.12.0'
    , 'Excel 12.0;Database=C:\inetpub\wwwroot\app_data\tmp\myExport130301172218.xls;'
    , 'SELECT * FROM [Sheet1$]')  
SELECT * FROM vw_shrub 

If I execute the above SQL Statements separately, they work fine. But if I execute them together, it raises the following error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.".

Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

The problem is I want to pass them both from my application as single a request. Hence, I have to execute them together. I found that i can execute them together in Management Studio, if I add GO between them. But I cannot use GO when I pass the request from my application.

Answer 1

An alternative could be make this two lines into two different steps in a SQL Server Agent job and execute it with a single instruction:

sp_start_job nameOfYourJob

The two steps of the job act like there was a GO in between.