Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS-SQL question

Posted on 1997-09-11
5
Medium Priority
?
237 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 40 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

885 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