Limits to length of Stored Proc

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • C Kirby

    Limits to length of Stored Proc

    In SQL Server 2000, I've got a rather lengthy stored procedure, which
    creates a lot of temporary tables as it processes down through a few
    sets of data.
    When testing it through Query Analyzer, it runs fine (a bit slow
    though). But when I try to run it through the ade, it doesn't do
    anything. It runs through the procedure in milliseconds but doesn't
    seem to ever actually start it. If I change the calling code in the
    ade VBA to refer to a different SP, it will call/run the different SP,
    so I don't think its the way I call it.
    Is there a limit to the number of lines a stored procedure can have,
    or some other limit on memory or transactions?
  • Dan Guzman

    #2
    Re: Limits to length of Stored Proc

    I doubt the proc size is the issue. Have you included SET NOCOUNT ON at
    the beginning of the proc?

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):




    -----------------------

    "C Kirby" <ckirby@mindspr ing.com> wrote in message
    news:psdqlvklfn jtml6v4jr8ambl2 c3fns0lk0@4ax.c om...[color=blue]
    > In SQL Server 2000, I've got a rather lengthy stored procedure, which
    > creates a lot of temporary tables as it processes down through a few
    > sets of data.
    > When testing it through Query Analyzer, it runs fine (a bit slow
    > though). But when I try to run it through the ade, it doesn't do
    > anything. It runs through the procedure in milliseconds but doesn't
    > seem to ever actually start it. If I change the calling code in the
    > ade VBA to refer to a different SP, it will call/run the different SP,
    > so I don't think its the way I call it.
    > Is there a limit to the number of lines a stored pr[/color]

    Comment

    • John Bell

      #3
      Re: Limits to length of Stored Proc

      Hi

      You would not be able to compile the Stored procedure if you have hit a size
      limit, although it could be the query cost that is somehow behaving
      differently and therefore hitting that limit (see sp_configure/ query
      governor cost limit in Books online).

      If you can run this through QA it seems most likely that you are not passing
      the parameters incorrectly in your code, so try adding some debug
      statements! If the procedure is as long as you say, it is probably worth
      consider modularising it and splitting it into sub-procedures; you may also
      be able to re-write the code to be more efficient. This may also help stop
      recompilations.

      John


      "C Kirby" <ckirby@mindspr ing.com> wrote in message
      news:psdqlvklfn jtml6v4jr8ambl2 c3fns0lk0@4ax.c om...[color=blue]
      > In SQL Server 2000, I've got a rather lengthy stored procedure, which
      > creates a lot of temporary tables as it processes down through a few
      > sets of data.
      > When testing it through Query Analyzer, it runs fine (a bit slow
      > though). But when I try to run it through the ade, it doesn't do
      > anything. It runs through the procedure in milliseconds but doesn't
      > seem to ever actually start it. If I change the calling code in the
      > ade VBA to refer to a different SP, it will call/run the different SP,
      > so I don't think its the way I call it.
      > Is there a limit to the number of lines a stored procedure can have,
      > or some other limit on memory or transactions?[/color]


      Comment

      • C Kirby

        #4
        Re: Limits to length of Stored Proc

        Thanks for the help.. I do have NOCOUNT set to on, so I don't think
        that is the problem.

        I've tried to setup the debugging by using the IF @@ERROR <>0 ,
        Rollback and return X.
        I can't seem to get the front end to actually look at the return value
        to tell if the sp ran successfully though, so something isn't quite
        right with that.

        As far as the parameters go, this sp doesn't use any, so I'm not
        sending it any. Could this be why the front end isn't reading the
        return parameter?

        One question on splitting out the different functions into separate
        stored procs. The very first thing the sp does is to create a temp
        table holding the records to be manipulated. Right now it is named
        #temp (or something like that). In order to reference this table from
        another sp, should a use the double # ('##temp')?

        On Tue, 9 Sep 2003 09:02:09 +0100, "John Bell"
        <jbellnewsposts @hotmail.com> wrote:
        [color=blue]
        >Hi
        >
        >You would not be able to compile the Stored procedure if you have hit a size
        >limit, although it could be the query cost that is somehow behaving
        >differently and therefore hitting that limit (see sp_configure/ query
        >governor cost limit in Books online).
        >
        >If you can run this through QA it seems most likely that you are not passing
        >the parameters incorrectly in your code, so try adding some debug
        >statements! If the procedure is as long as you say, it is probably worth
        >consider modularising it and splitting it into sub-procedures; you may also
        >be able to re-write the code to be more efficient. This may also help stop
        >recompilations .
        >
        >John
        >
        >
        >"C Kirby" <ckirby@mindspr ing.com> wrote in message
        >news:psdqlvklf njtml6v4jr8ambl 2c3fns0lk0@4ax. com...[color=green]
        >> In SQL Server 2000, I've got a rather lengthy stored procedure, which
        >> creates a lot of temporary tables as it processes down through a few
        >> sets of data.
        >> When testing it through Query Analyzer, it runs fine (a bit slow
        >> though). But when I try to run it through the ade, it doesn't do
        >> anything. It runs through the procedure in milliseconds but doesn't
        >> seem to ever actually start it. If I change the calling code in the
        >> ade VBA to refer to a different SP, it will call/run the different SP,
        >> so I don't think its the way I call it.
        >> Is there a limit to the number of lines a stored procedure can have,
        >> or some other limit on memory or transactions?[/color]
        >[/color]

        Comment

        • Dan Guzman

          #5
          Re: Limits to length of Stored Proc

          > I've tried to setup the debugging by using the IF @@ERROR <>0 ,[color=blue]
          > Rollback and return X.
          > I can't seem to get the front end to actually look at the return value
          > to tell if the sp ran successfully though, so something isn't quite
          > right with that.
          >
          > As far as the parameters go, this sp doesn't use any, so I'm not
          > sending it any. Could this be why the front end isn't reading the
          > return parameter?[/color]

          The return value is essentially an output parameter. Does your
          procedure return resultsets? If so, you may need to consume those
          before the return value is available.
          [color=blue]
          > One question on splitting out the different functions into separate
          > stored procs. The very first thing the sp does is to create a temp
          > table holding the records to be manipulated. Right now it is named
          > #temp (or something like that). In order to reference this table from
          > another sp, should a use the double # ('##temp')?[/color]

          The local temp table (#temp) is visible to the nested procs so you don't
          need to resort to a global (##temp) table. An issue with global temp
          tables is that you'll need to uniquely name them to handle concurrency.

          --
          Hope this helps.

          Dan Guzman
          SQL Server MVP


          "C Kirby" <ckirby@mindspr ing.com> wrote in message
          news:4m7ulvo2b8 fbn7d230e30es5p l299c8to3@4ax.c om...[color=blue]
          > Thanks for the help.. I do have NOCOUNT set to on, so I don't think
          > that is the problem.
          >
          > I've tried to setup the debugging by using the IF @@ERROR <>0 ,
          > Rollback and return X.
          > I can't seem to get the front end to actually look at the return value
          > to tell if the sp ran successfully though, so something isn't quite
          > right with that.
          >
          > As far as the parameters go, this sp doesn't use any, so I'm not
          > sending it any. Could this be why the front end isn't reading the
          > return parameter?
          >
          > One question on splitting out the different functions into separate
          > stored procs. The very first thing the sp does is to create a temp
          > table holding the records to be manipulated. Right now it is named
          > #temp (or something like that). In order to reference this table from
          > another sp, should a use the double # ('##temp')?
          >
          > On Tue, 9 Sep 2003 09:02:09 +0100, "John Bell"
          > <jbellnewsposts @hotmail.com> wrote:
          >[color=green]
          > >Hi
          > >
          > >You would not be able to compile the Stored procedure if you have hit[/color][/color]
          a size[color=blue][color=green]
          > >limit, although it could be the query cost that is somehow behaving
          > >differently and therefore hitting that limit (see sp_configure/ query
          > >governor cost limit in Books online).
          > >
          > >If you can run this through QA it seems most likely that you are not[/color][/color]
          passing[color=blue][color=green]
          > >the parameters incorrectly in your code, so try adding some debug
          > >statements! If the procedure is as long as you say, it is probably[/color][/color]
          worth[color=blue][color=green]
          > >consider modularising it and splitting it into sub-procedures; you[/color][/color]
          may also[color=blue][color=green]
          > >be able to re-write the code to be more efficient. This may also help[/color][/color]
          stop[color=blue][color=green]
          > >recompilations .
          > >
          > >John
          > >
          > >
          > >"C Kirby" <ckirby@mindspr ing.com> wrote in message
          > >news:psdqlvklf njtml6v4jr8ambl 2c3fns0lk0@4ax. com...[color=darkred]
          > >> In SQL Server 2000, I've got a rather lengthy stored procedure,[/color][/color][/color]
          which[color=blue][color=green][color=darkred]
          > >> creates a lot of temporary tables as it processes down through a[/color][/color][/color]
          few[color=blue][color=green][color=darkred]
          > >> sets of data.
          > >> When testing it through Query Analyzer, it runs fine (a bit slow
          > >> though). But when I try to run it through the ade, it doesn't do
          > >> anything. It runs through the procedure in milliseconds but[/color][/color][/color]
          doesn't[color=blue][color=green][color=darkred]
          > >> seem to ever actually start it. If I change the calling code in[/color][/color][/color]
          the[color=blue][color=green][color=darkred]
          > >> ade VBA to refer to a different SP, it will call/run the different[/color][/color][/color]
          SP,[color=blue][color=green][color=darkred]
          > >> so I don't think its the way I call it.
          > >> Is there a limit to the number of lines a stored procedure can[/color][/color][/color]
          have,[color=blue][color=green][color=darkred]
          > >> or some other limit on memory or transactions?[/color]
          > >[/color]
          >[/color]


          Comment

          • Erland Sommarskog

            #6
            Re: Limits to length of Stored Proc

            Dan Guzman (danguzman@nosp am-earthlink.net) writes:[color=blue]
            > The return value is essentially an output parameter. Does your
            > procedure return resultsets? If so, you may need to consume those
            > before the return value is available.[/color]

            To add to what Dan says here, it depends on whether you are using
            client-side or server-side cursor. With client-side cursors you can
            access the return value directly.

            But we are a bit in the dark here, as we have not seen any of your
            code, neither the ADO code, nor the SQL code.

            --
            Erland Sommarskog, SQL Server MVP, sommar@algonet. se

            Books Online for SQL Server SP3 at
            Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

            Comment

            • C Kirby

              #7
              Re: Limits to length of Stored Proc

              You guys have *no* idea how much I appreciate the help!!

              Here's the code that I am using to call the sp from the Access ade

              'call stored procedure
              docmd.hourglass true
              Set com = New ADODB.Command
              With com
              .ActiveConnecti on = getadoconnectst ring("SM")
              .CommandText = "qryBSTransPost "
              .CommandType = adCmdStoredProc
              .CommandTimeout = 0
              .Execute , , adAsyncExecute

              If .Parameters(0) <> 0 Then
              'operation failed
              MsgBox "Fail"
              Else
              DoCmd.Hourglass False
              MsgBox "Transactio n Import Complete", vbOKOnly, "Brokerage
              Transactions Imported"
              End If

              End With
              Set com = Nothing

              This is all going into a pre-exisiting software package that has the
              getadoconnectio nstring function. Since the simple stored procs work
              with this call, I'm going to say that the connection is ok.

              Due to the length of the sp, I won't post it, but would be glad to let
              anyone see it (even though it is *ugly*!!). It does not return any
              records. It basically looks at a table, picks the records that meet a
              certain criteria, creates a temp table to hold the Primary keys to
              those records, then runs through a series of data manipulations using
              more temp tables, then adds those created records into a couple of
              different tables, then changes some values in the original records so
              that they no longer meet the initial criteria.

              This is the final section of the sp:
              IF @@ERROR <> 0
              BEGIN
              ROLLBACK TRANSACTION
              RETURN 11
              END

              COMMIT TRANSACTION
              GO


              Regardless of what actually happens with the sp when I call it from
              the ade, the .parameter(0) value never triggers the 'fail' option..


              On Wed, 10 Sep 2003 22:26:36 +0000 (UTC), Erland Sommarskog
              <sommar@algonet .se> wrote:
              [color=blue]
              >Dan Guzman (danguzman@nosp am-earthlink.net) writes:[color=green]
              >> The return value is essentially an output parameter. Does your
              >> procedure return resultsets? If so, you may need to consume those
              >> before the return value is available.[/color]
              >
              >To add to what Dan says here, it depends on whether you are using
              >client-side or server-side cursor. With client-side cursors you can
              >access the return value directly.
              >
              >But we are a bit in the dark here, as we have not seen any of your
              >code, neither the ADO code, nor the SQL code.[/color]

              Comment

              • Dan Guzman

                #8
                Re: Limits to length of Stored Proc

                Is there some reason you are using the adAsyncExecute option here? If
                not, you might try removing the option from your Execute method.

                It looks to me like your code isn't written to handle asynchronous proc
                execution. The code is checking the return value even though the proc
                may still be executing. The code probably works with your other procs
                simply because they complete before you check the result.

                Also, note @@ERROR is changed after every SQL statement so you need to
                check it after each statement and perform error processing then. For
                example:

                BEGIN TRAN
                INSERT INTO MyTable VALUES(1)
                IF @@ERROR <> 0 GOTO ErrorHandler
                INSERT INTO MyTable VALUES(2)
                IF @@ERROR <> 0 GOTO ErrorHandler
                COMMIT
                RETURN 0

                ErrorHandler:
                IF @@TRANCOUNT > 0 ROLLBACK
                RETURN 11


                --
                Hope this helps.

                Dan Guzman
                SQL Server MVP

                "C Kirby" <ckirby@mindspr ing.com> wrote in message
                news:d2bvlvgj5c 2hqv1mm6fqohdsh 641pmifp3@4ax.c om...[color=blue]
                > You guys have *no* idea how much I appreciate the help!!
                >
                > Here's the code that I am using to call the sp from the Access ade
                >
                > 'call stored procedure
                > docmd.hourglass true
                > Set com = New ADODB.Command
                > With com
                > .ActiveConnecti on = getadoconnectst ring("SM")
                > .CommandText = "qryBSTransPost "
                > .CommandType = adCmdStoredProc
                > .CommandTimeout = 0
                > .Execute , , adAsyncExecute
                >
                > If .Parameters(0) <> 0 Then
                > 'operation failed
                > MsgBox "Fail"
                > Else
                > DoCmd.Hourglass False
                > MsgBox "Transactio n Import Complete", vbOKOnly, "Brokerage
                > Transactions Imported"
                > End If
                >
                > End With
                > Set com = Nothing
                >
                > This is all going into a pre-exisiting software package that has the
                > getadoconnectio nstring function. Since the simple stored procs work
                > with this call, I'm going to say that the connection is ok.
                >
                > Due to the length of the sp, I won't post it, but would be glad to let
                > anyone see it (even though it is *ugly*!!). It does not return any
                > records. It basically looks at a table, picks the records that meet a
                > certain criteria, creates a temp table to hold the Primary keys to
                > those records, then runs through a series of data manipulations using
                > more temp tables, then adds those created records into a couple of
                > different tables, then changes some values in the original records so
                > that they no longer meet the initial criteria.
                >
                > This is the final section of the sp:
                > IF @@ERROR <> 0
                > BEGIN
                > ROLLBACK TRANSACTION
                > RETURN 11
                > END
                >
                > COMMIT TRANSACTION
                > GO
                >
                >
                > Regardless of what actually happens with the sp when I call it from
                > the ade, the .parameter(0) value never triggers the 'fail' option..
                >
                >
                > On Wed, 10 Sep 2003 22:26:36 +0000 (UTC), Erland Sommarskog
                > <sommar@algonet .se> wrote:
                >[color=green]
                > >Dan Guzman (danguzman@nosp am-earthlink.net) writes:[color=darkred]
                > >> The return value is essentially an output parameter. Does your
                > >> procedure return resultsets? If so, you may need to consume those
                > >> before the return value is available.[/color]
                > >
                > >To add to what Dan says here, it depends on whether you are using
                > >client-side or server-side cursor. With client-side cursors you can
                > >access the return value directly.
                > >
                > >But we are a bit in the dark here, as we have not seen any of your
                > >code, neither the ADO code, nor the SQL code.[/color]
                >[/color]


                Comment

                • C Kirby

                  #9
                  Re: Limits to length of Stored Proc

                  Looks like that was the problem, Dan! I removed the adAsync option
                  and the procedure is running!!!! Thanks for the help!!

                  I'm going to take a shot at splitting the the sp into a few sub
                  procedures and then see if I can get the return value to work..

                  Thanks for all the help from everybody!!!!!! !

                  On Thu, 11 Sep 2003 03:02:13 GMT, "Dan Guzman"
                  <danguzman@nosp am-earthlink.net> wrote:
                  [color=blue]
                  >Is there some reason you are using the adAsyncExecute option here? If
                  >not, you might try removing the option from your Execute method.
                  >
                  >It looks to me like your code isn't written to handle asynchronous proc
                  >execution. The code is checking the return value even though the proc
                  >may still be executing. The code probably works with your other procs
                  >simply because they complete before you check the result.
                  >
                  >Also, note @@ERROR is changed after every SQL statement so you need to
                  >check it after each statement and perform error processing then. For
                  >example:
                  >
                  >BEGIN TRAN
                  >INSERT INTO MyTable VALUES(1)
                  >IF @@ERROR <> 0 GOTO ErrorHandler
                  >INSERT INTO MyTable VALUES(2)
                  >IF @@ERROR <> 0 GOTO ErrorHandler
                  >COMMIT
                  >RETURN 0
                  >
                  >ErrorHandler :
                  >IF @@TRANCOUNT > 0 ROLLBACK
                  >RETURN 11[/color]

                  Comment

                  Working...