Hey Jonas...
ODBC Connections can sometimes return unusual results. Here are some
quick points that might help:
1) Historically different versions of the ODBC Drivers have had
problems returning data when there are not qualifications (where
clause) even if the qualification returns all records.
2) Check to see if there is limit on the server for the number of
records that can be returned by a query.
3) Are you using ARODBC or are you using a Database ODBC connection.
I prefer the ARODBC connection when I don't want to have translate
Enumerated lists or figure out the Epoch Date.
4) When importing into Excel, or Access - remember that there is a
Microsoft Office limitation for the number of columns that can be
returned.
That's about all I can think of right now...
Let me know.
On Jul 8, 6:11 am, Jonas Stumph Stevnsvig <jo...@stevnsvig.com> wrote:
> 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 atwww.arslist.org
> attend wwrug11www.wwrug.comARSList: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
