Solved

MS-SQL question

Posted on 1997-09-11
5
233 Views
Last Modified: 2012-05-04
Ok, here is the problem, I am currently running an MS-SQL 6.5 database with an Access 7.00 front end. The problem here is that long running queries will time out in Access before they can be completed, and just using ISQL/w, I can't do crosstab queries. Is there anyway to run a Crosstab Query in standard SQL, the server doesn't seem to support TRANSFORM and PIVOT, so I'm assuming those are an Access invention. I need to figure out a way to either run the query directly in SQL, or Keep access from timing out in 60 seconds so I can run it from there.
Right now, my option is to run a maketable query on the server to store all of the relevent data, and then run the crosstab directly on that. I've tried using a View, but the server can't process it quickly enough to keep access from timing out, which means I have to run the maketable query everytime I want to run the crosstab query or the results will be out of date. Anyone have a solution?
0
Comment
Question by:wrussell051197
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1088928
Your right TRANSFORM and PIVOT are Access only conventions.
Take a look at the With ROLLUP and With CUBE midifiers on the group by clause and see if that doesn't help some.  You would say:

select x,y,z,sum(quantity) from XYZ
group by x,y,z with rollup (or with cube)
order by x,y,z
0
 

Author Comment

by:wrussell051197
ID: 1088929
Hmm, interesting, but those don't seem to offer a solution to what I'm trying to do. Perhaps I should go into more detail on the query I'm trying to run. Here is what I have, as generically as I can put it. Table(s) with the following fields:

ID     - integer
adate  - datetime
groupn - integer

I'm trying to get a result that look similar to this

groupn  1980  1981  1982  1983  1984  1985  1986 ...
0         12    34    19     7   ...
1         15  
2         32
3          6
4          9
5         ...

Where the column headings are a year pulled from adate, the row headings are the value of groupn, and the data that makes up the remainder of the result is the COUNT(ID) where groupn is the group for that row, and datepart(yy,adate) is the year for the column. I can't see any way to do this using CUBE or ROLLUP. Any other suggestions?
0
 

Author Comment

by:wrussell051197
ID: 1088930
Hmm, it seems to have butchered what was supposed to be a table, but I think my description should give you a general idea.
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 10 total points
ID: 1088931
HHHMMMmmmm... Ok try something like this.  Only take parts of the query (like each integer value grouping) into Access and use access to append all the groups locally on Access into one  query or table, then do your pivot/transform there.  This might keep  the individual times down so that you don't time out.

Will that work for you?
0
 

Expert Comment

by:wildokie
ID: 1088932
I run into the ODBC timeout when appending large number of records into SQL Server.  Try BCPump32 which can be downloaded, it is tons faster than Access 97 for appending data.  

Also you can edit the registry in \\HKEY_LOCAL_MACHINE\Software\Micorsoft\Jet\3.0\Engines\ODBC and change the QueryTimeout Value to something other than 60 Seconds.

Rewriting a large query into several smaller queries also helps, so that you are selecting data and then using that result as input into your crosstab query.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Row insertion failed. Array 5 48
SQL query and VBA 5 46
MS SQL SERVER and ADODB.commands 8 23
SQL - Simple Pivot query 8 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question