1 (edited by Rob B 29-08-2006 09:50:35)

Re: Report wizard errors

Just got back form trip in north with two seasonal rangers. Different errors on each machines. One fixable- registry pointed to a different MSDE, odd but there ye go!

The other one is more tedious; Essentially I was unable to perfom reports on his machine using the wizard. Most errors occurred during final phase and by the example below, make the barest sense to me.

For example, when plugging in the first key 'CI000011000000D5' into 'Find key addin' then trying every permutation, I get results for Survey Event, Biotope occurence, and Location. Only the location would have had anything to do with the parameters within the selected report. 

I understand that making sense of error logs is just the start, but to say that it's embarrassing trying to tshow someone to use R6 with confidence & then getting errors for everything I do, is a little rich..

Exception occurred in application Recorder 6 at 28/08/2006 16-30-54.
Version : 6.7.2.77

Exception path:
EOleException : The column prefix 'ITS' does not match with a table name or alias name used in the query
TExceptionPath : An error occurred executing the following SQL:
SELECT DISTINCT Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_EVENT_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SAMPLE_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as LIST_ITEM_KEY , Convert(Integer, Null)  as [Event_Vague_Date_Start], Convert(Integer, Null)  as [Event_Vague_Date_End], Convert(varchar(2), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Event_Vague_Date_Type], Convert(varchar(100), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Event Location], Convert(varchar(100), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Taxon Status Long Name] into "#REPORT_OUTPUT" From (((((Taxon_Occurrence LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key ) LEFT JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key ) LEFT JOIN Location LSamp ON Sample.Location_Key = LSamp.Location_Key ) LEFT JOIN Index_Taxon_Name ITN ON (ITN.Taxon_List_Item_Key = Taxon_Determination.Taxon_List_Item_Key and ITN.System_Supplied_Data=1) ) LEFT JOIN Location_Name LNSamp ON LSamp.Location_Key = LNSamp.Location_Key ) LEFT JOIN Taxon_List_Item ON Taxon_List_Item.Taxon_List_Item_Key = ITN.Taxon_List_Item_Key WHERE (LNSamp.Preferred = 1 or LNSamp.Preferred is null) AND LSamp.Location_Key IN ('CI000011000000D5','CI000011000000E2','CI0001930000002P','CI0001930000004N','CI0001930000004O','CI0001930000004P','CI0001930000004Q','CI0001930000004R','CI0001930000004S','CI0001930000004T','CI0001930000004U') AND ITS.Taxon_List_Item_Key IN ('NHMSYS0000530069')

Last event\actions:
  TfrmFilterResult deactivated
  TfrmTaxonDictBrowser activated
  actPlacesForOccurrencesReport invoked
  TfrmTaxonDictBrowser deactivated
  TfrmTaxonDictBrowser destroyed
  actReportWizard invoked
  TfrmFilterResult created
  TfrmFilterResult activated
  TdlgWizard created

Operating System : Windows 2000  5.00.2195  Service Pack 4
Physical Memory available : 130,544 KB

DLLs loaded:
  advapi32.dll (5.0.2195.6710)
  comctl32.dll (5.81.4916.400)
  comdlg32.dll (5.0.3700.6693)
  gdi32.dll (5.0.2195.6762)
  HHCtrl.ocx (5.2.3735.1)
  kernel32.dll (5.0.2195.6794)
  mpr.dll (5.0.2195.6611)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.520.9030.0)
  ole32.dll (5.0.2195.6810)
  oleaut32.dll (2.40.4522.0)
  shell32.dll (5.0.3700.6705)
  user32.dll (5.0.2195.6799)
  version.dll (5.0.2195.6623)
  winmm.dll (5.0.2161.1)
  winspool.drv (5.0.2195.6659)

I look forward to a little guidance, Cheers now, Rob.

2

Re: Report wizard errors

I have no idea why, but I am pretty sure that the 'ITS' that is causing the problem should be 'ITN' as in INDEX_TAXON_NAME. That query is looking for 'NHMSYS0000530069' (White-Tailed Eagle from the BIRDS list) within a set of locations. For some reason, the report wizard is generating this dodgy SQL:

AND ITS.Taxon_List_Item_Key IN ('NHMSYS0000530069')

If you change the ITS to ITN, I'd bet the query would run.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

3

Re: Report wizard errors

so where do you bypass the wizard & change the sql?

4

Re: Report wizard errors

I did it in SQL Server Management Studio (aka Enterprise Manager/Query Browser if you're on SQL 2000). These come with the full version of SQL Server 2005 and 2000 respectively; however, you can download the very capable SQL Server Management Studio Express from for free:

http://msdn.microsoft.com/vstudio/express/sql/download/

This isn't a substitute for using the Report Wizard, though, but simply a troublshooting measure. What you're experiencing looks to be like a bug. Are you able to reliably reproduce it? What are the steps you're taking in order to generate the error?

Charles

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

5

Re: Report wizard errors

Hi Charles,

Firstly, the errors occurred during site-specific reports ('Report about a place' in 1st screen). I gradually reduced the parameters trying to find out whether anything specific was causing them.

All errors were prefixed by :"Exception path:
EOleException : The column prefix '......' does not match with a table name or alias name used in the query"

Where said column was either  'Taxon_List' or 'ITS', as seen above. The only other factor I can think of as this was a satellite standalone, is that I'd somehow missed something crucial when importing data from the server version. As next month we are scheduled to roll out  9 other standalone installs to Conservation Managers, ye can appreciate why these errors are not really good news. Perhaps instead of generating site or location area-specifc data exports -usually done by creating a filter on File Code within locations-, I should start to use the Snapshot functions instead?

Regarding the Management Studio, how much SQL knowledge is needed to make adequate use of it?

6

Re: Report wizard errors

I've tried running a few 'about a place' reports and can't reproduce the problem; it's very strange.

Regarding SSMS, when you say 'SQL knowledge' do you mean knowledge of SQL programming and syntax, or do you mean SQL Server knowledge? If you're going to be writing, studying or manipulating SQL queries by hand (as opposed to, say, doing it in the Access query designer), then yes, you need knowledge of SQL the language.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

7 (edited by Rob B 04-09-2006 09:07:00)

Re: Report wizard errors

ok, possibly time for new thread, however I've been looking at doing one of these two courses mainly to improve my own competency:

Microsoft-SQL-Server-2000-Administratio … eminar.htm

and

SQL Server 2005 Comprehensive Introduct … /uk131.htm

Both are pretty expensive & I'll probably have to find my own funding. However in the course of the past year , especially with R6, I'd like to be far more adept with the structure, language & potentials of SQL as opposed to asking wee questions here & there as problems come up.

As always, all comments and/or suggestions welcome!