Solved

MS-SQL question

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

9 Experts available now in Live!

Get 1:1 Help Now