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

Stored Procedure Question

Expand Messages
  • Andy Mills
    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
    Message 1 of 9 , Apr 9, 2012
    • 0 Attachment
      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]
    • John Warner
      Andy, I m sure someone can post better but: http://blog.sqlauthority.com/2007/05/25/sql-server-stored-procedure-to-dis
      Message 2 of 9 , Apr 10, 2012
      • 0 Attachment
        Andy, I'm sure someone can post better but:
        http://blog.sqlauthority.com/2007/05/25/sql-server-stored-procedure-to-dis
        play-code-text-of-stored-procedure-trigger-view-or-object/

        Then code if you have access to a procedural tool like C# or VB and pass
        through each one making the changes. Or worse case paste into a text
        editor and find/replace since this is a onetime deal.

        John Warner


        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Andy Mills
        > Sent: Monday, April 09, 2012 3:13 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > 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]
        >
        >
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        >
      • Andy Mills
        Thanks John From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner Sent: Tuesday, April 10, 2012 5:02 AM To:
        Message 3 of 9 , Apr 10, 2012
        • 0 Attachment
          Thanks John

          From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          Sent: Tuesday, April 10, 2012 5:02 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: RE: [SQLQueriesNoCode] Stored Procedure Question



          Andy, I'm sure someone can post better but:
          http://blog.sqlauthority.com/2007/05/25/sql-server-stored-procedure-to-dis
          play-code-text-of-stored-procedure-trigger-view-or-object/

          Then code if you have access to a procedural tool like C# or VB and pass
          through each one making the changes. Or worse case paste into a text
          editor and find/replace since this is a onetime deal.

          John Warner

          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>
          > [mailto:SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>] On Behalf Of Andy Mills
          > Sent: Monday, April 09, 2012 3:13 PM
          > To: SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>
          > 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]
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >



          [Non-text portions of this message have been removed]
        • Damhuis Anton
          My simple solutions would be the script the whole database, and then run through the script file with the editor, and find all references to the column. But I
          Message 4 of 9 , Apr 12, 2012
          • 0 Attachment
            My simple solutions would be the script the whole database, and then run through the script file with the editor, and find all references to the column.

            But I am sure
            Regards
            Anton


            ________________________________
            Please note: This email and its contents are subject to the disclaimer as displayed at the following link: https://sa.investmentsolutions.co.za/Legal_Notices/EmailLegalNotice . Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


            [Non-text portions of this message have been removed]
          • Charles Carroll
            http://www.speedferret.com/speedferret.html rocks for all databases. ... [Non-text portions of this message have been removed]
            Message 5 of 9 , Apr 12, 2012
            • 0 Attachment
              http://www.speedferret.com/speedferret.html
              rocks for all databases.

              On Mon, Apr 9, 2012 at 3:12 PM, Andy Mills <amills@...> wrote:

              > **
              >
              >
              > 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?
              >


              [Non-text portions of this message have been removed]
            • Andy Mills
              Charles: I thank you for the link but have to ask if this product is still supported? When I look at the system requirements:
              Message 6 of 9 , Apr 12, 2012
              • 0 Attachment
                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

                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                Sent: Thursday, April 12, 2012 10:17 AM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: Re: [SQLQueriesNoCode] Stored Procedure Question



                http://www.speedferret.com/speedferret.html
                rocks for all databases.




                [Non-text portions of this message have been removed]
              • 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 7 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 8 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 9 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.