file-io (sqlserver)

[Ramani Ranjan]

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,

[Kyle Burns]

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

[Lothar Haensler]

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)

[Ramani Ranjan]

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,

[Lothar Haensler]

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.