Cross Apply with parameterized function

Question

Cross Apply with parameterized function

JohnG69

Hall of Fame

Points: 3114
More actions
September 9, 2015 at 12:04 pm

#399015

Hi all,
It's been a few weeks now that I'm getting calls from some clients due to a syntax error. I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.
The error is: Incorrect syntax near '.'
the query in question resembles this:
Select column1, column2
from Table1 T
cross apply function(t.column4,t.column5) F
where column3 = 'XXXX'
I made sure that the compatibility level is greater than 90
this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)
Can it be the .net framework? Although the machines had .net framework 3.52.
Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2
I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.
I'm out of ideas, can someone tell me what other components I should be looking at?
Thank you
Much Appreciated.
JG

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

jghali (9/9/2015)
Hi all,
It's been a few weeks now that I'm getting calls from some clients due to a syntax error. I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.
The error is: Incorrect syntax near '.'
the query in question resembles this:
Select column1, column2
from Table1 T
cross apply function(t.column4,t.column5) F
where column3 = 'XXXX'
I made sure that the compatibility level is greater than 90
this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)
Can it be the .net framework? Although the machines had .net framework 3.52.
Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2
I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.
I'm out of ideas, can someone tell me what other components I should be looking at?
Thank you
Much Appreciated.
JG

I can't picture this being an OS issue, or the version of .Net

but not all clients are suffering from the same problem

Okay, let's work one problem at a time.

Exactly which versions of SQL are having this issue?

Also, would you mind posting the exact query that is having the problem (not the pseudo-code that you have)?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I'd also be looking at how SQL Server was installed at the clients where things are failing versus where it isn't failing. It is peculiar that it is failing on the dot (.). Only thing that comes to my mind is differences in collation and that is based on the "example" code you posted.

I agree, we really need to see the actual query having the problem.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

Check the compatibility level of the dbs. If that is too low, you'll get that syntax error, since earlier versions of SQL didn't allow that type of reference.

Edit: OOPS, just saw this in your original post:

I made sure that the compatibility level is greater than 90

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JohnG69 Hall of Fame Points: 3114 More actions · Answer 4

we received calls from clients that are using SQL2005 SP2 - I even had them upgrade their service Pack. didn't work

another client, had the same issue with SQL 2008 R2 SP2 - updating the Service packs didn't help.

I have a customer that i will be calling in the morning (EST)... as usual I will verify the compatibility Level - I never thought of verifying the collation so I will do that too.

Since the customers are on production servers I have no way to test on their servers.

So I'm trying to take as much info as possible to try to reproduce in our lab.

Since I couldn't reproduce, i suggested that they upgrade their servers with new installations of OS and SQL2014 express and that worked... until one client decides to send me to hell.

It's not a solution.

as for the exact query, It's a little too much to ask since it is private property and I've only started here 2 months ago and still on probation until I hit 6 months 😉

but I can tell you that it's the same concept as stated in my previous post...

The query works for most customers... why by upgrading OS and SQL server for those clients that are having problems does it work?

As I mentioned, I will be connecting to the customer's tomorrow and will try to write down as much info as possible and come back with that info here...

collation

compatibility level

version(s) .net framework installed

sql server version

windows version

anything else I should look at?

I really appreciate all your help...

thanks

JG

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

jghali (9/9/2015)
anything else I should look at?

Yes... despite your misgivings, you can't fix a car that you can't touch never mind see. Ask for a copy of the actual unmodified code and a copy of the function. And have them get into the habit of using the 2 part naming convention, as well.

Shooting in the dark, have you made sure that the function is a table valued function of one sort or another because a scalar function isn't going to work here and scalar functions don't normally work without the 2 part naming. That may be where the error near the period is coming from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

Just out of curiosity are all your clients using the same version of whatever app they are using to connect to the database?

JohnG69 Hall of Fame Points: 3114 More actions · Answer 7

so I connected to the client...

took all the info down and even took a backup of his DB.

compared everything with my lab and all is the same... My Lab Works and the clients doesn't.

I was able to reproduce the error in my Lab only when I restored his DB.

The question now is, What in his DB can cause a cross Apply with parameterized function to give a syntax error?

Version of Windows: Windows 7 Pro SP1

Version of SQL Server Express 2008: 10.50.4042.0

the info regarding the DB is as follows:

collation is: SQL_Latin_General_CP1_CI_AS

compatibility Level: 100

the rest of the DB Properties are identical between my working lab and the client's DB.

Let me tell you that the cross apply has been introduced, in our software, in our latest release, and many clients use this release only a few (3 until now) are having this problem.

I understand that its a shot in the dark, but What I'm trying to get is some ideas to go deeper in my research to figure it out.

There's nothing different in the query... I took the existing query and simplified it to what I posted here and it still gives the error only on his DB...

The only way I got it to work on his DB is by putting constants in the Parameterized function.

Any other ideas?

again, I really really appreciate all of your help.

Thank you

JG

JohnG69 Hall of Fame Points: 3114 More actions · Answer 8

More development for your info...

I think I might have put my finger on the problem...

When I restored the database in my lab, the database restored with a compatibility level of 80.

I have a feeling that although the compatibility level is showing 100 in the GUI, I don't think it converted properly to 100.

I'm waiting to connect to the client again to verify the compatibility level using TSQL and alter the database to the 100 compatibility level...

The client did the upgrade, I'm not sure if he got an error or what exactly happened but have you ever seen such a problem?

if a backup is taken from a DB that has a compatibility level of 100 it should restore as 100... but since it restored to 80, I suspect that there's an issue with the compatibility level.

I will let you know if this fixes the issue or not so that this can be documented somewhere on the web for the next person that has this issue...

Thank you all for your help.

JG

JohnG69 Hall of Fame Points: 3114 More actions · Answer 9

Hi all,

As I suspected the problem was the compatibility level. Although it showed 100, it was not really converted. by downgrading it to 90 and pressing OK (It looked like it was processing something) and then upgrading it again and pressing OK (This part didn't seem to process much). The query finally worked.

Thank you

John