?
Solved

how to pass param to OpenRowset in my store?

Posted on 2005-03-27
9
Medium Priority
?
690 Views
Last Modified: 2012-06-21
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
Comment
Question by:software22
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13641545
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
 

Author Comment

by:software22
ID: 13641744
thanks acperkins
trongtb
0
 
LVL 6

Expert Comment

by:OlegP
ID: 13642909
1) Use local temporary table for inserting of  your SELECT by EXEC statment
2) Use cursor with temporary table
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 43

Expert Comment

by:Eugene Z
ID: 13642973
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
 

Author Comment

by:software22
ID: 13649024
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13651583
software22:
Please clarify your question

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


0
 

Author Comment

by:software22
ID: 13660402
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
 
LVL 6

Accepted Solution

by:
OlegP earned 750 total points
ID: 13660611
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
 

Author Comment

by:software22
ID: 13679070
Many thnx OlegP,
That 's worked
trongtb
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

764 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