ODBC-- call failed.

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Kirby1
    New Member
    • Sep 2007
    • 7
    #1

    ODBC-- call failed.

    ODBC call failed
    --------------------------------------------------------------------------------

    We have recently upgraded our SQL Server from 7.0 to 2000. Since then my Access database to generate reports on the data is giving this error when I try to display a report:

    ODBC call failed.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression. (#512) [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'SG_412007 named 'ixTable'.(#191 3)
    Tags: None
    • davef
      New Member
      • Sep 2007
      • 98
      #2
      Originally posted by Kirby1
      ODBC call failed
      --------------------------------------------------------------------------------

      We have recently upgraded our SQL Server from 7.0 to 2000. Since then my Access database to generate reports on the data is giving this error when I try to display a report:

      ODBC call failed.
      [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression. (#512) [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'SG_412007 named 'ixTable'.(#191 3)
      Can you post your query? It looks like the problem is in the subquery definition. Maybe, you need to use EXISTS instead of IN or something to that effect. Anyway, post it and we'll see.

        Comment

        • Kirby1
          New Member
          • Sep 2007
          • 7
          #3
          Originally posted by davef
          Can you post your query? It looks like the problem is in the subquery definition. Maybe, you need to use EXISTS instead of IN or something to that effect. Anyway, post it and we'll see.
          Private Sub CmdCAAPreview_C lick()
          On Error GoTo Err_CmdCAAPrevi ew_Click
          ' Preview report.
          Dim strDocName As String

          'Check to see that there is a value selected in the combo box of Fiscal year for Utilities

          If IsNull(Me.CboCA ARec.Value) Then
          MsgBox "You must select a Fiscal Year prior to view a report on CAA."
          Me.CboCAARec.Se tFocus
          Else
          Me.TxtHoldingTy peOfRpt = "CAA"
          Me.TxtHoldingNa meOfRpt = "CAA"
          strDocName = "RCrosstabUTLRp t"
          Me.Visible = False
          DoCmd.OpenRepor t strDocName, acViewPreview
          End If

          Exit_CmdCAAPrev iew_Click:
          Exit Sub

          Err_CmdCAAPrevi ew_Click:
          If Err = ConErrRptCancel ed Then
          Resume Exit_CmdCAAPrev iew_Click
          Else
          MsgBox Err.Description
          Resume Exit_CmdCAAPrev iew_Click
          End If

          End Sub

            Comment

            • davef
              New Member
              • Sep 2007
              • 98
              #4
              Originally posted by Kirby1
              Private Sub CmdCAAPreview_C lick()
              On Error GoTo Err_CmdCAAPrevi ew_Click
              ' Preview report.
              Dim strDocName As String

              'Check to see that there is a value selected in the combo box of Fiscal year for Utilities

              If IsNull(Me.CboCA ARec.Value) Then
              MsgBox "You must select a Fiscal Year prior to view a report on CAA."
              Me.CboCAARec.Se tFocus
              Else
              Me.TxtHoldingTy peOfRpt = "CAA"
              Me.TxtHoldingNa meOfRpt = "CAA"
              strDocName = "RCrosstabUTLRp t"
              Me.Visible = False
              DoCmd.OpenRepor t strDocName, acViewPreview
              End If

              Exit_CmdCAAPrev iew_Click:
              Exit Sub

              Err_CmdCAAPrevi ew_Click:
              If Err = ConErrRptCancel ed Then
              Resume Exit_CmdCAAPrev iew_Click
              Else
              MsgBox Err.Description
              Resume Exit_CmdCAAPrev iew_Click
              End If

              End Sub
              I still don't see your query. If it's not available in the code, get it from the SQL Server sp or function. Alternatively, run the SQL Server Profiler tool while executing the program to capture the actual parameters of the query.

                Comment

                • Kirby1
                  New Member
                  • Sep 2007
                  • 7
                  #5
                  Originally posted by davef
                  I still don't see your query. If it's not available in the code, get it from the SQL Server sp or function. Alternatively, run the SQL Server Profiler tool while executing the program to capture the actual parameters of the query.

                  CREATE PROCEDURE dbo.pQryUtility ByFiscalYear
                  (@dStartDate varchar(128) , @dEndDate varchar(128), @sEFTType varchar(3))
                  AS
                  SET NOCOUNT ON
                  DECLARE @table_name sysname
                  /* Assign tablename_param value1 [_paramvalue2] ... */
                  SET @table_name = 'SG'
                  DECLARE @param0 varchar(128)
                  SET @param0 = @dStartDate
                  SET @param0 = REPLACE(@param0 , ' ', '')
                  SET @param0 = REPLACE(@param0 , '/', '')
                  SET @param0 = REPLACE(@param0 , '-', '')
                  SET @table_name = @table_name + '_' + @param0
                  DECLARE @param1 varchar(128)
                  SET @param1 = @dEndDate
                  SET @param1 = REPLACE(@param1 , ' ', '')
                  SET @param1 = REPLACE(@param1 , '/', '')
                  SET @param1 = REPLACE(@param1 , '-', '')

                  /* SET @table_name = @table_name + '_' + @param1 */
                  /* Check the age of the table for regeneration */

                  IF EXISTS(SELECT table_name FROM information_sch ema.tables
                  WHERE table_name = @table_name)
                  BEGIN
                  DECLARE @tabletime datetime
                  SET @tabletime = (SELECT crdate FROM sysobjects
                  WHERE name = @table_name)
                  /* After testing the stored procedure, remove the two comment pairs below and replace 12 with the number of hours between successive */
                  /* IF DATEDIFF(hh, @tabletime, GETDATE()) < 12 GOTO shortcut */
                  END
                  /* Drop the Existing table */
                  IF EXISTS(SELECT table_name FROM
                  information_sch ema.tables
                  WHERE table_name = @table_name)
                  BEGIN
                  EXEC ('DROP TABLE wshUser1.' + @table_name)
                  END
                  /* Create a temporary table from the function */
                  IF EXISTS(SELECT table_name FROM
                  information_sch ema.tables
                  WHERE table_name = 'tbTempCT')
                  BEGIN
                  EXEC ('DROP TABLE wshUser1.tbTemp CT')
                  END
                  CREATE TABLE wshUser1.tbTemp CT (AccountNumber varchar(11), Lastname varchar(20), Init varchar(5),
                  MonthPeriod varchar(80), TransferredAmou nt money)
                  /* Insert the view/function resultset */
                  INSERT INTO wshUser1.tbTemp CT Select AccountNumber, Lastname, Init, MonthPeriod, TransferredAmou nt From
                  wshUser1.fnxtab UtilityByFiscal Year(@dStartDat e,@dEndDate,@sE FTType)

                  /* Generate a new crosstab table */

                  EXEC ('CREATE TABLE wshUser1.' + @table_name + ' (AccountNumber varchar(11),
                  Lastname varchar(20), Init varchar(5), [APR] money DEFAULT 0.0,
                  [MAY] money DEFAULT 0.0, [JUN] money DEFAULT 0.0,
                  [JUL] money DEFAULT 0.0, [AUG] money DEFAULT 0.0,
                  [SEP] money DEFAULT 0.0, [OCT] money DEFAULT 0.0,
                  [NOV] money DEFAULT 0.0, [DEC] money DEFAULT 0.0,
                  [JAN] money DEFAULT 0.0, [FEB] money DEFAULT 0.0,
                  [MAR] money DEFAULT 0.0, [TotalAmount] money DEFAULT 0.0)')

                  /* Index is a bit out of order */
                  CREATE INDEX ixTempCT ON wshUser1.tbTemp CT (AccountNumber, Lastname,Init)

                  /* Add the full set of row headings */
                  EXEC ('INSERT INTO ' + @table_name + ' (AccountNumber, Lastname, Init)
                  SELECT DISTINCT AccountNumber, Lastname, Init FROM tbTempCT
                  ORDER BY LastName')
                  EXEC ('CREATE CLUSTERED INDEX ixTable ON ' + @table_name + ' (Lastname)')
                  /* Add the column header values */
                  EXEC ('UPDATE ' + @table_name + ' SET [APR] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''APR'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [MAY] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''MAY'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [JUN] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''JUN'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [JUL] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''JUL'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [AUG] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''AUG'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [SEP] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''SEP'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [OCT] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''OCT'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [NOV] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''NOV'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [DEC] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''DEC'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [JAN] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''JAN'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [FEB] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''FEB'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  EXEC ('UPDATE ' + @table_name + ' SET [MAR] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''MAR'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                  /* ADD ROW TOTALS */

                  EXEC ('UPDATE ' + @table_name + ' SET TotalAmount = ISNULL([APR],0) + ISNULL([MAY],0) + ISNULL([JUN],0) + ISNULL([JUL],0) + ISNULL([AUG],0) + ISNULL([SEP],0) +
                  ISNULL([OCT],0) + ISNULL([NOV],0) + ISNULL([DEC],0) + ISNULL([JAN],0) + ISNULL([FEB],0) + ISNULL([MAR],0)')

                  /* ADD ROW OF COLUMN TOTALS */

                  EXEC ('INSERT ' + @table_name + ' (Lastname) VALUES (''zzzzTotals'' )')

                  /* EXEC Statements here for the next 12 month */
                  EXEC ('UPDATE ' + @table_name + ' SET AccountNumber = "" WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET Init = "" WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [APR] = (SELECT SUM(APR) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [MAY] = (SELECT SUM(MAY) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [JUN] = (SELECT SUM(JUN) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [JUL] = (SELECT SUM(JUL) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [AUG] = (SELECT SUM(AUG) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [SEP] = (SELECT SUM(SEP) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [OCT] = (SELECT SUM(OCT) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [NOV] = (SELECT SUM(NOV) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [DEC] = (SELECT SUM(DEC) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [JAN] = (SELECT SUM(JAN) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [FEB] = (SELECT SUM(FEB) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  EXEC ('UPDATE ' + @table_name + ' SET [MAR] = (SELECT SUM(MAR) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                  /* Add crossfoot (grand) Total value */
                  EXEC ('UPDATE ' + @table_name + ' SET TotalAmount = (Select SUM(TotalAmount )
                  From ' + @table_name + ')
                  WHERE Lastname = ''zzzzTotals''' )
                  /* Drop the Temporary table */
                  IF EXISTS(SELECT table_name FROM information_sch ema.tables
                  WHERE table_name = 'tbTempCT')
                  DROP TABLE wshUser1.tbTemp CT

                  shortcut:
                  /* Return the table data with a fix-up for the totals row */

                  EXEC ('SELECT AccountNumber, REPLACE(LastNam e, ''zzzzTotals'', ''TotalAmount'' ) As Lastname, Init, [APR], [MAY], [JUN],
                  [JUL], [AUG], [SEP],[OCT], [NOV], [DEC],[JAN], [FEB], [MAR], TotalAmount
                  FROM ' + @Table_name)
                  GO

                    Comment

                    • davef
                      New Member
                      • Sep 2007
                      • 98
                      #6
                      Originally posted by Kirby1
                      CREATE PROCEDURE dbo.pQryUtility ByFiscalYear
                      (@dStartDate varchar(128) , @dEndDate varchar(128), @sEFTType varchar(3))
                      AS
                      SET NOCOUNT ON
                      DECLARE @table_name sysname
                      /* Assign tablename_param value1 [_paramvalue2] ... */
                      SET @table_name = 'SG'
                      DECLARE @param0 varchar(128)
                      SET @param0 = @dStartDate
                      SET @param0 = REPLACE(@param0 , ' ', '')
                      SET @param0 = REPLACE(@param0 , '/', '')
                      SET @param0 = REPLACE(@param0 , '-', '')
                      SET @table_name = @table_name + '_' + @param0
                      DECLARE @param1 varchar(128)
                      SET @param1 = @dEndDate
                      SET @param1 = REPLACE(@param1 , ' ', '')
                      SET @param1 = REPLACE(@param1 , '/', '')
                      SET @param1 = REPLACE(@param1 , '-', '')

                      /* SET @table_name = @table_name + '_' + @param1 */
                      /* Check the age of the table for regeneration */

                      IF EXISTS(SELECT table_name FROM information_sch ema.tables
                      WHERE table_name = @table_name)
                      BEGIN
                      DECLARE @tabletime datetime
                      SET @tabletime = (SELECT crdate FROM sysobjects
                      WHERE name = @table_name)
                      /* After testing the stored procedure, remove the two comment pairs below and replace 12 with the number of hours between successive */
                      /* IF DATEDIFF(hh, @tabletime, GETDATE()) < 12 GOTO shortcut */
                      END
                      /* Drop the Existing table */
                      IF EXISTS(SELECT table_name FROM
                      information_sch ema.tables
                      WHERE table_name = @table_name)
                      BEGIN
                      EXEC ('DROP TABLE wshUser1.' + @table_name)
                      END
                      /* Create a temporary table from the function */
                      IF EXISTS(SELECT table_name FROM
                      information_sch ema.tables
                      WHERE table_name = 'tbTempCT')
                      BEGIN
                      EXEC ('DROP TABLE wshUser1.tbTemp CT')
                      END
                      CREATE TABLE wshUser1.tbTemp CT (AccountNumber varchar(11), Lastname varchar(20), Init varchar(5),
                      MonthPeriod varchar(80), TransferredAmou nt money)
                      /* Insert the view/function resultset */
                      INSERT INTO wshUser1.tbTemp CT Select AccountNumber, Lastname, Init, MonthPeriod, TransferredAmou nt From
                      wshUser1.fnxtab UtilityByFiscal Year(@dStartDat e,@dEndDate,@sE FTType)

                      /* Generate a new crosstab table */

                      EXEC ('CREATE TABLE wshUser1.' + @table_name + ' (AccountNumber varchar(11),
                      Lastname varchar(20), Init varchar(5), [APR] money DEFAULT 0.0,
                      [MAY] money DEFAULT 0.0, [JUN] money DEFAULT 0.0,
                      [JUL] money DEFAULT 0.0, [AUG] money DEFAULT 0.0,
                      [SEP] money DEFAULT 0.0, [OCT] money DEFAULT 0.0,
                      [NOV] money DEFAULT 0.0, [DEC] money DEFAULT 0.0,
                      [JAN] money DEFAULT 0.0, [FEB] money DEFAULT 0.0,
                      [MAR] money DEFAULT 0.0, [TotalAmount] money DEFAULT 0.0)')

                      /* Index is a bit out of order */
                      CREATE INDEX ixTempCT ON wshUser1.tbTemp CT (AccountNumber, Lastname,Init)

                      /* Add the full set of row headings */
                      EXEC ('INSERT INTO ' + @table_name + ' (AccountNumber, Lastname, Init)
                      SELECT DISTINCT AccountNumber, Lastname, Init FROM tbTempCT
                      ORDER BY LastName')
                      EXEC ('CREATE CLUSTERED INDEX ixTable ON ' + @table_name + ' (Lastname)')
                      /* Add the column header values */
                      EXEC ('UPDATE ' + @table_name + ' SET [APR] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''APR'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [MAY] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''MAY'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [JUN] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''JUN'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [JUL] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''JUL'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [AUG] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''AUG'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [SEP] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''SEP'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [OCT] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''OCT'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [NOV] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''NOV'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [DEC] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''DEC'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [JAN] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''JAN'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [FEB] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''FEB'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      EXEC ('UPDATE ' + @table_name + ' SET [MAR] = (SELECT SUM(Transferred Amount) FROM tbTempCT WHERE MonthPeriod = ''MAR'' AND tbTempcT.Accoun tNumber = ' + @table_name + ' .AccountNumber) ')
                      /* ADD ROW TOTALS */

                      EXEC ('UPDATE ' + @table_name + ' SET TotalAmount = ISNULL([APR],0) + ISNULL([MAY],0) + ISNULL([JUN],0) + ISNULL([JUL],0) + ISNULL([AUG],0) + ISNULL([SEP],0) +
                      ISNULL([OCT],0) + ISNULL([NOV],0) + ISNULL([DEC],0) + ISNULL([JAN],0) + ISNULL([FEB],0) + ISNULL([MAR],0)')

                      /* ADD ROW OF COLUMN TOTALS */

                      EXEC ('INSERT ' + @table_name + ' (Lastname) VALUES (''zzzzTotals'' )')

                      /* EXEC Statements here for the next 12 month */
                      EXEC ('UPDATE ' + @table_name + ' SET AccountNumber = "" WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET Init = "" WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [APR] = (SELECT SUM(APR) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [MAY] = (SELECT SUM(MAY) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [JUN] = (SELECT SUM(JUN) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [JUL] = (SELECT SUM(JUL) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [AUG] = (SELECT SUM(AUG) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [SEP] = (SELECT SUM(SEP) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [OCT] = (SELECT SUM(OCT) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [NOV] = (SELECT SUM(NOV) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [DEC] = (SELECT SUM(DEC) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [JAN] = (SELECT SUM(JAN) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [FEB] = (SELECT SUM(FEB) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      EXEC ('UPDATE ' + @table_name + ' SET [MAR] = (SELECT SUM(MAR) FROM ' + @table_name + ') WHERE Lastname = ''zzzzTotals''' )
                      /* Add crossfoot (grand) Total value */
                      EXEC ('UPDATE ' + @table_name + ' SET TotalAmount = (Select SUM(TotalAmount )
                      From ' + @table_name + ')
                      WHERE Lastname = ''zzzzTotals''' )
                      /* Drop the Temporary table */
                      IF EXISTS(SELECT table_name FROM information_sch ema.tables
                      WHERE table_name = 'tbTempCT')
                      DROP TABLE wshUser1.tbTemp CT

                      shortcut:
                      /* Return the table data with a fix-up for the totals row */

                      EXEC ('SELECT AccountNumber, REPLACE(LastNam e, ''zzzzTotals'', ''TotalAmount'' ) As Lastname, Init, [APR], [MAY], [JUN],
                      [JUL], [AUG], [SEP],[OCT], [NOV], [DEC],[JAN], [FEB], [MAR], TotalAmount
                      FROM ' + @Table_name)
                      GO
                      Ok, there're a few spots in the query that can yield multiple rows in single variable assignment statements. Let's run this one first:
                      Code:
                        
SET @tabletime = (SELECT crdate FROM sysobjects
 WHERE name = @table_name)
                      See if the subquery returns more than one record.

                        Comment

                        • Kirby1
                          New Member
                          • Sep 2007
                          • 7
                          #7
                          It was working just fine before the system admin moved from the sql 7.0 to sql 2000??

                            Comment

                            • davef
                              New Member
                              • Sep 2007
                              • 98
                              #8
                              Originally posted by Kirby1
                              It was working just fine before the system admin moved from the sql 7.0 to sql 2000??
                              Well, since nobody has claimed yet that 7.0 and 2000 are fully compatible, I'm afraid you'd have to do some code reviewing and debugging.

                                Comment

                                • Kirby1
                                  New Member
                                  • Sep 2007
                                  • 7
                                  #9
                                  Since I am a very beginner in all this can you be a bit more specific. I am not sure what you mean by the following?

                                  Ok, there're a few spots in the query that can yield multiple rows in single variable assignment statements. Let's run this one first:

                                  Code: ( text )
                                  SET @tabletime = (SELECT crdate FROM sysobjects
                                  WHERE name = @table_name)

                                  See if the subquery returns more than one record.

                                    Comment

                                    • davef
                                      New Member
                                      • Sep 2007
                                      • 98
                                      #10
                                      Originally posted by Kirby1
                                      Since I am a very beginner in all this can you be a bit more specific. I am not sure what you mean by the following?

                                      Ok, there're a few spots in the query that can yield multiple rows in single variable assignment statements. Let's run this one first:

                                      Code: ( text )
                                      SET @tabletime = (SELECT crdate FROM sysobjects
                                      WHERE name = @table_name)

                                      See if the subquery returns more than one record.
                                      If a subquery actually returns more than one record while assigning the result to a variable (@tabletime in this case), the sp will fail with an erro similar to what you've received. That being said, I don't claim that that particular statement causes the problem. For starters, you'll need to sift through the code to watch for suspicious statements like that. This is how I would tackle it.
                                      Edit: Well, this particular statement can't be blamed I've just realized as there should be only one crdate value for each object in the sysobjects table.
                                      Last edited by davef; Sep 24 '07, 08:01 PM. Reason: Correction

                                        Comment

                                        • Kirby1
                                          New Member
                                          • Sep 2007
                                          • 7
                                          #11
                                          So, if I am following correctly, from your edit this is not the problem??

                                            Comment

                                            • davef
                                              New Member
                                              • Sep 2007
                                              • 98
                                              #12
                                              Originally posted by Kirby1
                                              So, if I am following correctly, from your edit this is not the problem??
                                              Honestly, I don't see any more statements in the sp that could throw that error... I suggest that you debug it in the SQL Server Analyzer to iron out the faulty statement(s). Let me know if you need further guidelines for debugging.

                                                Comment

                                                Previous template Next
                                                Working...