Solved

VBscript SQL query hangs script

Posted on 2010-11-12
65
1,392 Views
Last Modified: 2012-05-10
Hi,

I have a vbscript that querys a SQL database and put the records in to a text file. (CSV)
the issue I am having is if there are too many records to pull the script just never end and hogs 50% of the cpu

I tried to put in a an commandtimeout thinking it would cause the timeout and end the script, but it did nothing

IF there are not that many records to query, it works fine.. it just happens when there are thousands of records

I need a way to have the script stop so that it doesnt continue to hold the processor

The arrlist has my sql query in it
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 15
objConn.CommandTimeout = 10
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open StrConnectString
set 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) & "," 
  Next
  stat1 = stat1 & vbcrlf
  objRS.MoveNext
Loop

objRS.MoveFirst
Do While Not objRS.EOF
  nRowC = nRowC + 1
  if nRowC = nRow then
  End If
  objRS.MoveNext
Loop
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing
err.clear

data.WriteLine stat1
if LastEvent <> "" then
  Set f2 = fso.OpenTextFile(c:\test.log",2 , True)
  write.writeLine LastEvent
end if
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 5
objConn.CommandTimeout = 5
err.clear
Set objRS = CreateObject("ADODB.Recordset")
Set objTextFile = objFSO.OpenTextFile(c:\test1.log", 1)
Do Until objTextFile.AtEndOfStream
  strread = strread & objTextFile.ReadLine & vbTab
Loop
objTextFile.Close

Open in new window

0
Comment
Question by:neoptoent
  • 24
  • 19
  • 4
  • +5
65 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 34122989
I take it this is vbscripting not vba code within access

if that is the case then see if you can create an access object instead and try to use the existing commands like TransferText

eg

    Dim acc As Object
   
    Set acc = CreateObject("Access.Application")
   
    acc.OpenCurrentDatabase "c:\mydatabase.mdb"
   
    acc.DoCmd.TransferText 2, , "myquery", "c:\temp\output.csv", -1
   
    acc.CloseCurrentDatabase
    Set acc = Nothing


If done within access, drop the object creation, just use

DoCmd.TransferText acExportDelim, , "myquery", "c:\temp\output.csv", -1
0
 

Author Comment

by:neoptoent
ID: 34123173
This is just a vbscript that runs on a schedule... a .vbs
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34123262
so try what I suggested first. It dumps the query "myquery" to a csv file in c:\temp\output.csv
last argument -1 represents True which is to show column headers. Change to 0 if not required
0
 

Author Comment

by:neoptoent
ID: 34123741
I need to keep my code the same it runs querys based on differernt variables..

I just need a way to say if the sql select takes longer than 1 minute stop the script
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34126360
I dont know I though commandtimeout would do it also

there is a timeout for cscript (which I assume you use to run your vbs files)

if you pass in /t then a value in seconds, that is your cscript (vbscripting) timeout

Usage: CScript scriptname.extension [option...] [arguments...]

Options:
 //B         Batch mode: Suppresses script errors and prompts from displaying
 //D         Enable Active Debugging
 //E:engine  Use engine for executing script
 //H:CScript Changes the default script host to CScript.exe
 //H:WScript Changes the default script host to WScript.exe (default)
 //I         Interactive mode (default, opposite of //B)
 //Job:xxxx  Execute a WSF job
 //Logo      Display logo (default)
 //Nologo    Prevent logo display: No banner will be shown at execution time
 //S         Save current command line options for this user
 //T:nn      Time out in seconds:  Maximum time a script is permitted to run
 //X         Execute script in debugger
 //U         Use Unicode for redirected I/O from the console


0
 

Author Comment

by:neoptoent
ID: 34133057
It is scheduled and just launches a vbs script.
is there a way i can put something in the script to stop the script after 3 minutes?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34190218
First let's clarify what exactly is taking too long: the query in sql server, or post-query processing in the script.

If it's the query, then you can see if you can optimize it, for example by running Index Tuning Wizard. Or you can simply limit the number of the records it returns using clause TOP, such as

SELECT TOP 1000 * FROM .....

If it's the latter, i.e. the query is fast, but it's the processing in the query that is taking too long, then you can implement a simply counter in the vbsript code:

Do While Not objRS.EOF
  nRow = nRow + 1
  if nRow t> 10000 then exit do
.....


Finally, you might want to consider running sql server bcp utility instead of your script - most likely it would be much faster than vbscript.

0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34190238
correction in the above example:

if nRow > 10000 then exit do
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34190252
neoptoent,

I need to keep my code the same it runs querys based on differernt variables..

Are the variables passed as part of the commandline that executes the script?

It is scheduled and just launches a vbs script.

How is it scheduled?  (I.e., is it scheduled via Windows Task Scheduler or what?)

In looking at your provided code, I see no reference to LastEvent prior to the following IF statement:

if LastEvent <> "" then
Set f2 = fso.OpenTextFile(c:\test.log",2 , True)
write.writeLine LastEvent
end if


Could this be an omission of code or is it an oversight/bug in the code?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34190335
Why are you using VBScript to export data to a CSV file when you could do it in a one bcp command line, which will never hang and be incomparably faster?

>bcp database.dbo.tablename -o "c:\tmp\Export\filename.CSV" -c -T
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 34190768
i too would recommend bcp command line, which will never hang and be incomparably faster?

bcp yourdatabase.dbo.yourtablename -o "c:\YOURDIRECTORY\yourfilename.CSV" -c -T
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34190917
The only reason I didn't chime in initially with a recommendation for using either SSIS or BCP was the following line from an earlier author's response:
I need to keep my code the same it runs querys based on differernt variables

Even if the variable need to be evaluated, the process could at least be moved to an SSIS package which would also give the author all manner of parameter checking, data reformatting, and notification possibilities.
0
 
LVL 28

Expert Comment

by:strickdd
ID: 34190994
I too would use the bcp utility. It is much faster and won't hang.

<insert copy and pasted code here> bcp yourdatabase.dbo.yourtablename -o "c:\YOURDIRECTORY\yourfilename.CSV" -c -T</insert>

Oh WAIT! That's what "vadimrapp1" said already!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34191050
Exporting data from SQL server using any script or language is the less performance wise way to do it. The VB script uses a loop to go through all the rows in the recordset and then another loop through the fields within th erecord set. This is what kills performance.

bcp uses a set based approach which is what the databases were designed for to provide the best performance.

Even if you need to parametrize the bcp command it would stiil be much easier and faster than using plain VBScript. You can do that in SQL code itself or in a DTS or SSIS or even in a VBScript.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34191178
As I said, the author's response regarding keeping the code the same was the only reason I didn't imediately suggest changing to BCP/SSIS.

However, at this point, since the suggestions/recommendations have been made, there is not a lot of point in continuing to beat this horse until the author responds. ;-)
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34191228
8080_Driver,

No offense here, but you should mind your own responses and not be worried by anybody else's. Everybody is trying to make their own point in regards to what they consider more relevant to solve the problem. The only judge should be the author and not the experts.

I only mentioned this because it will help to keep the posts cleaner.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34191238
I should probably also note that the requirement to simply cut off the "too long" execution looks... unusual.  I.e. the readiness to process only the fraction of the data. Usually the request would be "how to make this 1,000 times faster"
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34191464
@Zberteoc,

As you said, no offense intended but protracted repetitious recommendations without the author's response tend to just make it harder for the author to make a final decision with regard to accepting an answer.  The point of my post was that, should the author come back to visit this question, there are adequate responses recommending an alternative approach for the author to get the hint that, perhaps, an alternative approach should possibly be considered.  The key phrase there being "should the author come back to visit this question."  

As you also point out, the author is the final judge of the question.  However, once the evidence/arguments have been laid out, perhaps we should simply await the judge's decision or the judge's request for further information.

Of course, if you wish to continue to flog the dead horse in the hopes that it will make it go faster, then you are entirely welcome to do so. ;-)
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 34191491
strickdd - disagree i was agreeing with Zberteoc comment: but added my own code...
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34193333
I see these performance killers:
String concatenation (stat1) to build the entire CSV file
Wasted second pass through the recordset -- what is this supposed to accomplish?

Once you build a line, write it out and begin the next line's output with an empty stat1 variable.
0
 
LVL 28

Expert Comment

by:strickdd
ID: 34196181
cs97jjm3 - compare your post with Zberteoc's.


Zberteoc
   [snip], which will never hang and be incomparably faster?
cs97jjm3
   [snip], which will never hang and be incomparably faster?

    Identical, except for the part before the comma. However, if you look at Zberteoc's post, it is in the form of a question and therefore ends with a question mark (?). Now, your's on the other hand, is not a question, but ends with a question mark as well. Seems like a lazy copy and paste job.
    Now, let's look at the next part:

Zberteoc
   >bcp [DATABASE].[SCHEMA].[TABLENAME] -o "[FOLDERPATE]\[FILENAME].CSV" -c -T
cs97jjm3
     bcp [DATABASE].[SCHEMA].[TABLENAME] -o "[FOLDERPATE]\[FILENAME].CSV" -c -T

   I generalized both of your code statements and for all intents and purposes, they are identical. This is a little more understandable because with command-line utilities, there is a limitation on format and parameter order. Even so, that takes me back to the identical nature of the fist line of both your responses, why do you have a question mark when your post isn't even a question? Unless you meant to ask the poster which of the two options "will never hand" and which is "incomparably faster." I don't think that is what you meant though.
   Lastly, your post was almost a full hour after Zberteoc's, there is NO question that if either of you have the right answer, Zberteoc's should be accepted. If you were within a few minutes of his post then it would be understandable that you may not have refreshed before posting, but almost a full hour is unacceptable. It seems to me that you are simply trying to get partial credit by answering the same thing as someone else.
0
 

Author Comment

by:neoptoent
ID: 34197992
Hi,

Let me try and explain what my script does (top part not shown)..
The first part create 2 text files
1.  events
2. last event (populated after the first time the script runs) populated with 2 columns (5478451, 10/1/10 10:00:00pm)
3. variable "time" is created from the time in the "last event" file
The first part of the script create a sql query (arrlist) that reads from the last event table and creates a select statement for 14 colums and uses the variable "time" as where alerttime > "time"

It then returns the results line by line to a variable called stat1. (columns separated by ",")
(if i do a msgbox for stat1 i will see hundred of lines)
It then does a write to "events"
After that it reads the last line of the "events file" and write it to the "last event" table to be used the next time the query is run

I think I need to change it to write to the events to "events" file as the query runs, instead of putting it all into the memory..

I just cant figure out how to do that
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34198017
please post all your code
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34198530
I know what you're doing, instead of writing line by line into the file you are streaming the whole output to the stat1 stream variable and then you stream it into the file.

Still you definetely don't need the second loop to just get the last line that was added to the stat1 stream variable. What you need is to use another variable, called strLine, to be used in the first Do While loop  and build the output line into it in the For loop and when the line is finished just add it to the stat1 variable. This way when your first Do While is done you already will have in the strLine variable the last line added to the stat1 stream. You will also not need the nRow variable because you can get the total number or rows/lines from the Recordcount property of the recordset object.

So what you need is to modify your first loop to be like the one below and get rid of the second loop.
...
'declare the new strLine variable
dim strLine
intCount = 0
Do While Not objRS.EOF
  'nRow = nRow + 1 ' you don't need this counter; you can find the total number of records with objRS.Recordcount property
  'initialize the strLine variable to empty before each record
  strLine=""
  'build the strLine
  For i = 0 to objRS.Fields.Count - 1
    strLine = strLine & objRS.Fields(i) & "," 
  Next
  'add the strLine to th stat1 stream variable but make sure you get rid of the last comma because that will "tell" csv file that there is another column, which is not the case
  stat1 = stat1 & Left(strLine, Len(strLine)-1) & vbcrlf
  objRS.MoveNext
Loop
'when you finish this loop the strLine variable will contain th elast line added to stat1 and you can use it as needed
...

Open in new window

0
 

Author Comment

by:neoptoent
ID: 34202034
So instead of streaming it into one variable.. how can I have it write line by line into the file
Then after the query is complete take the time and put it in the file?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34202189
Since you are already using the filesystemobject, you would use its WriteLine method after creating each output line.

ref:
http://msdn.microsoft.com/en-us/library/t5399c99(VS.85).aspx

=======
We're still waiting to see your code.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34202211
> how can I have it write line by line into the file

replace

stat1 = stat1 & vbcrlf

for

data.WriteLine stat1

However, I don't see how this can help "to have the script stop so that it doesn't continue to hold the processor", which was your initial goal.
0
 

Author Comment

by:neoptoent
ID: 34206213
i think it put to much into memory and then holds it.
If I change to the text above how will i have all the coluns separated with commas?
Also how will it know to go to the next line
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34206705
@neoptoent

>>...how will i have all the coluns separated with commas?
Do you understand the code that you posted (and are using)?

Do you understand how string concatenation works?
0
 

Author Comment

by:neoptoent
ID: 34209149
Somewhat. Stat1 =sta1 & "," I understand.
But when returning it from a. Query I get lost

So let's say I am returning 14 colums in a query and all rows since the last query how do I do a write line row by row is where I get lost.

0
 

Author Comment

by:neoptoent
ID: 34209678
Here is the code
DBServerName = "sqlserver" 

 DBName = "events"

 TrustedConnection = "True"

 StrConnectString = "Provider=SQLOLEDB;Data Source=" & DBServerName & ";Initial Catalog=" & DBName & ";Trusted_Connection=yes"

 Set objFSO = CreateObject("Scripting.FileSystemObject")

 Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

 objConn.CommandTimeout = 0

 Set objRS = CreateObject("ADODB.Recordset")

 Set fso = CreateObject("Scripting.FileSystemObject")

 Set f = fso.OpenTextFile(c:\results\AuditLog.log", 8 , True)

           

 Set f2 = fso.OpenTextFile(c:\results\lastevent.log",1)

                EventTime = f2.readLine

                 ARR = Split(EventTime, ",")

                 For intElement = 0 To UBound(ARR)

                                 ddd = ARR(intElement)

                                IDold = ARR(0)

                                 EventTime = ARR(1)        

                 Next

                 strLastEventDate = EventTime

                 CurrentTime = now

                 arrlist = "SELECT [ID],[App],[ActivityType],[UserID],[task],[title],[Field],[Source],[PreviousValue],[NewValue] FROM [sqlserver].[dbo].[Auditinfo] where ID > '" & IDold& "'" 

            

 Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

 objConn.CommandTimeout = 0

Set objRS = CreateObject("ADODB.Recordset")

objConn.Open StrConnectString

 set 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)  & "," 

                 Next

                 stat1 = stat1 & vbcrlf

                 objRS.MoveNext

 Loop

 objRS.MoveFirst

 Do While Not objRS.EOF

                 nRowC = nRowC + 1

                 if nRowC = nRow then

                                 lastevent = objRS.Fields(0) & "," & objRS.Fields(3)

                 End If

                 objRS.MoveNext

 Loop

 objRS.Close

 set objRS = Nothing

 objConn.Close

 set objConn = Nothing



 f.WriteLine  stat1

if LastEvent <> "" then

                 err.clear

                 Set f2 = fso.OpenTextFile(c:\results\lastevent.log",2 , True)

          

                 

                 f2.writeLine LastEvent

 end if

 Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

 objConn.CommandTimeout = 0



 Set objRS = CreateObject("ADODB.Recordset")

 Set objTextile = objFSO.OpenTextFile(c:\results\AuditLog.log", 1)

Do Until objTextFile.AtEndOfStream

     strread = strread & objTextFile.ReadLine & vbTab

 Loop

objTextFile.Close

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 34210059
I made several corrections to the code.  

Note: It would be helpful if you enforced object declaration by adding an Option Explicit statement at the top of your script and Dim statements for your variables.

In this version of the code, I am creating the CSVLine in the SQL statement.  There is no need for string concatenation or field iteration.

Also, I'm writing each CSV line as it is read.

I have no idea what the (now commented) code at the bottom is trying to do, but it is doing nothing, so I commented it.

DBServerName = "sqlserver"

DBName = "events"

TrustedConnection = "True"

StrConnectString = "Provider=SQLOLEDB;Data Source=" & DBServerName & ";Initial Catalog=" & DBName & ";Trusted_Connection=yes"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

objConn.CommandTimeout = 0

Set objRS = CreateObject("ADODB.Recordset")

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set f = objFSO.OpenTextFile("c:\results\AuditLog.log", 8, True) 'changed "fso" to "objFSO"

          

Set f2 = objFSO.OpenTextFile("c:\results\lastevent.log", 1)  'changed "fso" to "objFSO"

EventTime = f2.ReadLine

arr = Split(EventTime, ",")



For intElement = 0 To UBound(arr)

  ddd = arr(intElement)

  IDold = arr(0)

  EventTime = arr(1)

Next

strLastEventDate = EventTime

CurrentTime = Now



'arrlist = "SELECT [ID],[App],[ActivityType],[UserID],[task],[title],[Field],[Source],[PreviousValue],[NewValue] FROM [sqlserver].[dbo].[Auditinfo] where ID > '" & IDold & "'"

arrlist = "SELECT [ID] " & "," & [App] & "," & [ActivityType] & "," & [UserId] & "," & [task] & "," & [Title] & "," & [Field] & "," & [Source] & "," & [PreviousValue] & "," & [NewValue] & "As CSVLine FROM [sqlserver].[dbo].[Auditinfo] where ID > '" & IDold & "'"



Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

objConn.CommandTimeout = 0

Set objRS = CreateObject("ADODB.Recordset")

objConn.Open StrConnectString

Set objRS = objConn.Execute(arrlist)

objRS.MoveFirst

intCount = 0

Do While Not objRS.EOF

  f.WriteLine objRS.Fields("CSVLine")

  objRS.MoveNext

Loop



objRS.MoveLast

LastEvent = objRS.Fields(0) & "," & objRS.Fields(3)

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing



If LastEvent <> "" Then

  Err.Clear

  Set f2 = objFSO.OpenTextFile("c:\results\lastevent.log", 2, True)

  f2.WriteLine LastEvent

End If

Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

objConn.CommandTimeout = 0



Set objRS = CreateObject("ADODB.Recordset")



'************

' Following block of code does nothing -- commented

'************

'Set objTextile = objFSO.OpenTextFile("c:\results\AuditLog.log", 1)

'Do Until objTextFile.AtEndOfStream

'  strread = strread & objTextFile.ReadLine & vbTab

'Loop

'objTextFile.Close

Open in new window

0
 

Author Comment

by:neoptoent
ID: 34214993
Hi,

It keeps giving me errors on the array list.
Seems like it the select statement has ID then  ,,,,,,
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34215252
oops.  Try this.
arrlist = "SELECT [ID] & ',' & [App] & ',' & [ActivityType] & ',' & [UserId] & ',' & [task] & ',' & [Title] & ',' & [Field] & ',' & [Source] & ',' & [PreviousValue] & ',' & [NewValue] As CSVLine FROM [sqlserver].[dbo].[Auditinfo] where ID > '" & IDold & "'"

Open in new window

0
 

Author Comment

by:neoptoent
ID: 34217879
So Now the Query looks better but I get this:

C:\results\script.vbs(42, 1) Microsoft OLE DB Provider for SQL Server: The data
types bigint and datetime are incompatible in the '&' operator.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34218455
You will need to cast/format the values.  Do you know how to do that?
0
 

Author Comment

by:neoptoent
ID: 34218520
I was able to cast 3 columns then when i got to title i got :

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

So I took off the cast part for "title"

Cannot resolve collation conflict for column 1 in SELECT statement.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34218530
You only need to cast the non-text fields.

What did you use for the ID column?
0
 

Author Comment

by:neoptoent
ID: 34218605
This is how farI have gotten so far


Title is where the issue is occuring
select cast(ID as varchar(10)) + ',' + cast(app as varchar(10)) + ',' + cast(sensor_host as varchar(10)) + ',' + cast(rule_id as varchar(10)) + ',' + title FROM [sqlserver].[dbo].[all_alerts_view]

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 34221850
what kind of column is TITLE ?

Note: you are getting data from a different source, all_alerts_view, than in your earlier post,  Auditinfo.
0
 

Author Comment

by:neoptoent
ID: 34222679
the columns have different types of data...some have numbers some letter some both

I know that i have a different source.
btw the sql query i am running is against a view

0
 
LVL 45

Expert Comment

by:aikimark
ID: 34223675
but what type is TITLE  -- most important, since that is the column associated with your most recent error.
0
 

Author Comment

by:neoptoent
ID: 34227383
Title has  both in it
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34227430
>>Title has  both in it

I don't understand.  How is the TITLE column defined?
0
 

Author Comment

by:neoptoent
ID: 34231569
how can I check that
0
 

Author Comment

by:neoptoent
ID: 34231733
Also I got past tite..

I am not struggling on "alert_time"
The database stores it in UTC time, so I use a DATEADD(hour, -5, alert_time) to give me local time...
I used a  cast to change it to a varchar.. but then it shows it as Nov 29 ...... Where I need it to remain a 11/29 format

Also I get  a "rowset does not support fetching backward for line 88 which is  the last event write
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34232341
from http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

SELECT Right(convert(varchar, getdate(), 111),5) 



should be = Right( yyyy/mm/dd , 5) => mm/dd

Open in new window

0
 

Author Comment

by:neoptoent
ID: 34232558
great, I will try to convert it like that

the Rowsert does not support backward fetching is occurign on the movelast to get the 2 items for the lastevents

so it fails here and doesnt write those records to the file
objRS.MoveLast

LastEvent = objRS.Fields(0) & "," & objRS.Fields(3)

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 34232659
the default recordset type is forward only.  specify either a snapshot or a dynaset recordset type when you instantiate your recordset variable.
0
 

Author Comment

by:neoptoent
ID: 34232770
Im sorry.. Not really sure how to do that
0
 

Author Comment

by:neoptoent
ID: 34234791
I tried adding this in, but I cant get it to work


objRS.open arrlist, objConn, _

    adOpenStatic, adLockOptimistic

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 34234882
from http://www.w3schools.com/ado/prop_rs_cursortype.asp

you can set the recordset's CursorType property before you invoke its Open method.

Something like this:


bjConn.Open StrConnectString

objRS.CursorType = 2

objRS.Open arrlist, objConn

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 34234889
You probably don't have the constants defined.
0
 

Author Comment

by:neoptoent
ID: 34235571
What constants do I need to define?
0
 

Author Comment

by:neoptoent
ID: 34235576
btw

Thanks so much for the help
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34235800
adOpenStatic, adLockOptimistic
0
 

Author Comment

by:neoptoent
ID: 34240095
Hi

I made the changes and still get the error of backward fetching
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

objConn.CommandTimeout = 0

Set objRS = CreateObject("ADODB.Recordset")

objConn.Open StrConnectString



objRS.CursorType = 2



objRS.Open arrlist, objConn

 



'Set objRS = objConn.Execute(arrlist)

objRS.MoveFirst

intCount = 0

Do While Not objRS.EOF

  f.WriteLine objRS.Fields("CSVLine")

  objRS.MoveNext

Loop



objRS.MoveLast

LastEvent = objRS.Fields(0) & "," & objRS.Fields(3)

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing



If LastEvent <> "" Then

  Err.Clear

  Set f2 = objFSO.OpenTextFile("c:\results\lastevent.log", 2, True)

  f2.WriteLine LastEvent

End If

Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionTimeout = 0

objConn.CommandTimeout = 0



Set objRS = CreateObject("ADODB.Recordset")

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 34241177
you have two
  Set objRS = CreateObject("ADODB.Recordset")
lines.  Is the error happening after the second one?

This is really strange.  What can you tell us about the view you created and are using as the source of objRS?

=========
Is the CSV file creation performance problem resolved?  
Do the output lines look ok?
0
 

Author Comment

by:neoptoent
ID: 34249362
I have gotten past the recordsert problem now.
CSVLine looks correct.

the
LastEvent = objRS.Fields(0) & "," & objRS.Fields(3)
 
Though is not working..
Doesnt recognize field (0) and 3
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34250468
that's because there is only one field (CSVLine) in the recordset.  Change the SQL to add the two fields you need and then change the line to

LastEvent = objRS.Fields(1) & "," & objRS.Fields(2)

Open in new window

0
 

Author Closing Comment

by:neoptoent
ID: 34297612
Thanks for the great help
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34298103
How is the performance?
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now