Query works in sql studio but not in classic ASP

Hi experts,

I've got a query that works in my classic ASP page. But now I made it more complex and after i successfully tested it in MS SQL management studio it does no longer work in my ASP page.

It is this query:

'SQL = "with cte as (Select ADS_ongekoppelde_accounts_acties.actiedatum, ADS_ongekoppelde_accounts.id, ADS_ongekoppelde_accounts.afdeling, ADS_ongekoppelde_accounts.inlijst, ADS_ongekoppelde_accounts.reden, ADS_ongekoppelde_accounts.account,ROW_NUMBER() OVER(PARTITION BY ADS_ongekoppelde_accounts_acties.actiedatum ORDER BY ADS_ongekoppelde_accounts.account desc) rn from ADS_ongekoppelde_accounts_acties inner join ADS_ongekoppelde_accounts on ADS_ongekoppelde_accounts.id=ADS_ongekoppelde_accounts_acties.account) select id, actiedatum,account, reden, inlijst, afdeling from cte where rn=1;"

Open in new window


I get this error :

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'with'.
LVL 1
SteynskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
Try changing with to WITH

If for some reason that fails, then try:
SQL=";WITH...

I don't really see anything else wrong with your code if that's all the code you have.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pateljituCommented:
I would suggest to create a store procedure for that SQL statement and making call to your store procedure from classic ASP page.
0
SteynskAuthor Commented:
Thank you Sammy
0
PortletPaulfreelancerCommented:
it's the leading semi-colon that made the difference ;^)

an observation: there is no advantage in using a CTE for this query, a simple nested subquery would do just as well.
SELECT
        id
      , actiedatum
      , account
      , reden
      , inlijst
      , afdeling
FROM (
        SELECT
                ADS_ongekoppelde_accounts_acties.actiedatum
              , ADS_ongekoppelde_accounts.id
              , ADS_ongekoppelde_accounts.afdeling
              , ADS_ongekoppelde_accounts.inlijst
              , ADS_ongekoppelde_accounts.reden
              , ADS_ongekoppelde_accounts.account
              , ROW_NUMBER() OVER (PARTITION BY ADS_ongekoppelde_accounts_acties.actiedatum ORDER BY ADS_ongekoppelde_accounts.account DESC) rn
        FROM ADS_ongekoppelde_accounts_acties
        INNER JOIN ADS_ongekoppelde_accounts
                ON ADS_ongekoppelde_accounts.id = ADS_ongekoppelde_accounts_acties.account
      ) AS SQ
WHERE rn = 1;

Open in new window

may make it easier next time
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.