Re: ODBC not returning the expected values from Incident form
I've discovered a problem that seems to stem from the Office 2010 version of MS Query's interaction with the ODBC. A basic full table grab will not return att rows if certain fields ar included in the query. Remove those and att rows are returned. I tried adding a where required field is not null clause and get the same results 3k of 14k rows. The same query via user tool brings back all the rows as expected. Kinda messes up reporting. On Wed, Aug 3, 2011 at 9:47 AM, Elry elryal...@gmail.com wrote: ARODBC Can return odd things if there is no WHERE clause. Even if the WHERE clause returns all entries. On Aug 2, 8:42 am, Jonas Stumph stevnsvig jo...@stevnsvig.com wrote: Hi again, I'll try not to hit ctrl+enter again... sorry for the triple posts. I am still fiddling with this problem, concering ARODBC and the number of records to be returned. I and querying the incident form on a server with 12000 records, and getting very big variations in how many records are being returned. I started in excel, and moved to VBscript to 1) ascertain whether it was an excel or ARODBC error. and 2) to be able to correct and experiment faster than waiting for the GUIs in the ODBC system in excel to refresh. I have tried several different variations of where clauses, as previously suggested (eg. WHERE Incident_Number 'ThisOldString') but to no avail. BMC support is misunderstanding me, and thinks that I am trying on different databases, wih different numbers of records, so I have tried to clarfy my query to them. Does anyone else have any ideas of what else I can try? Best regards, Jonas Stevnsvig ** SELECT with approximately 60 records returned, of 12000 total SELECT * FROM HPD_Help_Desk ORDER BY Incident_Number DESC ** SELECT with approximately 1000 records returned, of 12000 total 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 approximately 9500 records returned, of 12000 total 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 ** SELECT with 12000 records returned, of 12000 total 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, Corporate_ID, Reported_Source, Submit_Date, Submitter FROM HPD_Help_Desk ORDER BY Incident_Number DESC ___ 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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: ODBC not returning the expected values from Incident form
ARODBC Can return odd things if there is no WHERE clause. Even if the WHERE clause returns all entries. On Aug 2, 8:42 am, Jonas Stumph stevnsvig jo...@stevnsvig.com wrote: Hi again, I'll try not to hit ctrl+enter again... sorry for the triple posts. I am still fiddling with this problem, concering ARODBC and the number of records to be returned. I and querying the incident form on a server with 12000 records, and getting very big variations in how many records are being returned. I started in excel, and moved to VBscript to 1) ascertain whether it was an excel or ARODBC error. and 2) to be able to correct and experiment faster than waiting for the GUIs in the ODBC system in excel to refresh. I have tried several different variations of where clauses, as previously suggested (eg. WHERE Incident_Number 'ThisOldString') but to no avail. BMC support is misunderstanding me, and thinks that I am trying on different databases, wih different numbers of records, so I have tried to clarfy my query to them. Does anyone else have any ideas of what else I can try? Best regards, Jonas Stevnsvig ** SELECT with approximately 60 records returned, of 12000 total SELECT * FROM HPD_Help_Desk ORDER BY Incident_Number DESC ** SELECT with approximately 1000 records returned, of 12000 total 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 approximately 9500 records returned, of 12000 total 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 ** SELECT with 12000 records returned, of 12000 total 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, Corporate_ID, Reported_Source, Submit_Date, Submitter FROM HPD_Help_Desk ORDER BY Incident_Number DESC ___ 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
Re: ODBC not returning the expected values from Incident form
Hi again. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: ODBC not returning the expected values from Incident form
Hi again. I am still fiddling with this problem, concering ARODBC and the number of records to be returned. I and querying the incident form on a server with 12000 records, and getting very big variations in how many records are being returned. I started in excel, and moved to VBscript to 1) ascertain whether it was an excel or ARODBC error. and 2) ** SELECT with approximately 60 records returned, of 12000 total SELECT * FROM HPD_Help_Desk ORDER BY Incident_Number DESC ** SELECT with approximately 1000 records returned, of 12000 total 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 approximately 9500 records returned, of 12000 total 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 ** SELECT with 12000 records returned, of 12000 total 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, Corporate_ID, Reported_Source, Submit_Date, Submitter FROM HPD_Help_Desk ORDER BY Incident_Number DESC ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: ODBC not returning the expected values from Incident form
Hi again, I'll try not to hit ctrl+enter again... sorry for the triple posts. I am still fiddling with this problem, concering ARODBC and the number of records to be returned. I and querying the incident form on a server with 12000 records, and getting very big variations in how many records are being returned. I started in excel, and moved to VBscript to 1) ascertain whether it was an excel or ARODBC error. and 2) to be able to correct and experiment faster than waiting for the GUIs in the ODBC system in excel to refresh. I have tried several different variations of where clauses, as previously suggested (eg. WHERE Incident_Number 'ThisOldString') but to no avail. BMC support is misunderstanding me, and thinks that I am trying on different databases, wih different numbers of records, so I have tried to clarfy my query to them. Does anyone else have any ideas of what else I can try? Best regards, Jonas Stevnsvig ** SELECT with approximately 60 records returned, of 12000 total SELECT * FROM HPD_Help_Desk ORDER BY Incident_Number DESC ** SELECT with approximately 1000 records returned, of 12000 total 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 approximately 9500 records returned, of 12000 total 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 ** SELECT with 12000 records returned, of 12000 total 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, Corporate_ID, Reported_Source, Submit_Date, Submitter FROM HPD_Help_Desk ORDER BY Incident_Number DESC ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
ODBC not returning the expected values from Incident form
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
Re: ODBC not returning the expected values from Incident form
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
