Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-18
7
Medium Priority
?
2,073 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
  • 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 49

Author Comment

by:Dale Fye
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Author Comment

by:Dale Fye
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 2000 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 49

Author Comment

by:Dale Fye
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 49

Author Closing Comment

by:Dale Fye
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

971 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