Query

Similar Threads:

1.Query using UDF joined to a sub query vs Temp Table to sub Query

I have a simple query question.  Currently I have a query that joins a UDF that returns a table to a sub query of a number of secondary tables.  This query seems to run forever, but if I dump the results of the udf into a temp table and use joined to the sub query the results are as expected.  Can anyone shed some light onto why?  Also is it possible to use the UDF in this manner with possible join hints to get the query return time I'm looking for?

2.SQL Server 2005 -- Internal Query Processor Error: The query processor could not produce a query plan

Hi all
I migrated a database from SQLServer 2000 (Standard edition) to
SQLServer 2005, standard, SP1, on Windows XP Prof, SP3
After migration I get the following error:

   Server: Msg 8624, Level 16, State 1, Line 6 
   Internal Query Processor Error: The query processor could not produce a query
plan. 

when I try to execute:

   insert into <table name> (Col1,Col2,Col3) values (val1,val2,val3)

I searched the internet and I found that similar errors where produced in SQL
2000
but this was a bug that was fixed in one of the service packs for 2000. Also
they involved complex query and anyway, it works in my SQL 2000. I found nothing
related to SQL 2005.

Any help appreciated

3.ADP code hangs while running the same query in query analyser works

I have got an updateable snapshot based on a view. When close the form, it 
automatically sends out the following script to update the underlying table.

exec sp_executesql N'UPDATE "arpos".."tafe_hdr" SET "ref2"=@P1 WHERE 
"seqno"=@P2 AND "created_date"=@P3 AND "enrolmentno"=@P4 AND "ord_ref"=@P5 
AND "ref1"=@P6 AND "ref2"=@P7 AND "notes" IS NULL AND "invno"=@P8', N'@P1 
varchar(6),@P2 int,@P3 datetime,@P4 varchar(12),@P5 varchar(4),@P6 
varchar(10),@P7 varchar(4),@P8 int', 'testts', 246, 'Feb  3 2003 
12:00:00:000AM', 'p02062693446', 'test', '0203000085', 'test', 246

This screen in ADP simply times out. In SQL Server locks, I can see that 
this connection is blocked by itself?! (how is that possible).

I run the same script in query analyser, the script completes under 1 sec.

Any idea what is going on?

I have a trigger on the underlying table that is being updated

CREATE TRIGGER ASAS_UPD_TAFE_HDR ON [dbo].[TAFE_HDR]
FOR UPDATE
AS
 if exists (select * from master.dbo.sysprocesses where program_name like 
'microsoft office%' and spid=@@spid)
 begin
  update
   a
  set
   a.transdate=b.created_date,
   a.ref2=b.enrolmentno,
   a.x_enrol_id=b.enrolmentno,
   a.ord_ref=b.ord_ref,
   a.ref1=b.ref1
  from
   dr_trans a inner join inserted b on
    a.jobno=b.seqno


  insert into asas_audit
   select
    'Update Invoice Header',
    system_user,
    host_name(),
    getdate(),
    invno
   from
    inserted
 end

I don't think it makes any difference. I have included it just in case if 
anyone can spot any problem here.

Thanks in advance

4.Showing Query Results from VBA SQL Query

Hello all,

I am running into an issue showing results from a VBA SQL query that I need 
to export to Excel.  Its nested within a loop sequence, and will run for each 
individual in a certain work group.

I have used the following code before and am just looking for they way to do 
it a VBA SQL dataset.

    DoCmd.OpenQuery "Query1"
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.RunCommand acCmdCopy

5.make table query which lists all queries in db and their SQL state

Is there a way to create a query which returns all queries in the db and 
their SQL statement?  Or is there code that will do this for me?

Essentially, I'm envisioning a table which would have two columns:

Query Name | SQL

(Please don't mention Access' documenter feature as it is poorly designed 
for what I'm looking to do.)

Thanks!

Dave

6. save an query by sql code and deleting the saved query

7. Convert Access SQL Query to Excel MS Query

8. Query within a Query - SQL in VBA