By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

ODBC-- call failed.

P: 7
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'.(#1913)
Sep 24 '07 #1
Share this Question
Share on Google+
11 Replies


P: 98
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'.(#1913)
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.
Sep 24 '07 #2

P: 7
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_Click()
On Error GoTo Err_CmdCAAPreview_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.CboCAARec.Value) Then
MsgBox "You must select a Fiscal Year prior to view a report on CAA."
Me.CboCAARec.SetFocus
Else
Me.TxtHoldingTypeOfRpt = "CAA"
Me.TxtHoldingNameOfRpt = "CAA"
strDocName = "RCrosstabUTLRpt"
Me.Visible = False
DoCmd.OpenReport strDocName, acViewPreview
End If

Exit_CmdCAAPreview_Click:
Exit Sub

Err_CmdCAAPreview_Click:
If Err = ConErrRptCanceled Then
Resume Exit_CmdCAAPreview_Click
Else
MsgBox Err.Description
Resume Exit_CmdCAAPreview_Click
End If

End Sub
Sep 24 '07 #3

P: 98
Private Sub CmdCAAPreview_Click()
On Error GoTo Err_CmdCAAPreview_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.CboCAARec.Value) Then
MsgBox "You must select a Fiscal Year prior to view a report on CAA."
Me.CboCAARec.SetFocus
Else
Me.TxtHoldingTypeOfRpt = "CAA"
Me.TxtHoldingNameOfRpt = "CAA"
strDocName = "RCrosstabUTLRpt"
Me.Visible = False
DoCmd.OpenReport strDocName, acViewPreview
End If

Exit_CmdCAAPreview_Click:
Exit Sub

Err_CmdCAAPreview_Click:
If Err = ConErrRptCanceled Then
Resume Exit_CmdCAAPreview_Click
Else
MsgBox Err.Description
Resume Exit_CmdCAAPreview_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.
Sep 24 '07 #4

P: 7
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.pQryUtilityByFiscalYear
(@dStartDate varchar(128) , @dEndDate varchar(128), @sEFTType varchar(3))
AS
SET NOCOUNT ON
DECLARE @table_name sysname
/* Assign tablename_paramvalue1 [_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_schema.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_schema.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_schema.tables
WHERE table_name = 'tbTempCT')
BEGIN
EXEC ('DROP TABLE wshUser1.tbTempCT')
END
CREATE TABLE wshUser1.tbTempCT (AccountNumber varchar(11), Lastname varchar(20), Init varchar(5),
MonthPeriod varchar(80), TransferredAmount money)
/* Insert the view/function resultset */
INSERT INTO wshUser1.tbTempCT Select AccountNumber, Lastname, Init, MonthPeriod, TransferredAmount From
wshUser1.fnxtabUtilityByFiscalYear(@dStartDate,@dE ndDate,@sEFTType)

/* 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.tbTempCT (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(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''APR'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [MAY] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''MAY'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [JUN] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''JUN'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [JUL] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''JUL'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [AUG] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''AUG'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [SEP] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''SEP'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [OCT] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''OCT'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [NOV] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''NOV'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [DEC] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''DEC'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [JAN] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''JAN'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [FEB] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''FEB'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [MAR] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''MAR'' AND tbTempcT.AccountNumber = ' + @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_schema.tables
WHERE table_name = 'tbTempCT')
DROP TABLE wshUser1.tbTempCT

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

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

P: 98
CREATE PROCEDURE dbo.pQryUtilityByFiscalYear
(@dStartDate varchar(128) , @dEndDate varchar(128), @sEFTType varchar(3))
AS
SET NOCOUNT ON
DECLARE @table_name sysname
/* Assign tablename_paramvalue1 [_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_schema.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_schema.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_schema.tables
WHERE table_name = 'tbTempCT')
BEGIN
EXEC ('DROP TABLE wshUser1.tbTempCT')
END
CREATE TABLE wshUser1.tbTempCT (AccountNumber varchar(11), Lastname varchar(20), Init varchar(5),
MonthPeriod varchar(80), TransferredAmount money)
/* Insert the view/function resultset */
INSERT INTO wshUser1.tbTempCT Select AccountNumber, Lastname, Init, MonthPeriod, TransferredAmount From
wshUser1.fnxtabUtilityByFiscalYear(@dStartDate,@dE ndDate,@sEFTType)

/* 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.tbTempCT (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(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''APR'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [MAY] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''MAY'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [JUN] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''JUN'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [JUL] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''JUL'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [AUG] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''AUG'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [SEP] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''SEP'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [OCT] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''OCT'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [NOV] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''NOV'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [DEC] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''DEC'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [JAN] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''JAN'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [FEB] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''FEB'' AND tbTempcT.AccountNumber = ' + @table_name + ' .AccountNumber)')
EXEC ('UPDATE ' + @table_name + ' SET [MAR] = (SELECT SUM(TransferredAmount) FROM tbTempCT WHERE MonthPeriod = ''MAR'' AND tbTempcT.AccountNumber = ' + @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_schema.tables
WHERE table_name = 'tbTempCT')
DROP TABLE wshUser1.tbTempCT

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

EXEC ('SELECT AccountNumber, REPLACE(LastName, ''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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SET @tabletime = (SELECT crdate FROM sysobjects
  3.  WHERE name = @table_name)
See if the subquery returns more than one record.
Sep 24 '07 #6

P: 7
It was working just fine before the system admin moved from the sql 7.0 to sql 2000??
Sep 24 '07 #7

P: 98
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.
Sep 24 '07 #8

P: 7
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.
Sep 24 '07 #9

P: 98
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.
Sep 24 '07 #10

P: 7
So, if I am following correctly, from your edit this is not the problem??
Sep 25 '07 #11

P: 98
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.
Sep 25 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.