Solved

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

Posted on 2014-02-18
7
1,693 Views
Last Modified: 2014-02-25
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.
0
Comment
Question by:Dale Fye (Access MVP)
  • 4
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39869194
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39869901
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39871995
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39878502
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39880019
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39880065
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
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 39887686
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now