Idera analysis is suggesting I:

USE [tempdb]

ALTER PROC ##zDP_235302...

Is this really a good idea? Most of the "fixes" their diagnostic software suggests is spot on, but this kind of makes me take a step back. There is a long list of these procedures in tempdb they suggest altering.

  • 2
    I don't get their suggestion. Is it telling you to change the SET options? Or suggesting you use temporary stored procedure? Could you elaborate what it shows? – Tara Kizer May 16 '17 at 20:52
  • Is this Dynamics? – Erik Darling May 16 '17 at 21:35
  • SQL Module tempdb.dbo.##zDP_2353027SD was found with ANSI_NULLS and/or QUOTED_IDENTIFIER set to OFF. These settings can prevent the query optimizer from using an index seek for certain queries. – k1rk May 17 '17 at 16:15
  • it is dynamics GP – k1rk May 17 '17 at 16:18
  • Here is another smaller query example: USE [tempdb] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER OFF GO ALTER PROC ##zDP_1450622S_1 (@RunReportID int) AS /* 14.00.0085.000 */ set nocount on SELECT TOP 1 RunReportID, Group_ID, CMPANYID, USERID, MasterDocumentName, OutputFileType, DATE1, TIME1, Count1, SuccessCount, FailureCount, Status, PRTOSCRN, PRTOPRTR, PRNTOFIL, ERRSTATE, DEX_ROW_ID FROM .##1450622 WHERE RunReportID = @RunReportID ORDER BY RunReportID ASC set nocount off – k1rk May 17 '17 at 16:25

There's no harm in doing it, but it'll just go away next time the service is restarted. If it's something that really needs to be done, you'd have to alter the code that is creating the procedure, wherever that lives. It could be they are in the model database.

| improve this answer | |
  • 1
    How do you know there's no harm if you haven't seen the change? – Erik Darling May 16 '17 at 23:47
  • good point but I do have a little trust in Idera...if it has DROP DATABASE in it I would think they'd get a few calls. – SqlACID May 17 '17 at 0:32
  • Best of luck with that. – Erik Darling May 17 '17 at 1:15

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.