Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Stored Procedure Question

Expand Messages
  • Charles Carroll
    No idea. It was a great product pre-SQL Server. RedGate and Embracedero probably have that capability or should. As to support you may want to ask them. They
    Message 1 of 9 , Apr 12, 2012
    • 0 Attachment
      No idea. It was a great product pre-SQL Server.

      RedGate and Embracedero probably have that capability or should.

      As to support you may want to ask them. They are not a thriving company but
      the Access and Xbase crowd loved that product.

      On Thu, Apr 12, 2012 at 10:36 AM, Andy Mills <amills@...> wrote:

      > **
      >
      >
      > Charles:
      >
      > I thank you for the link but have to ask if this product is still
      > supported?
      >
      > When I look at the system requirements:
      > http://www.speedferret.com/sysreqmts.html
      > The site makes no reference to Windows Vista / Windows 7 and only lists up
      > to SQL Server 2000, ignoring 2005 / 2008, with the same issues for VB (no
      > .Net) and ends at Access 2002.
      >
      > Thanks
      > Andy
      >


      [Non-text portions of this message have been removed]
    • Noman Aftab
      Try this: SELECT sysobjects.name, REPLACE(syscomments.text, CREATE PROC , ALTER PROC ) FROM sysobjects INNER JOIN syscomments ON sysobjects.id =
      Message 2 of 9 , Apr 15, 2012
      • 0 Attachment
        Try this:

        SELECT sysobjects.name, REPLACE(syscomments.text, 'CREATE PROC', 'ALTER PROC') FROM sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
        WHERE sysobjects.xtype = 'P'
        AND syscomments.text LIKE '%ShortDescription%'
        AND syscomments.text LIKE '%OrderDetails%'


        It will serve as a start step towards your desired solution.
        You can modify this query to cleverly use REPLACE function to replace varchar(200) with varchar(1000).
         
        Best Wishes,
        Noman Aftab

         
        http://corpus.quran.com/wordbyword.jsp



        ________________________________
        From: Andy Mills <amills@...>
        To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
        Sent: Monday, 9 April 2012 11:12 PM
        Subject: [SQLQueriesNoCode] Stored Procedure Question


         
        I am working on a bug for my current project and tracked down the issue to a column having the wrong size.

        We have dbo.Products.ShortDescription set to VARCHAR(1000) and this value is used to populate dbo.OrderDetails.ShortDescription when a user orders an item (please note: I am aware we are storing the same information in two locations, adding complexity to this issue, but it was done years ago and I have no control over this at this time). The column for dbo.OrderDetails.ShortDescription is set to VARCHAR(200) so in essence, the dbo.OrderDetails.ShortDescription field is only getting the first 200 characters.

        I know to fix this issue, we first need to match the column sizes and update the data.

        My question though is: is there a way to find inside stored procedures where "ShortDescription" or "OrderDetails" is used inside the stored procedure so I can update the variables inside from VARCHAR(200) to VARCHAR(1000) to prevent this from occurring in the future? I know we have an "InsertStoreDetails" procedure that needs updated, but the site has about 150 stored procedures and I didn't want to have to open every one.

        Is there some query I can run to find this information, or some way to avoid having to open each and every stored procedure?

        Thanks
        Andy

        [Non-text portions of this message have been removed]




        [Non-text portions of this message have been removed]
      • Andy Mills
        Thanks Norman From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab Sent: Sunday, April 15, 2012 3:12 PM
        Message 3 of 9 , Apr 16, 2012
        • 0 Attachment
          Thanks Norman

          From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
          Sent: Sunday, April 15, 2012 3:12 PM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: Re: [SQLQueriesNoCode] Stored Procedure Question



          Try this:

          SELECT sysobjects.name, REPLACE(syscomments.text, 'CREATE PROC', 'ALTER PROC') FROM sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
          WHERE sysobjects.xtype = 'P'
          AND syscomments.text LIKE '%ShortDescription%'
          AND syscomments.text LIKE '%OrderDetails%'

          It will serve as a start step towards your desired solution.
          You can modify this query to cleverly use REPLACE function to replace varchar(200) with varchar(1000).

          Best Wishes,
          Noman Aftab


          http://corpus.quran.com/wordbyword.jsp

          ________________________________
          From: Andy Mills <amills@...<mailto:amills%40jplcreative.com>>
          To: "SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>" <SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>>
          Sent: Monday, 9 April 2012 11:12 PM
          Subject: [SQLQueriesNoCode] Stored Procedure Question



          I am working on a bug for my current project and tracked down the issue to a column having the wrong size.

          We have dbo.Products.ShortDescription set to VARCHAR(1000) and this value is used to populate dbo.OrderDetails.ShortDescription when a user orders an item (please note: I am aware we are storing the same information in two locations, adding complexity to this issue, but it was done years ago and I have no control over this at this time). The column for dbo.OrderDetails.ShortDescription is set to VARCHAR(200) so in essence, the dbo.OrderDetails.ShortDescription field is only getting the first 200 characters.

          I know to fix this issue, we first need to match the column sizes and update the data.

          My question though is: is there a way to find inside stored procedures where "ShortDescription" or "OrderDetails" is used inside the stored procedure so I can update the variables inside from VARCHAR(200) to VARCHAR(1000) to prevent this from occurring in the future? I know we have an "InsertStoreDetails" procedure that needs updated, but the site has about 150 stored procedures and I didn't want to have to open every one.

          Is there some query I can run to find this information, or some way to avoid having to open each and every stored procedure?

          Thanks
          Andy

          [Non-text portions of this message have been removed]

          [Non-text portions of this message have been removed]



          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.