You must Sign In to post a response.
  • Category: SQL Server

    SQL Error : Remote table-valued function calls are not allowed


    Are you looking for a way to resolve SQL error ? want to resolve Remote table-valued function calls are not allowed ? then read this thread to know more about it



    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
  • #751814
    Hai SivS,
    It seems till yesterday there was no transactions were happening to your table by multiple users so it was working fine till yesterday.
    Now today when there was some multiple transactions on the same table, the deadlock condition occurs and then it was not working.
    By putting with Nolock, it avoid the dead lock condition and the transaction is successful.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #751816
    The below query working fine up to yesterday. I have used "NO LOCK" only. (Without "WITH NOLOCK".

    SELECT MAX(ID)
    FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)
    WHERE <Condition>

    But today its throws the below error message.

    Remote table-valued function calls are not allowed.

  • #751822
    This is because of the Locking mechanisham,

    if there is alock applied on the table it will throw the error, in your case it may not be there till yesterday. But now it seems that the locks are getting applied due to transaction on concurrant access.

    use the NO LOCK for it as


    ]

    SELECT MAX(ID)
    FROM [LinkedServer].[Database].dbo.[TableName] WITH (NOLOCK)
    WHERE <Condition>

    With (NOLOCK)

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #751825
    Hi,

    Already Pawan explained about that, up to yesterday only 1 person hit that query that means dead lock won't happen, so nolock condition will not fire but today more than 1 person hit that query so dead lock will happen that's the reason nolock will fire using with nolock condition will work. without that it won't work.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #751834
    When i execute the below query in server 1 it throws the error

    SELECT MAX(ID)
    FROM [Server 2].[Database].dbo.[TableName] (NOLOCK)
    WHERE <Condition>

    In above query i am using LinkedServer


    When i execute the below query in server 2 it throws the error it running successfully

    SELECT MAX(ID)
    FROM dbo.[TableName] (NOLOCK)
    WHERE <Condition>

    In above query i am not using LinkedServer

    I have used "NO LOCK" only. (Without "WITH NOLOCK").

  • #751847
    Hi SivS,

    Did you read the total post replies read the post responses first and then let me know if you still confuse in that.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #751861
    @naveensanagasetti, your reply is not clear to me


  • Sign In to post your comments