Solved

Pass file name variable to select from OpenDataSource

Posted on 2006-11-03
16
1,279 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
Technology Partners: 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 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 100 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 400 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Help finding duplicate records 5 26
IF SQL Query 12 29
Convert time stamp to date 2 58
calculate running total 8 18
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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 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