?
Solved

Parameterized OPENROWSET Query

Posted on 2003-03-28
10
Medium Priority
?
1,123 Views
Last Modified: 2008-02-01
Hi,

I am trying to write parameterized openrowset query for importing excel spreadsheets into a sql database.  So far I have been able to achive this using a hard coded version.  However, I really need the code to be dynamic so I require parameters.  My attempt so far is as follows:

--CREATE PROCEDURE ImportTable

DECLARE @TableName AS VARCHAR(2000)
DECLARE @Path AS VARCHAR(2000)
DECLARE @Sheet AS VARCHAR(2000)
DECLARE @SQL AS VARCHAR(4000)
--AS
-- Variables Set for testing

SET @TableName = 'TEMP'
SET @Path= 'C:\Projects\Table1.xls'
SET @Sheet= 'Sheet1$'

SET @SQL='
SELECT *  INTO ' + @TableName + ' FROM OPENROWSET (Microsoft.Jet.OLEDB.4.0 '','' Excel 8.0;Database=' + @Path + ' ","  Select * FROM [' + @Sheet + '] )'

EXEC (@SQL)

This query completes but does not run.  Does anyone have any ideas as to why this is or if indeed it is possible to use parameters using the openrowset function.

Cheers!

ma0168
0
Comment
Question by:ma0168
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 8223907
SET @SQL='
SELECT *  INTO ' + @TableName
+ ' FROM OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'' ,''Extended properties=Excel 8.0;Data Source="'
+ @Path + '"'')...['+ @Sheet + ']'
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8223909
0
 

Author Comment

by:ma0168
ID: 8224046
Hi ispaleny

I have tried the code snippet that you gave me but it now gives me this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Failure creating file.]


Have you any ideas as how to resolve it.

Thanks for your help!

Nova30

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:ispaleny
ID: 8224383
Tested, works.

Test your source file, try this

SET @SQL=
'
exec master.dbo.xp_cmdshell ''copy '+@Path+' NULL''
'
EXEC (@SQL)
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8224885
Try to install a newer MDAC on the server.
(Tested with MDAC 2.7 and unlocked provider)
0
 

Author Comment

by:ma0168
ID: 8225400
ispaleny

As I am very new to SQL I am unsure as to what the code in the last snippet does!  All I want to be able to do is have a stored procedure which I can use to import excel and dbf files.  I recognize that the DTS package can do this but eventually I want to be able to run the stored procedure through a stand alone vb application.

Thanks for your help so far!

ma0168
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 200 total points
ID: 8225629
Q. I am unsure ...
A. It is a SQL thing only partially.
master.dbo.xp_cmdshell is a admistrative extended stored procedure running CMD.EXE (W2K command console).
I tried to check if your server can see your file.
"COPY YourFile.XLS NULL" copies your file to a standard black-hole OS device. On success it returns "1 file copied."

My sister is learning these things together with UNIX bash scripts in a newbie course.
0
 

Expert Comment

by:CleanupPing
ID: 9275471
ma0168:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183820
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: ispaleny http:#8223907

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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