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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

how to pass param to OpenRowset in my store?

My code is:
DECLARE curTEMP CURSOR  FOR      
SELECT *
FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};  DefaultDir=C:\Transfer\Test;','select * from  Case_DD.txt')
OPEN curTEMP

I know the technique to pass param to OpenRowset by pass all the sellect as a string then Exec that query but in my store as you can see, it not allow. Anyone have idea to solve my prob. Many thnx
trongtb
0
software22
Asked:
software22
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Anthony PerkinsCommented:
Just a friendly reminder, please maintain this abandoned question:
12/28/2004 500 How to implement Drag and Drop with cobo...  Open VB Controls
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Controls/Q_21255965.html

Thanks.
0
 
software22Author Commented:
thanks acperkins
trongtb
0
 
OlegPCommented:
1) Use local temporary table for inserting of  your SELECT by EXEC statment
2) Use cursor with temporary table
0
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.

 
Eugene ZCommented:
To use parameters, use OPENROWSET within an EXEC() statement:

more see:

OpenRowSet w/Parameters
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=256&lngWId=5
0
 
software22Author Commented:
Thanks for your coment OlegP and EugeneZ,
I know the technique to pass param to OPENROWSET as i said in my question. Any way your comment helps me much.
Yeah, use temp table that sound nice but i frequently work with many record(>10K each table) So that can affect to performent.
trongtb
0
 
Eugene ZCommented:
software22:
Please clarify your question

>...in my store as you can see, it not allow ..<?


0
 
software22Author Commented:
I'm so sorry for my poor in english and explain also. I mean that
SELECT * FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};  DefaultDir=C:\Transfer\Test;','select * from  Case_DD.txt')

This Statement can be execute well but i need to pass parameter to this Query like this

SELECT * FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};  xxxxxxxx;','xxxxxxxxxxxxxx')

this problem can be solve by convert the query to string and exec that
Set @str= 'SELECT * FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};  xxxxxxxx;','xxxxxxxxxxxxxx')'
exec @str
However i need it result for a curso then should the query be, because if i use:
DECLARE curTEMP CURSOR  FOR Exec @str
SQL Server not allow
Hope that is clear.
Thanks in Advance
trongtb
0
 
OlegPCommented:
Statement select * from  [Case_DD.txt] must have named field like  for example select VL from  [Case_DD.txt]'

the example work I tested

Declare @DR       varchar(1000)
Declare @QW       varchar(1000)
Declare @Name       varchar(100)

Set @DR='Driver={Microsoft Text Driver (*.txt; *.csv)};  DefaultDir=C:\Transfer\Test;'
Set @QW='select * from  [Case_DD.txt]'

Exec('Declare Cur Cursor For SELECT [VL] FROM OpenRowset(''MSDASQL'', '''+@DR+''','''+@QW+''')')
Open Cur
FETCH NEXT FROM Cur INTO @Name
WHILE @@FETCH_STATUS = 0
  Begin
    Print @Name
    FETCH NEXT FROM Cur INTO @Name
  End
CLOSE       Cur
DEALLOCATE Cur


0
 
software22Author Commented:
Many thnx OlegP,
That 's worked
trongtb
0

Featured Post

Independent Software Vendors: 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!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now