Solved

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

Posted on 2014-02-18
7
1,821 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 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