SQL Server thread issue and long running query

#1 (**permalink**) 28-Jul-2006, 08:01 AM

We have a SELECT INTO query that runs in Access utilizing SQL Server Tables
and ODBC. We changed the Access query so that it is Appending into a Table.
When we rip this query out of Access and run it in Query Analyzer, it returns
7,000+ rows in 30 seconds. However, when we run this query in Access, it is
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9334-sql-server-thread-issue-long-running.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
taking 10 minutes.

We have checked the Tamp folders and all is clean.

Can anyone help here???

Thanks in advance!

wnfisba

*

#2 (**permalink**) 28-Jul-2006, 08:01 AM

The data you are appending in the table, do you filter this table? and the
7000 records are the resault of the filter.

In that case you can create a PassThrough query to return the records from
the SQL, and then create the insert query using the OutPut of the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
PassThrough query.

--
Good Luck
BS"D

"wnfisba" wrote:

> We have a SELECT INTO query that runs in Access utilizing SQL Server Tables
> and ODBC. We changed the Access query so that it is Appending into a Table.
> When we rip this query out of Access and run it in Query Analyzer, it returns
> 7,000+ rows in 30 seconds. However, when we run this query in Access, it is
> taking 10 minutes.
>
> We have checked the Tamp folders and all is clean.
>
> Can anyone help here???
>
> Thanks in advance!
>
> wnfisba

#3 (**permalink**) 28-Jul-2006, 08:01 AM

How would I go about creating a "PassThrough" query??? Is that done in
Access???

"Ofer Cohen" wrote:

> The data you are appending in the table, do you filter this table? and the
> 7000 records are the resault of the filter.
>
> In that case you can create a PassThrough query to return the records from
> the SQL, and then create the insert query using the OutPut of the
> PassThrough query.
>
>
> --
> Good Luck
> BS"D
>
>
> "wnfisba" wrote:
>
> > We have a SELECT INTO query that runs in Access utilizing SQL Server Tables
> > and ODBC. We changed the Access query so that it is Appending into a Table.
> > When we rip this query out of Access and run it in Query Analyzer, it returns
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
> > 7,000+ rows in 30 seconds. However, when we run this query in Access, it is
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
> > taking 10 minutes.
> >
> > We have checked the Tamp folders and all is clean.
> >
> > Can anyone help here???
> >
> > Thanks in advance!
> >
> > wnfisba

#4 (**permalink**) 28-Jul-2006, 08:02 AM

Yes, It's done in Access.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
You create it in the MDB, a query, but it execute on the server, and it
return only the desired records.

Open a new query, from the MenuBar select Query > SQL sepecific >
PassThrough query.
Type the SQL the same as you typed in the Query Analayser, remember that it
runs on the server so some things that you can use in Access you can't use on
the SQL server (such as: IIf , you use CASE instead).
Also the table must be located on the server, you can't use the SQL with
local tables in the MDB.

--
Good Luck
BS"D

"wnfisba" wrote:

> How would I go about creating a "PassThrough" query??? Is that done in
> Access???
>
> "Ofer Cohen" wrote:
>
> > The data you are appending in the table, do you filter this table? and the
> > 7000 records are the resault of the filter.
> >
> > In that case you can create a PassThrough query to return the records from
> > the SQL, and then create the insert query using the OutPut of the
> > PassThrough query.
> >
> >
> > --
> > Good Luck
> > BS"D
> >
> >
> > "wnfisba" wrote:
> >
> > > We have a SELECT INTO query that runs in Access utilizing SQL Server Tables
> > > and ODBC. We changed the Access query so that it is Appending into a Table.
> > > When we rip this query out of Access and run it in Query Analyzer, it returns
> > > 7,000+ rows in 30 seconds. However, when we run this query in Access, it is
> > > taking 10 minutes.
> > >
> > > We have checked the Tamp folders and all is clean.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9334
> > >
> > > Can anyone help here???
> > >
> > > Thanks in advance!
> > >
> > > wnfisba