Re: XML Report Suceeded
Well I've been having some fun :( running SQL from SQLExpress as I can't install R6 [it won't install on my machine with SQL already installed]. I did get the query to run but it would only do it a bit at a time. I mean that I had to introduce a where clause to reduce the amount of time/data otherwise it would time-out. I did change all the variables for time, available to me, as specified on the MS Forums [thanks to Charles for spotting some for me] but it made no difference.
When I found so many problems with the output some of which I know are faults in R6 I felt I had to run the whole report in R6 to be sure that I do not introduce more errors myself. Being hugely concerned now that garbage in garbage out meant R6 could not be used for a primary function, namely the status of a species. I had to be sure to minimise the chance that all my 109 queries had not been the cause of further problems.
To do this I have to convert the SQL to XML. Now I do see the advantage in the long-term of XML but does that have to mean no SQL. I could not see any obvious help in creating these. I did find a link referring to the waste of space called the wiki, where I only see empty pages for subjects that have not been written and we cannot edit. More empty title pages would be even more useful :)
I tried to follow the examples supplied but it always complained about my where clause, I removed it. It complained. I put a comment in to remind me, it complained. Finally I put the whole query into the where clause of the XML and it works. My guess it that the <Where> clause is not used as one might think.
Phew I got the query to run in R6. 42,000 odd records returned. Export to XLS. Catastrophic Failure. Thanks. Right-click Export Data. Sorry there is no data to export [something like that]. I had to copy and paste. I must admit I was thinking I would have to do a screen dump of each page worth of results and OCR it just to get some useful output.
It's taken days of my time for something that takes a few minutes in R3. Sure a big chunk is learning curve but it still seems more doesn't work than does. I'm of course referring to the use of the data perspective here not data entry. My plan has always been to use SQL server to do the talking. I've not tried connecting to it using our Enterprise Manager but the experience I've had thus far does not bode well. More hair loss seems guaranteed. :)
I like the idea of XML. I've read a book or two on the subject with varying levels of success. They never seem to tell you what you need or fail to give enough examples. Time and experience will out. The complexity of R6 does not seem to lend itself to ease of use for data abstraction. No doubt I will be happier when I'm further down the learning curve road. Remember it may take 5 minutes to write a query but 30 or more to make sure you got what you wanted. If you're unsure you may have to write the inverse query or similar to confirm the first was correct.
Somerset Environmental Records Centre