Solved

MS-SQL question

Posted on 1997-09-11
5
231 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now