Solved

VBscript SQL query hangs script

Posted on 2010-11-12
65
1,376 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
Comment Utility
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
Comment Utility
This is just a vbscript that runs on a schedule... a .vbs
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
correction in the above example:

if nRow > 10000 then exit do
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
strickdd - disagree i was agreeing with Zberteoc comment: but added my own code...
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
please post all your code
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You will need to cast/format the values.  Do you know how to do that?
0
 

Author Comment

by:neoptoent
Comment Utility
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
Comment Utility
You only need to cast the non-text fields.

What did you use for the ID column?
0
 

Author Comment

by:neoptoent
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
but what type is TITLE  -- most important, since that is the column associated with your most recent error.
0
 

Author Comment

by:neoptoent
Comment Utility
Title has  both in it
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
>>Title has  both in it

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

Author Comment

by:neoptoent
Comment Utility
how can I check that
0
 

Author Comment

by:neoptoent
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Im sorry.. Not really sure how to do that
0
 

Author Comment

by:neoptoent
Comment Utility
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
Comment Utility
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
Comment Utility
You probably don't have the constants defined.
0
 

Author Comment

by:neoptoent
Comment Utility
What constants do I need to define?
0
 

Author Comment

by:neoptoent
Comment Utility
btw

Thanks so much for the help
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
adOpenStatic, adLockOptimistic
0
 

Author Comment

by:neoptoent
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the great help
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
How is the performance?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

12 Experts available now in Live!

Get 1:1 Help Now