[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Pass file name variable to select from OpenDataSource

Posted on 2006-11-03
16
Medium Priority
?
1,300 Views
Last Modified: 2008-01-09
This works as coded below but I need to pass it the file name each time

DECLARE @effdat datetime,@clicod as varchar(30),@datsetcod as varchar(30),@yeartag as char(2),@filenam as varchar(255),@filepath as varchar(300)
SET @effdat =getdate()
SET @filenam = 'INV10k.xls'
SET @filepath = 'C:\share\archive\' + @filenam
print @filepath
--delete from INVDETAILINPUT where FILENAM = @filenam
--insert INVDETAILINPUT
SELECT @effdat,[Period Code],[Fiscal Year],[Market Price],NULL,NULL,@clicod,@datsetcod,NULL,@yeartag,@filenam
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
      'Data Source="C:\share\archive\INV10k.xls";Extended properties=Excel 8.0')...Sheet1$
0
Comment
Question by:MacNuttin
[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
  • 8
  • 7
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872090
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872105
Do you see 'Data Source="C:\share\archive\INV10k.xls";  Can you write for me so 'Data Source="C:\share\archive\' + @filenam +'";
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872119


SELECT *from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
      'Data Source="C:\sampledata.xls";Extended properties=Excel 8.0')...Sheet1$

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872120
sorry ..
Exec('SELECT * FROM  OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',
      ''Data Source="'+@filepath+'";Extended properties=Excel 8.0'')...Sheet1$')

0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872160
getting error Line 1: Incorrect syntax near '4'.  --doesn't like OLEDB.4.0'',

DECLARE @effdat datetime,@clicod varchar(30),@datsetcod  varchar(30),@yeartag varchar(2),@filenam varchar(255),@filepath varchar(300)
SET @effdat =getdate()
SET @filenam = 'INV10k.xls'
SET @clicod = '20034'
SET @datsetcod = '2003456'
SET @yeartag ='06'
SET @filepath = 'C:\share\archive\' + @filenam
print @filepath
--delete from INVDETAILINPUT where FILENAM = 'filename'
--insert INVDETAILINPUT
EXEC('SELECT '+@effdat+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+@clicod+','+@datsetcod+',NULL,NULL,'+@yeartag+','+@filenam+'
from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
      ''Data Source="'+@filenam+'";Extended properties=Excel 8.0'')...Sheet1$')
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872280
HELP
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872299
works with select * but not with my variables Now I can't select @effdat for example
0
 
LVL 5

Assisted Solution

by:CIC Admin
CIC Admin earned 400 total points
ID: 17872362
You might want to try creating the SELECT statement as a variable and then just using that variable in the EXEC statment.  I have had similar problems in the past.

SET @sql = 'SELECT '+@effdat+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+@clicod+','+@datsetcod+',NULL,NULL,'+@yeartag+','+@filenam+'
from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+@filenam+'";Extended properties=Excel 8.0'')...Sheet1$'

EXEC(@sql)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872382
you need to convert the variables other than varchar to varchar
for example CAST(varchar,@effDat)
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872403
Did both  Still I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872417
can u post the query ?
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872431
DECLARE @effdat varchar(8),@clicod varchar(30),@datsetcod  varchar(30),@yeartag varchar(2),@filenam varchar(255),@filepath varchar(300),@SQL varchar(1000)
SET @effdat =convert(varchar,getdate())
SET @filenam = 'INV10k.xls'
SET @clicod = '20034'
SET @datsetcod = '2003456'
SET @yeartag ='06'
SET @filepath = 'C:\share\archive\' + @filenam
SET @SQL ='SELECT '+ @effDat +',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+ @filenam +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$'

delete from INVDETAILINPUT where FILENAM = @filenam
--insert INVDETAILINPUT
/*EXEC('SELECT '+ @effdat +',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+ @filenam +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$')
*/
Exec(@SQL)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872439
SET @SQL ='SELECT '+''''+ @effDat+''''+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+ @filenam +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$'
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872460
what was the point of that? any way the error changed:
The column prefix 'INV10k' does not match with a table name or alias name used in the query.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1600 total points
ID: 17872468
MacNuttin,
> what was the point of that?

You need to put the Dates (Nov 04 ) inside the Quotes , samething you need to do with the @fileName tooo

SET @SQL ='SELECT '+''''+ @effDat+''''+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+''''+ @filenam+'''' +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$'



0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872474
fantastic! That was it!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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