Can someone help me on OpenRowset query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can someone help me on OpenRowset query.

Author

Topic

anilkumar3
Starting Member

3 Posts

Posted - 2011-01-03 : 04:09:35

I have almost 100 text files in my e: drive,I use the below query to read thetext file from SQL, i have all the text file names in a temp table ao trying to have a cursor which will dynamically select the file name one by one and read the data.

but the below query throws error, i guess the problem is with the single quotes or + symbol.

declare @@filename nvarchar(50)
set @@filename = 'ZA_REC_000_D_T_20101221.txt'
--select @@filename

select @@filename as Source,* from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=e:\AllCountryFiles_Test\AM;
Extended properties=''ColNameHeader=True;Format=TabDelimited;''','''' + 'select * from @@filename')

i want to read the filenames using the @@filename variable.

Can someone help me to sort out this issue.

Anil Kumar

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-03 : 04:25:53

quote:
but the below query throws error

You should always post the error message...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:37:09

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

There is an exmaple of how to use it dynamically

Madhivanan

Failing to plan is Planning to fail

anilkumar3
Starting Member

3 Posts

Posted - 2011-01-03 : 05:46:00

See this is my query:

declare @filename varchar(2000)
declare @selectsql varchar(2000)
set @filename = 'ZA_REC_000_D_T_20101221.txt'
--select @filename
SET @SelectSql = 'SELECT * FROM + @filename'
--select @SelectSql

select @filename as Source,* from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=e:\AllCountryFiles_Test\;
Extended properties=''ColNameHeader=True;Format=TabDelimited;''',''' + @SelectSql + ''')

The error follows:

An error occurred while preparing the query "' + @SelectSql + '" for execution against OLE DB provider "MSDASQL" for linked server "(null)".

Anil Kumar

anilkumar3
Starting Member

3 Posts

Posted - 2011-01-04 : 22:57:31

Your inputs are appreciated.. need help on this topic

Anil Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 03:02:34

Refer this logic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=4#239916

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 03:30:01

This:


SET @SelectSql = 'SELECT * FROM + @filename'

looks like it should be this:


SET @SelectSql = 'SELECT * FROM ' + @filename

but I very much doubt that has anything to do with the problem, and looks more likely to indicate that you do not understand how this is supposed to work - thus see madhivanan link - if you have any questions about that link then please ask.

The documentation states that "OPENROWSET does not accept variables for its arguments" so you can NOT use the expression


'' + @SelectSql + ''

in your code - the whole statement will have to be constructed as dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources