Hi listers
I have an interesting problem.
I have been tasked with fetching some data from HPD:Help Desk (ITSM v
7.6.03 both client and ARS server, v 7.6.03)
I ran into an oddity; When Fetching data through ODC (I used Excel to
start with) I only got about 1000 Records. But i expected 12000. I then
removed a couple of fields from the list of those i wanted returned, and
received the full 12k.
To further investigate I copied the connection string and SQL to a vbs
script I had for another purpose, and altered the sql to find the culprit.
By only altering the FROM part of the SQL clause (note - no WHERE clause
at all) I got significantly different results (see below)
My question is: have any of you experienced this?
I hope that is is something to do with data corruption (and then again I
pray that it isn't), since if it's not corrupted data, the ODBC driver is
not to be trusted to provide correct data, which would be catastrophic.
********** SELECT with approx 60 records
SELECT
*
FROM
HPD_Help_Desk
ORDER BY
Incident_Number DESC
********** SELECT with approx 1000 records
SELECT
Incident_Number,
Product_Categorization_Tier_1, Product_Categorization_Tier_2,
Product_Categorization_Tier_3,
First_Name, Last_Name, Site, Status, Priority, Assignee, Assigned_Group,
Assignee_Login_ID,
Categorization_Tier_1, Categorization_Tier_2, Categorization_Tier_3,
Corporate_ID, Reported_Source, Service_Type, Submit_Date, Submitter
FROM
HPD_Help_Desk
ORDER BY
Incident_Number DESC
********** SELECT with approx 9500 records
SELECT
Incident_Number,
Product_Categorization_Tier_1, Product_Categorization_Tier_2,
Product_Categorization_Tier_3,
First_Name, Last_Name, Site, Status, Priority, Assignee, Assigned_Group,
Assignee_Login_ID,
Categorization_Tier_1, Categorization_Tier_2, Categorization_Tier_3,
Corporate_ID, Reported_Source, Submit_Date, Submitter
FROM
HPD_Help_Desk
ORDER BY
Incident_Number DESC
****************************** FULL VBS SCRIPT BELOW HERE ****************
Dim objConnection
Dim sql_query, strOutputLine
Dim arrInputLine, strInputLine
Dim objFSO, objOutFile
Dim intCount, intCountNumber
Dim currDir, connStr
Dim fso, folder, files, NewsFile,sFolder, strFileName
Dim bolDEBUG
Dim strServer, strUser, strPass, strPort
'For DB connections
Const adOpenStatic = 3, adLockOptimistic = 3
'bolDEBUG Flag
bolDEBUG = false
'Define variables for folders and files
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2, ForAppending = 8
currDir = CreateObject("Wscript.shell").currentdirectory
dbFile = currDir & "\RKS-Medlemmer.mdb"
inputDir = currDir & "\Input"
strFileName = "Output_" & Date & "_" & DatePart("h", Now) & "-" &
DatePart("n", Now) & "-" & DatePart("s", Now) & ".csv"
Set objOutFile = objFSO.OpenTextFile(strFileName, ForAppending, True)
'Set Variables for connection
strServer = "servername"
strUser = "username"
strPass = "password"
strPort = "5500"
'build Connection String
connStr = "DRIVER={AR System ODBC Driver};ARUseUnderscores=1;ARServer=" &
strServer & ";" & _
"ARServerPort=" & strPort & ";" & _
"UID=" & strUser & ";" & _
"PWD=" & strPass & ";" & _
"ARAuthentication=;SERVER=NotTheServer"
'********** SELECT with approx 60 records
'sql_query = "SELECT " & _
'"* " & _
'"FROM HPD_Help_Desk " & _
'"ORDER BY Incident_Number DESC"
'********** SELECT with approx 1000 records
'sql_query = "SELECT " & _
' "Incident_Number, " & _
' "Product_Categorization_Tier_1, Product_Categorization_Tier_2,
Product_Categorization_Tier_3, " & _
' "First_Name, " & _
' "Last_Name, " & _
' "Site, " & _
' "Status, " & _
' "Priority, " & _
' "Assignee, " & _
' "Assigned_Group, " & _
' "Assignee_Login_ID, " & _
' "Categorization_Tier_1, Categorization_Tier_2, Categorization_Tier_3, "
& _
' "Corporate_ID, " & _
' "Reported_Source, " & _
' "Service_Type, " & _
'potential culprit!!!
' "Submit_Date, " & _
' "Submitter " & _
'"FROM HPD_Help_Desk " & _
'"ORDER BY Incident_Number DESC"
'********** SELECT with approx 1000 records
sql_query = "SELECT " & _
"Incident_Number, " & _
"Product_Categorization_Tier_1, Product_Categorization_Tier_2,
Product_Categorization_Tier_3, " & _
"First_Name, " & _
"Last_Name, " & _
"Site, " & _
"Status, " & _
"Priority, " & _
"Assignee, " & _
"Assigned_Group, " & _
"Assignee_Login_ID, " & _
"Categorization_Tier_1, Categorization_Tier_2, Categorization_Tier_3, "
& _
"Corporate_ID, " & _
"Reported_Source, " & _
"Submit_Date, " & _
"Submitter " & _
"FROM HPD_Help_Desk " & _
"ORDER BY Incident_Number DESC"
'********** Dump the data
set objConnection = CreateObject("ADODB.Connection") 'create and
open ODBC connection
objConnection.ConnectionString = connStr
objConnection.Open
Set objRecordSet = objConnection.execute(sql_query) 'execute query
intCount = 0
On Error Resume Next
objRecordSet.MoveFirst
intCountNumber = objRecordset.Fields.Count
Do While Not objRecordSet.eof
strOutputLine = ""
for fldCnt = 1 to intCountNumber
strOutputLine = strOutputLine & objRecordSet(fldCnt) & ", "
next
objOutFile.Write strOutputLine & vbCrLf
objRecordSet.MoveNext
intCount = intCount + 1
Loop
Wscript.echo intCount&" records copied to " & strFileName
'Close output files
objOutFile.close
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
