Solved

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

Posted on 2014-02-18
7
1,778 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

816 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

8 Experts available now in Live!

Get 1:1 Help Now