Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Parameterized OPENROWSET Query

Posted on 2003-03-28
10
Medium Priority
?
1,129 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
9 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

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.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.
Suggested Courses

578 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