WITH Clause Help 2

[gtb12314]

Hi Everyone,

When I am running the below code I am getting syntax error pointing at “with”, Any suggestions.

with pdninfo(transactionid, employeenbr) as
(SELECT
pdn.transactionid,
pdn.employeenbr
FROM [xyz].[dbo].[PDNMain] pdn)

SELECT transactionid, employeenbr
FROM pdninfo

I am using SQL Server 2005.

Thanks in advance!!

 

[gmmastros]

I don't immediately see anything wrong with your code. Can you post the error message that you are getting. Also, I suggest you look a couple lines above this one to see if there are any errors.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gtb12314]

Below is the error what I am getting:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

 

[gmmastros]

What is your compatibility level set to?

sp_dbcmptlevel [[!]YourDatabaseNameHere[/!]]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gtb12314]

what's this compatibility level? I am sorry I am new to SQl Server 2005. what level do I have to set and how to set it.

 

[gmmastros]

It's a little complicated, but I'll try to explain.

The WITH clause is new to SQL Server 2005.

So, if your database is SQL 7 or SQL 2000, then you cannot use the WITH clause. Each version of SQL Server adds new functionality, which will sometimes cause applications to fail (because the new functionality may interfere with older code). I've never seen this happen, but it's possible. So... Each database has a compatibility level associated with it. If the compatibility level is 70, then it's set for SQL7 compatibility. If compatibility = 80, then it's SQL2000 compatible. If compatibility = 90, then it's SQL2005 compatible.

So... even is a database is attached to a sql 2005 server, it may be using an older compatibility mode, which would prevent your code from running.

I suppose a good google search would probably explain it better than I could.


When I run this... [tt][blue]sp_dbcmptlevel [MyDBName][/blue][/tt]

I get...

[tt][blue]The current compatibility level is 90.[/blue][/tt]

If your value is 70 or 80, then you won't be able to use the WITH clause. Also... if you are running in an older compatibility mode, I would strongly encourage you to find out why. Is this a 3rd party DB that only supports SQL2000? Are there other reasons, etc....

Does this make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gtb12314]

Thank You so much gmmastros for your valuable help. I greatly appreciate it.