Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS-SQL question

Posted on 1997-09-11
5
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

670 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