Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vbscript sql query write to file

Posted on 2010-11-22
13
Medium Priority
?
833 Views
Last Modified: 2012-05-10
Hi,

I have a vbscript used to run a sql query...
In the past I have the whole results  placed in memory  (variable) and then write it to a file in the end.
I need to change this to write to the file as it reads it (writeline...comma separated)

The sql query returns 10 columns and hundreds of rows

anyone have any idea
(the old  piece of code for the sql query section is below)
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 0              
objConn.CommandTimeout = 0
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open StrConnectStringset objRS = objConn.Execute (arrlist)
objRS.MoveFirst

intCount = 0
Do While Not objRS.EOF
nRow = nRow + 1
For i = 0 to objRS.Fields.Count - 1 
stat1 = stat1 & objRS.Fields(i) & "," 
f.WriteLine stat1
Next

Open in new window

0
Comment
Question by:neoptoent
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34187922
You could always just directly export it using bcp out.

http://msdn.microsoft.com/en-us/library/ms162802.aspx
0
 

Author Comment

by:neoptoent
ID: 34188149
Nah,

 I need to use a specific query that changes earlier in the script..
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34188188
You can do that too. Let me know if you change your mind.
0
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 
LVL 7

Expert Comment

by:mmr159
ID: 34188204
So you have it already working (building the entire result set, then writing the file)??

All you have to do now is to write to the file after each row.  I'm not sure what else you need.


http://blogs.msdn.com/b/webdav_101/archive/2008/03/12/howto-write-a-file-using-vbscript.aspx
0
 

Author Comment

by:neoptoent
ID: 34188550
I cant firgure out where to do the writeline and then have it go to the next row
0
 
LVL 7

Expert Comment

by:mmr159
ID: 34188640
From the bit of code you posted, it appears like you're close.  Please post all of the code... at least the part that includes all of the FSO stuff.
0
 

Author Comment

by:neoptoent
ID: 34188938
I have the fso part
Set f = fso.OpenTextFile(SMPath & "\Provider\DB\AllSecurityEventsalert.log", 2 , True)

The issue I am having is getting the writeline to write line by line.... as opposed to populatin the stat1 variable with all the results of the squery then writing it once to the file

0
 
LVL 7

Expert Comment

by:mmr159
ID: 34191399
You have two loops, one for going over the number of records, another inside that loop to go over the number of columns.

1) You should consider eliminating the second loop.  You should instead concatenate the desired fields in one line.  Like

for each record
    record = rs.fields("column1") & "," & rs.fields("column2") & "," & rs.fields("columnX")
next

Doing this will allow your code to survive certain types of schema changes, and is simply the better practice.  You really should never reference fields by ordinal position.

2) It looks like the code does write to file as the recordset is read.  But looking at the code, I would imagine the output looks something like this:

colA, -- record 1
colA,colB, -- record 1
colA,colB,colC, -- record 1
colA,colB,colC,etc... -- record 1
colA, -- record 2
colA,colB, -- record 2
colA,colB,colC, -- record 2
colA,colB,colC,etc... -- record 2

To only write like per record, move the WriteLine under the Next.  You will also have to clear the stat1 variable after that record has been written.

0
 

Author Comment

by:neoptoent
ID: 34197879
Currently what happens is the query is executed (I have 13 columns in the select) it put them line after line into stat1 and then writes stat1 to a text file...

I am not sure I understand where in the code to force it to write line after line into the file
0
 
LVL 7

Accepted Solution

by:
mmr159 earned 1500 total points
ID: 34199449
OK, I think we still have a misunderstanding.

Are you asking to have the code physically write each line to a file *during* execution, so that you might see the code performing while it is running?

OR

Is there simply something wrong with the output, like the records or columns are not properly written by line?

If your issue is the first one, then I don't particularly see the problem.  As far as I know, WriteLine will write in real time.  If it is not, the only thing I can suggest you try is to open, WriteLine, then close the file object every record in the loop.

If your issue is the second one, let me know, and see some more of the code.  To me, you would want something more like the following:


Do While Not objRS.EOF
   variable = objRS.Fields("first_column") & "," & objRS.Fields("second_column") & etc...
   f.WriteLine variable
Next

Open in new window

0
 

Author Comment

by:neoptoent
ID: 34202047
The sql query streams into a variable called stat1 right now and then write to a file at the end.
I want the query to writeline to the event file instead of streaming into a variable
0
 
LVL 7

Expert Comment

by:mmr159
ID: 34206325
It *should not* be writing the file at the "end".

f is your file object, right?  When you call WriteLine, it should write what you ask it to right then and there.  This is why I don't understand why you're having a problem.

Without the entire code, or preferably a small but complete example that demonstrates your problem, I can't help.
0
 

Author Closing Comment

by:neoptoent
ID: 34537282
Some help
0

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

722 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