0

I get the error message as appears in the title and seen below when running a query against one of our SQL databases. The error seems to be consistent with this specific query when trying to return the @@ROWCOUNT of an executed transaction. However when returning @@ROWCOUNT within other queries, the error message does not appear.

I've been running the query from SSMS and haven't changed any of my connection settings. The query is quite long and I was a bit relucatant to post it however, please see it below.

Error Message:

The connection was recovered and rowcount in the first query is not available. Please execute another query to get a valid rowcount

SQL Syntax:

    SET NOCOUNT ON;
    DECLARE @MigrationTableKey      INT; 
    DECLARE @StartDtTime            DATETIME = GETDATE();
    DECLARE @EndDtTime              DATETIME = GETDATE();
    DECLARE @ChangeVersion          INT = 0;
    DECLARE @RowCount               INT = 0;
    DECLARE @TransferHistoryKey     INT = 0;
    SELECT  @MigrationTableKey      = MigrationTableKey FROM Administration.MigrationTables WHERE MigrationSchema   = 'dbo' AND MigrationTable      = 'DplanJobLineTask';
    SELECT  @ChangeVersion          = ISNULL(MAX(SYS_CHANGE_VERSION),0) FROM Logging.ChangeTrackingHistory WHERE MigrationTableKey = @MigrationTableKey;
    INSERT INTO dbo.DplanJobLineTask
        (
               [SYS_CHANGE_VERSION]
              ,[SYS_CHANGE_CREATION_VERSION]
              ,[SYS_CHANGE_OPERATION]
              ,[DtTimeAdded] 
              ,[Job No_]
              ,[Sub-Job Line No_]
              ,[Line No_]
              ,[Job Type]
              ,[Job Sub-Type]
              ,[Job Status]
              ,[Retailer Code]
              ,[Customer Store Code]
              ,[Bill-To Customer No_]
              ,[Supplier]
              ,[Brand]
              ,[Task Code]
              ,[Sub-Activity Code]
              ,[JAS Type Code]
              ,[Job Style]
              ,[Job Priority]
              ,[Brief Reqd]
              ,[Survey Requirements]
              ,[Parent Job From Year No_]
              ,[Parent Job From Week No_]
              ,[Parent Job To Year No_]
              ,[Parent Job To Week No_]
              ,[Job Description]
              ,[Job Short Name]
              ,[Customer Reference]
              ,[Customer Contact]
              ,[Billing Type]
              ,[Billable]
              ,[Billing Detail]
              ,[Preference - Sat]
              ,[Preference - Sun]
              ,[Preference - Mon]
              ,[Preference - Tue]
              ,[Preference - Wed]
              ,[Preference - Thu]
              ,[Preference - Fri]
              ,[Sub-Job Completed Date]
              ,[Region Code]
              ,[Area Code]
              ,[Tier1 Manager Code]
              ,[Tier2 Manager Code]
              ,[Tier3 Manager Code]
              ,[Ready to Archive]
              ,[Cancellation Reason]
              ,[Autocopy]
              ,[Copied From Job No_]
              ,[Copied From Job Line No_]
              ,[Reporting Deadline Day]
              ,[Shift Pattern Code]
              ,[Query Reasons]
              ,[Reset Flag]
              ,[Reset Reason]
              ,[Force Closed]
              ,[Previous Status]
              ,[Last Status Change Date]
              ,[Training Day Required]
              ,[POS required]
              ,[Stock Delivery To]
              ,[Multi-Region Code]
              ,[Supervisor (Store) Code]
              ,[Store Name]
              ,[Linked Job No_]
              ,[Group Job]
              ,[Captains Log Job No_]
              ,[Sub-Job No_]
              ,[Job Suffix Code]
              ,[Week No_ Code (JSx)]
              ,[Sub_ Activity Code (JSx)]
              ,[Task No_ Code (JSx)]
              ,[Job Weekly Summary Code]
              ,[Exported to D-Lite]
              ,[Job Status Changed]
              ,[Year No_ Code]
              ,[Sub-Job Year No_]
              ,[Sub-Job Week No_]
              ,[Week From Date]
              ,[Week To Date]
              ,[Fast Track]
              ,[Completed (D-Survey)]
              ,[Completed Date (D-Survey)]
              ,[Completed Time (D-Survey)]
              ,[Job Schedule Line No_]
              ,[Task Description]
              ,[Created Date]
              ,[Created Time]
              ,[Created by User ID]
              ,[Last Modified Date]
              ,[Last Modified Time]
              ,[Modified by User ID]
              ,[Tier1_Manager_Code_Id]
              ,[Tier2_Manager_Code_Id]
              ,[Tier3_Manager_Code_Id]
        )
    SELECT 

           ChangeTab.SYS_CHANGE_VERSION
          ,ChangeTab.SYS_CHANGE_CREATION_VERSION
          ,ChangeTab.SYS_CHANGE_OPERATION
          ,@StartDtTime 
          ,ChangeTab.[Job No_]
          ,ChangeTab.[Sub-Job Line No_]
          ,ChangeTab.[Line No_]
          ,JLine.[Job Type]
          ,JLine.[Job Sub-Type]
          ,JLine.[Job Status]
          ,JLine.[Retailer Code]
          ,JLine.[Customer Store Code]
          ,JLine.[Bill-To Customer No_]
          ,JLine.[Supplier]
          ,JLine.[Brand]
          ,JLine.[Task Code]
          ,JLine.[Sub-Activity Code]
          ,JLine.[JAS Type Code]
          ,JLine.[Job Style]
          ,JLine.[Job Priority]
          ,JLine.[Brief Reqd]
          ,JLine.[Survey Requirements]
          ,JLine.[Parent Job From Year No_]
          ,JLine.[Parent Job From Week No_]
          ,JLine.[Parent Job To Year No_]
          ,JLine.[Parent Job To Week No_]
          ,JLine.[Job Description]
          ,JLine.[Job Short Name]
          ,JLine.[Customer Reference]
          ,JLine.[Customer Contact]
          ,JLine.[Billing Type]
          ,JLine.[Billable]
          ,JLine.[Billing Detail]
          ,JLine.[Preference - Sat]
          ,JLine.[Preference - Sun]
          ,JLine.[Preference - Mon]
          ,JLine.[Preference - Tue]
          ,JLine.[Preference - Wed]
          ,JLine.[Preference - Thu]
          ,JLine.[Preference - Fri]
          ,JLine.[Sub-Job Completed Date]
          ,JLine.[Region Code]
          ,JLine.[Area Code]
          ,JLine.[Tier1 Manager Code]
          ,JLine.[Tier2 Manager Code]
          ,JLine.[Tier3 Manager Code]
          ,JLine.[Ready to Archive]
          ,JLine.[Cancellation Reason]
          ,JLine.[Autocopy]
          ,JLine.[Copied From Job No_]
          ,JLine.[Copied From Job Line No_]
          ,JLine.[Reporting Deadline Day]
          ,JLine.[Shift Pattern Code]
          ,JLine.[Query Reasons]
          ,JLine.[Reset Flag]
          ,JLine.[Reset Reason]
          ,JLine.[Force Closed]
          ,JLine.[Previous Status]
          ,JLine.[Last Status Change Date]
          ,JLine.[Training Day Required]
          ,JLine.[POS required]
          ,JLine.[Stock Delivery To]
          ,JLine.[Multi-Region Code]
          ,JLine.[Supervisor (Store) Code]
          ,JLine.[Store Name]
          ,JLine.[Linked Job No_]
          ,JLine.[Group Job]
          ,JLine.[Captains Log Job No_]
          ,JLine.[Sub-Job No_]
          ,JLine.[Job Suffix Code]
          ,JLine.[Week No_ Code (JSx)]
          ,JLine.[Sub_ Activity Code (JSx)]
          ,JLine.[Task No_ Code (JSx)]
          ,JLine.[Job Weekly Summary Code]
          ,JLine.[Exported to D-Lite]
          ,JLine.[Job Status Changed]
          ,JLine.[Year No_ Code]
          ,JLine.[Sub-Job Year No_]
          ,JLine.[Sub-Job Week No_]
          ,JLine.[Week From Date]
          ,JLine.[Week To Date]
          ,JLine.[Fast Track]
          ,JLine.[Completed (D-Survey)]
          ,JLine.[Completed Date (D-Survey)]
          ,JLine.[Completed Time (D-Survey)]
          ,JLine.[Job Schedule Line No_]
          ,JLine.[Task Description]
          ,JLine.[Created Date]
          ,JLine.[Created Time]
          ,JLine.[Created by User ID]
          ,JLine.[Last Modified Date]
          ,JLine.[Last Modified Time]
          ,JLine.[Modified by User ID]
          ,CASE WHEN ISNUMERIC(JLine.[Tier1 Manager Code]) = 1 THEN CAST(JLine.[Tier1 Manager Code] AS INT) ELSE NULL END [Tier1_Manager_Code_Id]
          ,CASE WHEN ISNUMERIC(JLine.[Tier2 Manager Code]) = 1 THEN CAST(JLine.[Tier2 Manager Code] AS INT) ELSE NULL END [Tier2_Manager_Code_Id]
          ,CASE WHEN ISNUMERIC(JLine.[Tier3 Manager Code]) = 1 THEN CAST(JLine.[Tier3 Manager Code] AS INT) ELSE NULL END [Tier3_Manager_Code_Id]
    FROM
        CHANGETABLE(CHANGES [DS-LOG-TEST].[dbo].[LIVE - DEE SET LOGISTICS$D-Plan Job Line Task],1) as ChangeTab
    LEFT JOIN
        [DS-LOG-TEST].[dbo].[LIVE - DEE SET LOGISTICS$D-Plan Job Line Task] JLine
    ON
        ChangeTab.[Job No_]  = JLine.[Job No_]  
    AND
        ChangeTab.[Line No_] = JLine.[Line No_]
    AND
        ChangeTab.[Sub-Job Line No_] = JLine.[Sub-Job Line No_]
    LEFT JOIN
        dbo.DplanJobLineTask JLine1
    ON
        ChangeTab.[Job No_]  = JLine1.[Job No_]  COLLATE DATABASE_DEFAULT
    AND
        ChangeTab.[Line No_] = JLine1.[Line No_]
    AND
        ChangeTab.[Sub-Job Line No_] = JLine1.[Sub-Job Line No_]
    LEFT JOIN
        Logging.ChangeTrackingHistory CTH
    ON
        ChangeTab.SYS_CHANGE_VERSION            = CTH.SYS_CHANGE_VERSION
    AND 
        ChangeTab.SYS_CHANGE_CREATION_VERSION   = CTH.SYS_CHANGE_CREATION_VERSION    
    AND
        CTH.MigrationTableKey = @MigrationTableKey
    WHERE
        JLine1.[Job No_]     IS NULL
    AND
        CTH.ChangeTrackingHistoryKey IS NULL
    AND
        ChangeTab.SYS_CHANGE_VERSION > @ChangeVersion;      

    INSERT INTO Logging.ChangeTrackingHistory
        (
             MigrationTableKey              
            ,SYS_CHANGE_VERSION         
            ,SYS_CHANGE_CREATION_VERSION    
        )
    SELECT
         @MigrationTableKey MigrationTableKey               
        ,DJL.SYS_CHANGE_VERSION         
        ,DJL.SYS_CHANGE_CREATION_VERSION    
    FROM
        dbo.DplanJobLineTask DJL
    LEFT JOIN
        Logging.ChangeTrackingHistory CTH
    ON
        DJL.SYS_CHANGE_VERSION          = CTH.SYS_CHANGE_VERSION
    AND 
        DJL.SYS_CHANGE_CREATION_VERSION = CTH.SYS_CHANGE_CREATION_VERSION    
    AND
        CTH.MigrationTableKey = @MigrationTableKey
    WHERE
        CTH.ChangeTrackingHistoryKey IS NULL
    GROUP BY
         DJL.SYS_CHANGE_VERSION         
        ,DJL.SYS_CHANGE_CREATION_VERSION;
    SELECT 
         @RowCount  = @@ROWCOUNT
        ,@EndDtTime = GETDATE();
  • Running a query from where? What is the basic structure of your query, is it part of a transaction, and what exactly is asking for or caring about rowcount? Is the query a select or an update/insert/delete/merge? Details, please. – Aaron Bertrand Apr 10 at 15:06
  • Hi @AaronBertrand, Apologies for the lack of detail, please see my updated message body. Thanks – Krishn Apr 10 at 15:24
  • I think this happens if the service has been restarted since you last ran any query in this window, something to do with an established connection. I wonder if the problem goes away if you include another batch first, e.g. SET NOCOUNT ON; PRINT 1; GO; – Aaron Bertrand Apr 10 at 15:31
  • The service has been up since yesterday. This is running on our test box. I plan on putting this query within a stored procedure so unfortuantely this would not work. The strange thing is, it doesn't always happen. If I replace the insert into with a select into, it's fine, equally when running an insert of object_ids from sys.objects into a newly created table, it ran without issue. – Krishn Apr 10 at 15:37
  • The service has been up since yesterday, but how long has your query window in SSMS been open? Anyway, I suspect you're not going to run into this issue during the execution of a stored procedure. This seems isolated to the weird connection semantics in SSMS (and that's what my suggestion was targeted at; preventing this scenario in SSMS). The key is to do something else to re-establish connection - just highlighting and executing SELECT @@VERSION; in that same window before running the rest of the script might be a solution too. – Aaron Bertrand Apr 10 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.