Printable View
Hi can anybody give suggestion to the problem under stated :
(Note : cuttent table is in sqlserver)
I have a table for which some of the columns has to beported from the table to a text file like
table1
-------
col1 col2 col3
10 e10 f10
20 e20 f20
30 e30 f30
and my text-file should look like
e20 is on f20
e30 is on f30
(condition porting like utl-file package in Oracle)
Thanking u,
Regards,
Ramani Ranjan Nayak,
The following code should help you:
'Using FileSystemObject and TextStream for I/O
Dim oFS as Scripting.FileSystemObject
Dim oStream as Scripting.TextStream
'Use ADO Connection/Recordset to get data
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
set oFS = new Scripting.FileSystemObject
set oStream = oFS.CreateTextFile("output.txt")
set cn = new ADODB.Connection
cn.Open "SomeDSN"
set rs = cn.Execute("SELECT col2,col3 FROM table1")
Do Until rs.eof
'Write the text file
oStream.WriteLine rs("col2") & " is on " & rs("col3")
rs.MoveNext
Loop
'Clean Up Here
rs.Close
set rs = nothing
cn.Close
set cn = nothing
oStream.Close
set oStream = nothing
set oFS = nothing
Hope that helps
your server is MS Sqlserver, right?
run ISQL from an NT command line
ISQL -Q "select col2 + ' is on ' + col3 from table" -oyouroutputfilenameGoesHere.txt ...other ISQL command line parameters (like -U, -S -d)
Hi Lothar
Thanks For the suggestion, but i need to automate the process through
procedure either Stored Procedure Or Trigger So that it can be interactive
Can You give suggestion to this problem.
(Note : cuttent table is in sqlserver)
I have a table for which some of the columns has to beported from the table to a text file like
table1
-------
col1 col2 col3
10 e10 f10
20 e20 f20
30 e30 f30
and my text-file should look like
e20
f20
e30
f30
(condition porting like utl-file package in Oracle)
Thanking u,
in that case, I'd rather use Kyle Burn's solution: write a vb program.
You could use the xp_cmdshell extended SP to start the ISQL command line that I specified.
If your SQL server is version 7, I'd use DTS (data transformation service) instead.