none
Issue with dynamic query using sp_executesql after the table data refresh

    Question

  • Hi,

    I have created a stored procedure in which I am using CTE with dynamic query and using sp_executesql to execute it. It worked fine on the initial data. But this dynamic query is built on a tables where data is completely refreshed periodically (i.e add new data and remove previous data). Once the new data is added the stored procedure is not working. There is no error but the output it is returning is 'NULL'. I had to restart the machine where the sql server is located to make it working again but when I refreshed the data again it stopped working again. This is the first time I am using CTE and sp_executesql to execute dynamic query. So, I might be missing something. The whole procedure is complicated so, I am skipping the dynamic query generation part instead I am going to type here what the dynamic query returns.

    Declare @sqlQuery nvarchar(max)
    SET @sqlQuery = N';WITH MaxDate As (Select Max(EventTime) as maxEventDate, ProductId from Orders temp where temp.EventTime <   DATEADD(mm, @counter+1, @StartDate) AND temp.OrderStatus IS NOT null AND temp.OrderStatus != '' group by ProductId) (select @OrdersCount = COUNT(*) from (select  DISTINCT(temp.ProductId) from Orders temp join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',  'Canceled') AND Level0 in ('Level1', 'Level2') UNION select  DISTINCT(temp.ProductId) from Orders temp join TrackBy t on t.OrderId = temp.Id  join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',  'Canceled') AND t.ProductName IN ('abc', 'def', 'xyz')) as u)'
    DECLARE @ParmDefinition nvarchar(500)
    SET @ParmDefinition = N'@counter int, @StartDate datetime, @SeverityName varchar(256), @DefectsCount int OUTPUT'

    In While loop I am calling sp_executesql and I am getting NULL in @Value:

    EXECUTE sp_executesql @sqlQuery, @ParmDefinition, @counter = @Counter, @StartDate = @StartDate, @SeverityName=@TmpSeverityName, @OrdersCount=@Value OUTPUT;
    Table definitions are:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Orders](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[productId] [varchar](50) NOT NULL,
    	[Severity] [varchar](128) NULL,
    	[Level0] [varchar](128) NULL,
    	[EventTime] [datetime] NULL,
    	[CreateDate] [datetime] NOT NULL,
    	[OrderStatus] [varchar](128) NULL
     CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    CREATE TABLE [dbo].[TrackBy](
    	[OrderId] [int] NOT NULL,
    	[ProductName] [varchar](256) NOT NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[TrackBy]  WITH CHECK ADD  CONSTRAINT [FK_TrackBy_Orders] FOREIGN KEY([OrderId])
    REFERENCES [dbo].[Orders] ([Id])
    ON DELETE CASCADE
    GO
    ALTER TABLE [dbo].[TrackBy] CHECK CONSTRAINT [FK_TrackBy_Orders]
    GO

    Could anyone help me to find what is that I am doing wrong to cause this problem? Currently I am running this in Sql server 2008, but at client it will be run in SQL server 2005 (which I will test it in 2005 before the release). So, I am trying to not use any 2008 features.

    Thank you in advance,

    Ana



    • Edited by Ana Ban Monday, March 12, 2012 3:00 PM Small correction in the query
    Monday, March 12, 2012 2:53 PM

Answers

All replies

  • One problem I see: you need to double up the single quotes for string literals:

    Declare @sqlQuery nvarchar(max)
    SET @sqlQuery = N';WITH MaxDate As (Select Max(EventTime) as maxEventDate, ProductId 
    from Orders temp where temp.EventTime <   DATEADD(mm, @counter+1, @StartDate) 
    AND temp.OrderStatus IS NOT null AND temp.OrderStatus != '' group by ProductId) 
    (select @OrdersCount = COUNT(*) from (select  DISTINCT(temp.ProductId) from Orders temp 
    join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId) 
     where Severity = @SeverityName AND temp.OrderStatus not in (''Completed'',  ''Canceled'') AND 
     Level0 in (''Level1'', ''Level2'') UNION select  DISTINCT(temp.ProductId) from Orders temp 
     join TrackBy t on t.OrderId = temp.Id  join MaxDate on ( temp.EventTime = MaxDate.maxEventDate 
     AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName 
     AND temp.OrderStatus not in (''Completed'',  ''Canceled'') AND t.ProductName IN 
     (''abc'', ''def'', ''xyz'')) as u)'
     
    PRINT @sqlQuery

    Dynamic SQL article:
    http://www.sqlusa.com/bestpractices/dynamicsql/

    In addition use the PRINT command to debug your dynamic query.


    Kalman Toth SQL SERVER & BI TRAINING




    Monday, March 12, 2012 3:12 PM
  • Thank you for responding SQLUSA. I forgot to double the single quotes when I was typing for the forum but in my procedure I am escaping the single quote with anothe single quote as you mentioned for string literals.

    Thanks you,

    Ana

    Monday, March 12, 2012 3:23 PM
  • I suggest to add printing the generated dynamic print statement. Most likely your dynamic print statement evaluates to NULL and that's why you get NULL as a result. If you print your SP, we may be able to spot the problem also, but that's my guess. If you add the printing statements, you will be able to catch it also.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 3:33 PM
  • Permit me a basic question: why are you using dynamic SQL at all? It is not apparent from the code you posted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 12, 2012 4:13 PM
  • Thank you Naomi for responding.  Here is a test script I wrote to explain my issue better:

    Declare  @SeverityName nvarchar(128) = N'High'
    Declare @TmpSeverityName varchar(128) = N'High'
    Declare @counter int = 0
    DECLARE @TimeNow datetime
    DECLARE @StartDate datetime = NULL
    DECLARE @EndDate datetime = NULL
    Declare @Value int
    IF @StartDate IS NULL 
    BEGIN
    	SET @TimeNow = GETDATE()
    	SET @StartDate = DATEADD(m, -11, CAST(FLOOR(CAST(@TimeNow AS DECIMAL(12, 5))) - (DAY(@TimeNow) - 1) AS DATETIME))
    	SET @EndDate = DATEADD(ss, -1, DATEADD(m, 12, @StartDate)) 
    END
    ELSE 
    BEGIN
    	SET @StartDate = CAST(FLOOR(CAST(@StartDate AS DECIMAL(12, 5))) - (DAY(@StartDate) - 1) AS DATETIME)
    	IF @EndDate IS NULL
    		SET @EndDate = DATEADD(ss, -1, DATEADD(m, 12, @StartDate))
    	ELSE 
    		SET @EndDate = DATEADD(ss, -1, DATEADD(m, 1, CAST(FLOOR(CAST(@EndDate AS DECIMAL(12, 5))) - (DAY(@EndDate) - 1) AS DATETIME)))
    END
    Declare @DefectsCount int
    Declare @DefectsCount2 int
    Declare @query nvarchar(max) = N';WITH MaxDate As (Select Max(EventTime) as maxEventDate, ProductId from Orders temp where temp.EventTime <   DATEADD(mm, @counter+1, @StartDate) AND temp.OrderStatus IS NOT null AND temp.OrderStatus != '''' group by ProductId) (select @DefectsCount = COUNT(*) from (select  DISTINCT(temp.ProductId) from Orders temp join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in (''Completed'',  ''Canceled'') AND Level0 in (''Level1'', ''Level2'') UNION select  DISTINCT(temp.ProductId) from Orders temp join TrackBy t on t.OrderId = temp.Id  join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in (''Completed'',  ''Canceled'') AND t.ProductName IN (''abc'', ''def'', ''xyz'')) as u)'
    Declare @ParmDefinition nvarchar(max) = N'@counter int, @StartDate datetime, @SeverityName varchar(256), @DefectsCount int OUTPUT'
    PRINT @query;
    while @counter < 12 BEGIN
    EXEC sp_executesql @query, @ParmDefinition, @counter = @counter, @StartDate = @StartDate, @SeverityName=@TmpSeverityName, @DefectsCount=@Value OUTPUT
    Select @Value as DefectsCount -- this prints NULL
    ;WITH MaxDate As (Select Max(EventTime) as maxEventDate, ProductId from Orders temp where temp.EventTime <   DATEADD(mm, @counter+1, @StartDate) AND temp.OrderStatus IS NOT null AND temp.OrderStatus != '' group by ProductId) (select @DefectsCount2 = COUNT(*) from (select  DISTINCT(temp.ProductId) from Orders temp join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',  'Canceled') AND Level0 in ('Level1', 'Level2') UNION select  DISTINCT(temp.ProductId) from Orders temp join TrackBy t on t.OrderId = temp.Id  join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',  'Canceled') AND t.ProductName IN ('abc', 'def', 'xyz')) as u)
    Select @DefectsCount2 as DefectsCount2 -- this prints the count the matches the criteria
    SET @counter =  @counter+1
    END

    I printed the @query and used it to execute the printed query directly and it prints the count correctly but the call to 'EXEC sp_executesql' statement prints NULL. So, do you see any issue from the above script.

    Thank you so much for your help,

    Ana


    • Edited by Ana Ban Tuesday, March 13, 2012 8:25 PM Changed the script to print @Value instead of @DefectsCount
    Monday, March 12, 2012 4:20 PM
  • I dynamically generate the query based on the values sent for parameters of the store procedure (which I haven't included in this forum) and also, once I generate the query I use it in a while loop with different values for @Counter, @SeverityName etc. (in the above script). I thought reusing same query is one of the advantages of using dynamic query, please let me know if my understanding is wrong.

    Thank you,

    Ana

    Monday, March 12, 2012 4:26 PM
  • I believe your value will be in the @Value variable. E.g. test @Value after each execute.

    Also, I don't understand the floor manipulations with the dates. Can you explain what do you want to get for @StartDate and @EndDate - may be there is a better way to set them up. I understand that this is irrelevant to the problem, but we may want to correct this issue also.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 4:39 PM
  • Ahhh... you are right Naomi, I should have selected @Value no @DefectsCount.

    But, with the same script if the data refreshes, the 'EXEC sp_executesql'  statement is returning NULL and the next statement where I am executing the statement directly returns the correct count. This is happening only after the data refresh until I restart the machine. On restart it works fine until the data is refreshed again. An asp.net webservice refreshes the data. The webservice first adds the new data and then removes the old data. Since direct execution is working but not with sp_executesql, making me think may be there is a issue with sp_executesql cache are something, for which I am a novice user, don't know a lot about. That's why I am turning to you experts.

    Please anyone with any ideas are appreciated.

    Thank you in advance,

    Ana

    Monday, March 12, 2012 5:54 PM
  • Naomi,

    Regarding date manipulation, what I am trying to do is, if the startdate is NULL, trying to set the startdate to 11 months prior to current month with date and time being first day of that month at midnight and endDate is last day of the current month with time 23:59:59pm. In the same way if StartDate is sent, trying to get to the first of the month at midnight and EndDate to the last day of the month with time 23:59:59pm.

    Thank you,

    Ana

    Monday, March 12, 2012 5:59 PM
  • Try to put the script to refresh data into a transaction with COMMIT at the end and then execute your exec script. It looks like a timing issue and your execscript doesn't yet see the refreshed data. Perhaps adding waitfor delay can also help here.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 6:03 PM
  • IF @StartDate IS NULL    set @StartTime = dateadd(month, -11 +

    datediff(month, '19000101',CURRENT_TIMESTAMP),'19000101') -- 11 months back from the current date with the beginning of the month   set @EndDate = dateadd(month, 12, @StartDate)

    -- don't use 1 second prior to midnight, use next day and >= < for date comparison.


    See this article

    Bad habits to kick : mis-handling date / range queries


    For every expert, there is an equal and opposite expert. - Becker's Law 



    My blog



    Monday, March 12, 2012 6:07 PM
  • Naomi,

    But I am seeing the updated data in management studio even before I run this dynamic sql. Even one hour after the refresh, still I am getting NULL.

    Thank you,

    Ana

    Monday, March 12, 2012 6:31 PM
  • Naomi, I will use your method of calculating dates.

    Thank you,

    Ana

    Monday, March 12, 2012 6:32 PM
  • I think we may want to see the whole process code here as it is hard to figure out the problem based on what you said so far. Something is cached too aggressively, but why and how - I don't know.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 6:48 PM
  • Naomi,

    The test script I added above demonstrates the problem, before the refesh EXEC Sp_executesql works and after the refresh its not. Which other code you want me to provide? webservice code?

    Thank you,

    Ana

    Monday, March 12, 2012 7:01 PM
  • Naomi, is there a way I can clear the cache to see if that fixes the problem?

    Thanks,

    Ana

    Monday, March 12, 2012 7:02 PM
  • I don't see 'refresh' in that code. What do you mean by that? If you're using development database and server, you can use freeproccache to clear the cache. You can not do this on production database.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 7:27 PM
  • Naomi,

    The refresh is done by an asp.net webservice. It simply inserts rows in to the tables (Orders, Trackby) and deletes the old rows when all rows are added. Do you need the code for it?

    Thank you,

    Ana

    Monday, March 12, 2012 7:43 PM
  • Naomi,

    On the test db, I ran 'DBCC freeproccache ' to see if the issue gets fixed and it did fix it. Now the statement returns values as expected. As you said I don't want to use it in the production db, but does this help in finding solution to this problem?  Is there a way I can force to regenerate action plan for the generated query?

    Thank you,

    Ana

    Monday, March 12, 2012 7:53 PM
  • Try adding OPTION RECOMPILE to your stored procedure. Alternatively, you can also run DBCC freeproccache for only this plan handle (see more info in BOL). However, this is strange - I would expect different query execution time, but not the different result.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 7:56 PM
  • Naomi,

    Can you please provide syntax for creating stored procedure with OPTION RECOMPILE ? I used same syntax as 'WITH RECOMPILE' and it complained 'Incorrect syntax'. I tried to look online with no luck.

    Thank you,

    Ana

    Monday, March 12, 2012 8:16 PM
  • Can you please provide syntax for creating stored procedure with OPTION RECOMPILE ? I used same syntax as 'WITH RECOMPILE' and it complained 'Incorrect syntax'. I tried to look online with no luck.

    Look at the 3rd example at the following page:
    http://www.sqlusa.com/bestpractices/parameter-sniffing/

    The stored procedure is created with the RECOMPILE option.

    You can also specify OPTION(RECOMPILE) at the query level within a stored procedure:

    http://www.sqlmag.com/article/sql-server/-using-the-recompile-query-hint-to-solve-parameter-sniffing-problems


    Kalman Toth SQL SERVER & BI TRAINING


    Monday, March 12, 2012 8:22 PM
  • OPTION RECOMPILE can be added to individual select statements. For the whole procedure you add WITH RECOMPILE to the ALTER PROCEDURE statement.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 8:25 PM
  • I have added the WITH RECOMPILE to the stored procedure where the dynamic query is generated and executed with sp_executesql but that did not fix the problem, I am still getting NULL. Instead of adding WITH RECOMPILE to the store procedure, do I need to add OPTIONE RECOMPILE on dynamic query itself? If so, where do i add it for this query? eash select statement in the dynamic query? I tried to add at the end of the dynamic query but it complains about the syntax.

    Declare @sqlQuery nvarchar(max)
    SET @sqlQuery = N';WITH MaxDate As (Select Max(EventTime) as maxEventDate, ProductId from Orders temp where temp.EventTime <   DATEADD(mm, @counter+1, @StartDate) AND temp.OrderStatus IS NOT null AND temp.OrderStatus != '' group by ProductId) (select @OrdersCount = COUNT(*) from (select  DISTINCT(temp.ProductId) from Orders temp join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',  'Canceled') AND Level0 in ('Level1', 'Level2') UNION select  DISTINCT(temp.ProductId) from Orders temp join TrackBy t on t.OrderId = temp.Id  join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)  where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',  'Canceled') AND t.ProductName IN ('abc', 'def', 'xyz')) as u)'
    DECLARE @ParmDefinition nvarchar(500)
    SET @ParmDefinition = N'@counter int, @StartDate datetime, @SeverityName varchar(256), @DefectsCount int OUTPUT'

    Thank you,

    Ana

    Monday, March 12, 2012 8:33 PM
  • I dynamically generate the query based on the values sent for parameters of the store procedure (which I haven't included in this forum) and also, once I generate the query I use it in a while loop with different values for @Counter, @SeverityName etc. (in the above script). I thought reusing same query is one of the advantages of using dynamic query, please let me know if my understanding is wrong.

    That does not explain why you need to make things more difficult by using dynamic SQL.

    Instead of:

    Declare @sqlQuery nvarchar(max)
    SET @sqlQuery = N';WITH MaxDate As (Select Max(EventTime) as maxEventDate, ProductId from Orders temp where temp.EventTime <     DATEADD(mm, @counter+1, @StartDate) AND temp.OrderStatus IS NOT null AND temp.OrderStatus != '' group by ProductId) (select @OrdersCount = COUNT(*) from (select    DISTINCT(temp.ProductId) from Orders temp join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)    where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',    'Canceled') AND Level0 in ('Level1', 'Level2') UNION select    DISTINCT(temp.ProductId) from Orders temp join TrackBy t on t.OrderId = temp.Id    join MaxDate on ( temp.EventTime = MaxDate.maxEventDate AND temp.ProductId = MaxDate.ProductId)    where Severity = @SeverityName AND temp.OrderStatus not in ('Completed',    'Canceled') AND t.ProductName IN ('abc', 'def', 'xyz')) as u)'
    DECLARE @ParmDefinition nvarchar(500)
    SET @ParmDefinition = N'@counter int, @StartDate datetime, @SeverityName varchar(256), @DefectsCount int OUTPUT'

    Why not simply use:

    ;WITH MaxDate As
         (Select Max(EventTime) as maxEventDate, ProductId
            from Orders temp
            where temp.EventTime <    DATEADD(mm, @counter+1, @StartDate)
             AND    temp.OrderStatus IS NOT null
             AND temp.OrderStatus != ''
             group by ProductId)
    select @OrdersCount = COUNT(*)
    from    (select    DISTINCT(temp.ProductId)
                 from        Orders temp
                 join     MaxDate on temp.EventTime = MaxDate.maxEventDate
                                             AND temp.ProductId = MaxDate.ProductId
                 where Severity = @SeverityName
                    AND temp.OrderStatus not in ('Completed',    'Canceled')
                     AND Level0 in ('Level1', 'Level2')
                 UNION
                 select    DISTINCT(temp.ProductId)
                 from        Orders temp
                 join        TrackBy t on t.OrderId = temp.Id
                 join        MaxDate on    temp.EventTime = MaxDate.maxEventDate
                                                AND    temp.ProductId = MaxDate.ProductId
                 where Severity = @SeverityName
                     AND temp.OrderStatus not in ('Completed',    'Canceled')
                     AND t.ProductName IN ('abc', 'def', 'xyz') as u)

    Dynamic SQL increases the level of complexity in your coding considerably, so there is all reason to avoid it if possible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 12, 2012 11:12 PM
  • Erland asks a valid question - can you explain why you're using dynamic SQL?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 11:15 PM
  • In the real stored procedure I don't directly get the query (i.e string in @sqlQuery ) and execute it. What I do is, stored procedure has 2 string, 2 boolean and 2 datetime parameters. Based on those parameters I generate the query, like add extra where conditions add/or  add extra joins add/or union statemenets.  I am using string concatinations to add these extra conditions/joins/unions. Isn't it a good candidate for dynamic SQL? So, when is a dynamic SQL really used?

    Also, shouldn't sql server automatically clear the cache for a statement for a table if the most of the data in the table is modified?

    If I can't use the dynamic SQL, do i need to create bunch of if, elses in combination with  left inner joins and case statements in where clauses? Then what about unions? Can we add conditional unions?

    Thank you,

    Ana


    • Edited by Ana Ban Tuesday, March 13, 2012 2:54 PM
    Tuesday, March 13, 2012 2:52 PM
  • No, this case (if some parameters are optional) is a perfect candidate for the dynamic SQL. So, you're saying that you always generate new @SQL string and then do execute sp_executeSQL @Sql, @ParamDefinition, @Param1, @Param2, etc.

    This is correct way.

    If you want to add OPTION recompile, here is a sample:

    set @SQL = @SQL + ' OPTION (RECOMPILE)'

    If your SQL is complex with UNIONs, you may try

    set @SQL = 'SELECT * FROM (' + @SQL + ') X OPTION (RECOMPILE)'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Tuesday, March 13, 2012 2:58 PM
  • Yes, I am always generating new @SQL string and then do execute sp_executeSQL @Sql, @ParamDefinition, @Param1, @Param2 in a while loop.

    I tried,  set @SQL = 'SELECT * FROM (' + @SQL + ') X OPTION (RECOMPILE)' and I am getting syntax error for my sql query which has CTE , which is added in the thread ealrier. The exact error is:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near ';'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near ')'.

    Looks like it didn't like it with semi colon in CTE. And the first method didn't work either. Its returning nothing. I Copied the same query in management studio and replaced the parameters with values and ran the query after adding OPTION (RECOPILE), it returned nothing and in the Messages tab it said "Command(s) completed successfully". If I remove OPTION(RECOMPILE) it returns correct count.

    Thank you,

    Ana

    Tuesday, March 13, 2012 3:26 PM
  • Looks like OPTION (RECOMPILE) doesn't help in your case, then. You may alternatively also try OPTIMIZE FOR UKNOWN (see suggestions in

    this article http://pratchev.blogspot.com/2007/08/parameter-sniffing.html )


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 13, 2012 3:43 PM
  • I have tried OPTIMIZE FOR UKNOWN, but once the data is refreshed same issue as before, its not returning anything. I don't understand why changing the data causing this problem? Is there any solution for my problem? any ideas?

    Thank you,

    Ana

    Tuesday, March 13, 2012 4:16 PM
  • I don't understand this problem either. Why would your code run against the cached version of the tables instead of reading the tables from the disk? You may want to also ask this question in the Database Engine forum here and in this case please give a link in this thread.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 13, 2012 4:51 PM
  • Would it be possible for you to store the dynamic query in a table together with all paramters used for that query and a datetime column to see what SQL code is generated before and after the recompile?

    You'd need to make sure the code is changed before the table gets refreshed. Otherwise the code change will cause a recompile, too.

    Then you could compare the SQL statements before and after. This might help to narrow down the issue.

    What I'm wondering:@TimeNow is not set if @StartDate IS NOT NULL ...

    Tuesday, March 13, 2012 6:36 PM
  • Is there any chance that the MAXDATE table (most likely MaxDate, but remotely possible that it's Orders or Trackby) are dropped and recreated, but momentarily empty until some other job/query/proc runs, and that the real issue isn't your proc, but that MAXDATE is empty when you call your proc?

    The thread is too long for me to read:  Did you say you can manually go into SSMS and run the generated query or its equivalent successfully at teh same time that your Proc is returning 0 records?

    For fun, you might also want to look at every log you can find too:  EventViewer on the SQL box, all the SQL error logs, and the IIS logs if applicable, starting at or close to the time that your proc returns 0 records.

    Tuesday, March 13, 2012 6:41 PM
  • MaxDate is Common Table Expression based on Orders table itself. And Orders and Trackby tables are not dropped or recreated.

    Even generated query doesn't run in SSMS after the data refresh. I also looked at the logs and nothing specific there.

    Tuesday, March 13, 2012 8:16 PM
  • I like the idea of LMU92 of tracking the generated SQL and the exact time of its execution. You can compare it with the time of the data refresh. Is it possible that the execution time is the same as the refresh?

    Also, are you talking about a single DB and server or there is mirror or replication involved?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 13, 2012 8:22 PM
  • LMU92,

    I can definetely store in a table or even print temporarly all the values, but I don't understand what you mean by 'before and after the recompile'.

    Also you said 'Then you could compare the SQL statements before and after.', i don't understand what is before and after. Could you please clarify it.

    @TimeNow is only used when @StartDate is NULL to figure out a start date. Its not used if @Start date is specified.

    Thank you,

    Ana

    Tuesday, March 13, 2012 8:32 PM
  • Just for curiosity, try this?

    Without using a variable, but instead via the REPLACE command, change the main CTE name "MaxDate" to a new name each time through.  You may end up with lots of extra execution plans, but it's worth a try, given the mystery.  (The theory is, perhaps since it's the SAME query plan each time but with the variables changing, weird caching things are happening not necessarily with the data, but with the query plan).

    For example, "Set @Query = Replace(@query, 'MaxDate', 'MaxDate2')", and possibly get a little fancy by incrementing the name each new run of the proc (not necessarly for each of the 12 passes  (worry about the details later).  But the main experiment, does changing the name lead to a new and different execution plan being made, which resolves this?

    Tuesday, March 13, 2012 8:35 PM
  • Thank you Johnqflorida!!!!!!!!

    Your idea with REPLACE worked. I have added millseconds from epoch to the MaxDate and replaced MaxDate with it and it worked. So, can I use this for my production or are there any problems having lots of execution plans perfomance wise or otherwise? Or is there a better solution than this to avoid creating lots of execution plans?

    Thank you,

    Ana

    Tuesday, March 13, 2012 9:14 PM
  • Can you also post the result of the SELECT @@Version? The issue you're showing sounds like a some sort of a bug in SQL Server and you may want to open a support case with MS.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 13, 2012 9:51 PM
  • In the real stored procedure I don't directly get the query (i.e string in @sqlQuery ) and execute it. What I do is, stored procedure has 2 string, 2 boolean and 2 datetime parameters. Based on those parameters I generate the query, like add extra where conditions add/or  add extra joins add/or union statemenets.  I am using string concatinations to add these extra conditions/joins/unions. Isn't it a good candidate for dynamic SQL? So, when is a dynamic SQL really used?

    Could be.

    Also, shouldn't sql server automatically clear the cache for a statement for a table if the most of the data in the table is modified?

    The problem is that we don't see the full picture. It is not uncommon that using DBCC FREEPROCCACHE etc can have an effect on execution times due to parameter sniffing.

    However, the result of the query should always be the same. It seemed for a while here that OPTION (RECOMPILE) could be the solution, but it's probably not the case. Something goes wrong for whatever reason. Exactly which version of SQL Server do you have? That is, what does "SELECT @@version" return?

    If you have an old version, you should apply the latest service pack, in case you are running into a bug that has been fixed.

    If that is not the solution, my advice is that you skip the dynamic SQL and write it all with static SQL. The combination of whatever problem there is and the fact that you are not very experienced, makes this a difficult equation. We can produce guesses all night long, but we are really fumbling in the dark.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 13, 2012 10:11 PM
  • What, no "Propose as Answer"?  ;-)

    And to answer your question, it's no more wasteful than if you had dynamically parsed the query on the fly (where each query would have been different), instead of using parameters.  But it is more wasteful than if you could have designed the overall solution without dynamic queries, though.  But like triggers and cursors, every now (even though it's more rare than people think), there just is a time when you have to use dynamic sql too.

    Any chance something like this would work?  (pseudocode):

    With TwelveMonths (MonthsAgo) as

    (Select -1 UNION Select -2... UNION Select -12)

    , DateList (MonthsAgo, StartDate, EndDate) as

    (Select Monthsago, DateAdd(mm, Monthsago...) from TwelveMonths

    (I'm not at my work machine to write this better, but basically, what if you created a CTE, consisting of 12 rows, and computed 12 months ago, 11 months ago, etc..., then joined on that first table to recreate your 12 sets of data for your business purpose?

    With regard to this being a bug, I tend to agree, it probably is.  How many people on earth have created a Stored Proc that creates Dynamic SQL, and on top of it all, using Parameters AND a CTE, I bet there's not many!  Still, if it's supposed to work, it should work correctly, so hopefully it will be addressed someday.

    Tuesday, March 13, 2012 10:44 PM
  • Version is :

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Wednesday, March 14, 2012 2:31 PM
  • Try installing the latest service pack which is SP3 for SQL Server 2008. If you can still reproduce the problem, you need to prepare a test case and open a Connect issue or a ticket with MS.

    See this link for Download

    http://www.microsoft.com/download/en/details.aspx?id=27594


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    • Edited by Naomi NModerator Wednesday, March 14, 2012 3:16 PM
    • Marked as answer by Ana Ban Wednesday, March 14, 2012 5:21 PM
    Wednesday, March 14, 2012 2:35 PM
  • The latest service pack for SQL 2008 is SP3.

    And, yes, Ana should definitely try to get the service pack installed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 14, 2012 3:13 PM
  • Thank you everyone who have answered! I installed SP2 on SQL 2008 and it fixed the issue. I tested this in SQL server 2005 SP4, which is what our client has, and it is behaving little bit differently, i.e, after the refresh the first call to the stored procedure is not working (i.e returns all counts 0, even though the data is committed) but from the second call onwards it works fine.

    Wednesday, March 14, 2012 5:26 PM
  • Which SP did you install? The latest SP for SQL Server 2008 is SP3, I made a typo (corrected) in my first post.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, March 14, 2012 5:30 PM