Pass parameters for filename and spreadsheet name to SQL Server SELECT with OpenRowset

Several part question, part 1

I would like to pass parameters for the filename and spreadsheet name into this SQL Statement in a T-SQL stored procedure, from Access.

SELECT * INTO #AccountLedger
FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0',
 'EXCEL 12.0 xml;HDR=YES;DATABASE=S:\Acct_Ledger\December Net.xlsx',
 'SELECT * FROM [Sheet1$]')

So, what I need is:
1.  the syntax for the Access side to call the SP and pass the appropriate values, and
2.  the syntax for the Stored procedure to receive the parameters and perform the upload to the temp table.

Once I get the data into #AccountLedger, I'll have some other steps to perform to massage the data before it gets pushed into tbl_Account_Ledger, but this is the critical part of that equation.  

If I can simply call this stored procedure from Access and pass in the filename and sheetname, I'll be on my way.  Actually, I might also need to pass in the parameters for the ACE version and the Excel file format, since I've got users that will be running this from a variety of Windows / Office combinations.
LVL 50
Dale FyeAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
You are right, I should have seen that.  You have a couple of choices:
Set @SQL = 
'SELECT * INTO #AccountLedger 
FROM OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
''EXCEL 12.0 xml;HDR=YES;DATABASE=' + @FilePath + @FileName + ''',
''SELECT * FROM [' + @SheetName + ']'');SELECT TOP 10 * FROM #AccountLedger; DROP TABLE #AccountLedger'

EXEC (@SQL) 

Open in new window

A better approach is to first define the table and then INSERT INTO it, as in
/*Create the temporary table*/
CREATE TABLE #AccountLedger(
		Col1 integer,
		Col2 varchar(50),
		...
		Coln varchar(100)

Set @SQL = 
'INSERT INTO #AccountLedger (Col1, Col2, Col3...)
SELECT Col1, Col2, Col3,... 
FROM OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
''EXCEL 12.0 xml;HDR=YES;DATABASE=' + @FilePath + @FileName + ''',
''SELECT * FROM [' + @SheetName + ']'')'

EXEC (@SQL) 

SELECT TOP 10 * FROM #AccountLedger

DROP TABLE #AccountLedger

Open in new window

0
 
Anthony PerkinsCommented:
I would like to pass parameters for the filename and spreadsheet name into this SQL Statement in a T-SQL stored procedure
You cannot do that without resorting to Dynamic SQL.
0
 
Dale FyeAuthor Commented:
That's fine, ac.  I've got the rest of the T-SQL working, just need to know how to pass those two values in, build that dynamic SQL string, and kick it off.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Anthony PerkinsCommented:
Your Stored Procedure should look like this:
CREATE PROCEDURE usp_YourStoredProcedureNameGoesHere
		@FileName nvarchar(200),
		@SheetName nvarchar(200)

AS

DECLARE @SQL nvarchar(MAX)

SET NOCOUNT ON

SET @SQL = 
'SELECT * INTO #AccountLedger
FROM OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
 ''EXCEL 12.0 xml;HDR=YES;DATABASE=' + @FileName + ''', 
 ''SELECT * FROM [' + @SheetName + ']'')'

 EXEC (@SQL)

Open in new window

0
 
Dale FyeAuthor Commented:
ac,

I modified this to pass in the FilePath and the FileName.  
I'm getting an error message: "Invalid object name '#AccountLedger',  pointing at the last line of:

use Accounting_Ledger

Declare @FileName nvarchar(200)
Declare @FilePath nvarchar(200)
Declare @SheetName nvarchar(200)
Declare @SQL nvarchar(MAX)

set @FileName = '12 2013 December Gross_Test.xlsx'
Set @FilePath = 'S:\Dale Fye\Operations\'
Set @SheetName = 'EXCEL_TRANS_5648836127$'

/*Import the data into temporary table*/
Set @SQL =
'SELECT * INTO #AccountLedger
FROM OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
''EXCEL 12.0 xml;HDR=YES;DATABASE=' + @FilePath + @FileName + ''',
''SELECT * FROM [' + @SheetName + ']'')'

EXEC (@SQL)
SELECT TOP 10 * FROM #AccountLedger   <==== Error occurs on this line
0
 
Dale FyeAuthor Commented:
ac,

Will take a look at that method.  Am also toying with writing the SQL in Access, and passing it to SQL Server as a pass-through query.

Thanks,

Dale
0
 
Dale FyeAuthor Commented:
AC,

Thanks for the help with this.

The several examples you provided gave me what I needed to construct the SQL String in Access and run a pass-thru query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.