none
Error in queries on linked tables to an SQL Server DB RRS feed

  • Question

  • Hello, I have a series of queries that have run for more than 3 years without any change, but since January 8 they started returning the error "Invalid procedure call" and I can not understand why, since nothing has changed in the code of the selects in question, can you help me understand what is happening?

    Query 1: (Runs fine, no errors)

    SELECT fpsession.transcript,
    InStr(1,[transcript],"from") AS From_1, 
    Mid([transcript],[From_1],80) AS texto_1,
    InStr(1,[texto_1],"/") AS Inicio_1, 
    InStr(1,[texto_1],">") AS Fim_1, 
    Mid([texto_1],[Inicio_1]+1,[Fim_1]-[Inicio_1]-2) AS Agente_1, 
    InStr([From_1]+[Fim_1],[transcript],"from") AS From_2, 
    Mid([transcript],[From_2],80) AS texto_2, 
    InStr(1,[texto_2],"/") AS Inicio_2, 
    InStr(1,[texto_2],">") AS Fim_2, 
    Mid([texto_2],[Inicio_2]+1,[Fim_2]-[Inicio_2]-2) AS Agente_2, 
    fpsession.sessionID, 
    fpsession.userID, 
    fpsession.workgroupID, 
    fpsession.startTime, 
    fpsession.endTime, 
    fpsession.queueWaitTime, 
    fpsession.state, 
    datediff("s",Data_Hora_INICIO,Data_Hora_FIM) AS Duracao_Minutos, 
    DateAdd("s",Round((Right([startTime],13)/1000),0),#1/1/1970#) AS Data_Hora_INICIO, 
    DateAdd("s",Round((Right([endTime],13)/1000),0),#1/1/1970#) AS Data_Hora_FIM, 
    Round(([fpsession]![queueWaitTime])/1000,0) AS Tempo_espera
    FROM fpsession
    WHERE fpsession.workgroupID="13" 
    And fpsession.transcript Is Not Null
    And YEAR(DateAdd("s",Round((Right([startTime],13)/1000),0),#1/1/1970#)) = 2018
    ORDER BY 20 DESC;

    Query 2: (Runs fine, no errors)

    SELECT [1_Filtro_VP].sessionID, 
    [1_Filtro_VP].workgroupID, 
    [1_Filtro_VP].queueWaitTime, 
    [1_Filtro_VP].state, 
    IIf([1_Filtro_VP].state<>2 Or [1_Filtro_VP].Duracao_Minutos=0,"",[1_Filtro_VP].Agente_1) AS Agente_final_1, 
    IIf([1_Filtro_VP].state<>2 Or [1_Filtro_VP].Duracao_Minutos=0,"",[1_Filtro_VP].Agente_2) AS Agente_final_2, 
    [1_Filtro_VP].Agente_1,
    [1_Filtro_VP].Agente_2,
    [1_Filtro_VP].Duracao_Minutos, 
    [1_Filtro_VP].Data_Hora_INICIO, 
    [1_Filtro_VP].Data_Hora_FIM, 
    [1_Filtro_VP].Tempo_espera, 
    CDate(Int([Data_Hora_INICIO])) AS DATA_Inicio, 
    Hour([Data_Hora_INICIO]) AS HORA_Inicio
    FROM 1_Filtro_VP;

    Query 3: ERROR "Invalid procedure call"

    SELECT [2_Resumo_VP].workgroupID, 
    [2_Resumo_VP].DATA_Inicio, 
    [2_Resumo_VP].HORA_Inicio, 
    [2_Resumo_VP].Agente_final_2, 
    [2_Resumo_VP].Data_Hora_INICIO AS DataHoraInicio, 
    [2_Resumo_VP].Data_Hora_FIM AS DataHoraFim
    FROM 2_Resumo_VP
    WHERE [2_Resumo_VP].workgroupID="13" 
    And YEAR([2_Resumo_VP].DATA_Inicio)=2018
    And [2_Resumo_VP].Agente_final_2<>""
    GROUP BY [2_Resumo_VP].workgroupID, 
    [2_Resumo_VP].DATA_Inicio, 
    [2_Resumo_VP].HORA_Inicio, 
    [2_Resumo_VP].Agente_final_2, 
    [2_Resumo_VP].Data_Hora_INICIO, 
    [2_Resumo_VP].Data_Hora_FIM
    ORDER BY [2_Resumo_VP].DATA_Inicio DESC;

    Monday, January 22, 2018 5:46 PM

Answers

  • Hello Deepak,
    Sorry for the delay in the reply but I finally found the reason for the error.
    Since the behavior was constant even when I created a new DB it ruled out the corruption issue, and the code of the queries was the same that has been in use for 3 years, the problem had to be in the data.
    This linked table has the data of user sessions on the Openfire SPARK app and in a session on January 10, for some reason I haven’t discovered yet (probably some problem in the app or the server), one of the fields of the SQL Server table that is not supposed to be allowed to be empty was empty, and that was causing this error.
    Problem solved, on to the next one …

    Thanks for your help and suggestions.

    Regards

    Vitor Almas

    • Proposed as answer by Paul P Clement IV Thursday, January 25, 2018 12:58 PM
    • Marked as answer by Vitor Almas Thursday, January 25, 2018 1:28 PM
    Thursday, January 25, 2018 11:09 AM

All replies

  • Hi Vitor Almas,

    Many reasons can produce this kind of issue.

    Did you install any Office or Windows related updates?

    If you did not check it then you can try to check it.

    Other possible reason is corruption.

    For that, you can try to compact and repair your database and check whether issue get solved or not.

    I want to confirm with you that are you running these queries from VBA code or from Access UI?

    If you are using any VBA code then you can also try to again set the references in VBA may help you to solve the issue.

    If the issue persist then you can again try to link your table with SQL DB and check whether it solves the issue or not.

    Let us know about the result of above mentioned suggestion.

    We will try to provide further suggestions, If needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 23, 2018 6:28 AM
    Moderator
  • Hello Deepak, First tanks for your reply, regarding the questions you make, the only updates I've installed are the Microsoft security updates for my system (Windows 7 Professional Service Pack 1 and the Office version is 2007) nothing else, and I'm running the queries directly in Access UI (Query options - Run), I don't believe that the problem is caused be the system updates because some queries run as always without any problems and others always return the error I mentioned, the 3 queries I have send are just part of a total of 9 and some of them run and others don't. I also thought of the corruption possibility and I have created a new database, linked the table from the SQL server and the behavior was the same (the queries that run in the old DB run in the new one, and other ones return the same error in booth DB's). Can you explain how I can implement your suggestion of using SQL DB to link the table? These are 2 more examples of 2 similar queries that one run and the other returns the error of "invalid procedure call". 1- Runs ok: SELECT [2_Resumo_VP].workgroupID, [2_Resumo_VP].DATA_Inicio, [2_Resumo_VP].HORA_Inicio, [2_Resumo_VP].Agente_final_2 AS LoginAgente, Count([2_Resumo_VP].state) AS Qtd_desistencias FROM 2_Resumo_VP WHERE [2_Resumo_VP].state=0 And [2_Resumo_VP].workgroupID="13" And YEAR([2_Resumo_VP].DATA_Inicio)=2018 GROUP BY [2_Resumo_VP].workgroupID, [2_Resumo_VP].DATA_Inicio, [2_Resumo_VP].HORA_Inicio, [2_Resumo_VP].Agente_final_2 ORDER BY [2_Resumo_VP].DATA_Inicio DESC; 2 - Runs with error: SELECT [2_Resumo_VP].workgroupID, [2_Resumo_VP].DATA_Inicio, [2_Resumo_VP].HORA_Inicio, [2_Resumo_VP].Agente_final_2 AS NomeAgente, Count([2_Resumo_VP].state) AS Qtd_atendidas FROM 2_Resumo_VP WHERE [2_Resumo_VP].state=2 And YEAR([2_Resumo_VP].DATA_Inicio)=2018 And [2_Resumo_VP].workgroupID="13" GROUP BY [2_Resumo_VP].workgroupID, [2_Resumo_VP].DATA_Inicio, [2_Resumo_VP].HORA_Inicio, [2_Resumo_VP].Agente_final_2 ORDER BY [2_Resumo_VP].DATA_Inicio DESC;
    Tuesday, January 23, 2018 3:31 PM
  • Hi Vitor Almas,

    To make sure that issue not caused by the update, You can try to run the same query on any other machine which does not have this update installed on it.

    It will help us to narrow down the issue.

    Is your ID saved as text?

    If possible you can try to remove all where clause and then try to run the query, Does it return same error?

    Further , You can try to post your sample database with dummy data in it.

    We will try to make a test on our side and try to reproduce the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 24, 2018 6:58 AM
    Moderator
  • Hello Deepak,
    Sorry for the delay in the reply but I finally found the reason for the error.
    Since the behavior was constant even when I created a new DB it ruled out the corruption issue, and the code of the queries was the same that has been in use for 3 years, the problem had to be in the data.
    This linked table has the data of user sessions on the Openfire SPARK app and in a session on January 10, for some reason I haven’t discovered yet (probably some problem in the app or the server), one of the fields of the SQL Server table that is not supposed to be allowed to be empty was empty, and that was causing this error.
    Problem solved, on to the next one …

    Thanks for your help and suggestions.

    Regards

    Vitor Almas

    • Proposed as answer by Paul P Clement IV Thursday, January 25, 2018 12:58 PM
    • Marked as answer by Vitor Almas Thursday, January 25, 2018 1:28 PM
    Thursday, January 25, 2018 11:09 AM