Remote table-valued function calls are not allowed

Question

Post reply

Remote table-valued function calls are not allowed

up.sivakumar

SSC-Addicted

Points: 487
More actions
September 19, 2014 at 1:37 am

#290091

SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)
WHERE <Condition>
The above SQL Script ran successfully up to yesterday. But today its throws the below error message.
Remote table-valued function calls are not allowed.
Now i have modified the SQL script as follows
SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] WITH (NOLOCK)
WHERE <Condition>
I want to know how the 1st SQL script runs successfully up to yesterday. Please clarify me
September 19, 2014 at 5:50 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Do you know what NOLOCK does? It may be perfectly fine in your situation, but you want to make sure you understand it.

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

up.sivakumar SSC-Addicted Points: 487 More actions · Answer 2

SELECT MAX(ID)

FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)

WHERE <Condition>

The above SQL Script ran successfully up to yesterday. But today its throws the below error message.

Remote table-valued function calls are not allowed.

My Doubt. How it was executed up-to yesterday? I don't want solution for the error message

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

up.sivakumar (9/19/2014)
SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)
WHERE <Condition>
The above SQL Script ran successfully up to yesterday. But today its throws the below error message.
Remote table-valued function calls are not allowed.
My Doubt. How it was executed up-to yesterday? I don't want solution for the error message

Is this now a table valued function instead of a base table? Something changed somewhere if the query worked and no doesn't.

As already stated you need to use the WITH keyword when using table hints. Leaving it off has been deprecated and it will be required at some point. I would recommend not using table hints at all but if you do, make sure you use that keyword.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

up.sivakumar SSC-Addicted Points: 487 More actions · Answer 4

No, I am using Base table. I didnt change anything on that query. I want to know how it was worked without "WITH" keyword upto yesterday. Please clarify me.

Now i have used "WITH" keyword and my problem got sloved.

I want to know how it was worked without "WITH" keyword upto yesterday. Please clarify me.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

up.sivakumar (9/19/2014)
No, I am using Base table. I didnt change anything on that query. I want to know how it was worked without "WITH" keyword upto yesterday. Please clarify me.
Now i have used "WITH" keyword and my problem got sloved.
I want to know how it was worked without "WITH" keyword upto yesterday. Please clarify me.

Did you change DB versions or run an update? Even in 2014 it is still only deprecated and not removed so not sure really. Do you actually need to use NOLOCK? Are accurate results not important for this query? You can and will get missing and/or duplicated rows when using NOLOCK.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Maybe someone changed the version level of the database? I think newer version(s) of SQL Server may require the WITH keyword??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service