error with derived table query

[googie]

I am using ASP to build a query which includes derived tables. When I loaded the page I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ' '.

In an attempt to troubleshoot I have simplified the query and table to it's simplies form and am still getting the same error.

here is the ASP code:

-------------------------------------------

Set objCon = Server.CreateObject("ADODB.Connection&quot
Set objRec = Server.CreateObject("ADODB.Recordset&quot
objcon.open "DSN=myDB","username","password"

SQL = "SELECT * FROM "
SQL = SQL & "("
SQL = SQL & "SELECT * "
SQL = SQL & "FROM orders"
SQL = SQL & &quot OST"

objrec.open SQL,objcon,3,3

-------------------------------------------

Produces the query:

SELECT * FROM (SELECT * FROM orders) OST

If I copy and past this into a view or query analyzer it works fine. The table now only has one varchar column in it. Any thoughts on what is happening?

thanks in advance,

gordy

 

[tlbroadbent]

Try using the keyword AS when declaring the alias for the subquery.

SQL = "SELECT * FROM "
SQL = SQL & "("
SQL = SQL & "SELECT * "
SQL = SQL & "FROM orders"
SQL = SQL & &quot AS OST"
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[googie]

Sorry...I should have mentioned the things that I have already tried. I did try using the keyword AS and have played with spacing (didn't think it would make a difference but worth a try) and neither has helped.

I have renamed the table as well as played with the derived table name in the query thinking maybe there was a keyword somewhere that I was unaware of.

Also, I have got another derived query with almost exact same syntax to work on another database, same server. Both written in ASP.

Last, I have changed the * to exact column names and back again.

No luck yet.