473,385 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > microsoft sql server > questions > odbc-- call failed.

Join Bytes to post your question to a community of 473,385 software developers and data experts.

ODBC-- call failed.

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
11 3692
davef
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
Kirby1
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
davef
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
Kirby1
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
davef
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
Kirby1
7
It was working just fine before the system admin moved from the sql 7.0 to sql 2000??
Sep 24 '07 #7
davef
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
Kirby1
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
davef
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
Kirby1
7
So, if I am following correctly, from your edit this is not the problem??
Sep 25 '07 #11
davef
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

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

Similar topics

7
ODBC--call failed.
by: tina | last post by:
Hello, Can you hale me to define my mistake please? I am trying to run a SQL Pass – Through Query from Access 2000 and inside the record set I am trying to loop for a LoginID. I think that this...
Microsoft Access / VBA
3
Access + MySQL = ODBC-call failed... sometime
by: Yannick Turgeon | last post by:
Hello all, I'm using: - A97 (front-end) - MySQL drivers 3.51 - MySQL 4.0.18 (back-end) I've got a MySQL table connected to an Access DB throw an ODBC link. When I connect to this table, no...
Microsoft Access / VBA
1
QueryDef with GUID parameters - ODBC Call Failed Errors
by: VBSponge | last post by:
Hi all. Need help here as I cant see whats wrong... I have a querydef in Access 2k which execs a stored proc in a SQL Server 2k db. I keep getting the following error, and cant stop it for the...
Microsoft Access / VBA
9
Oracle connection problems; Odbc call failed
by: mcbill20 | last post by:
Hello all. I just installed Oracle 10g developer tools on a machine running XP Pro and Office XP. Before this I had just the Oracle 9 client installed. I the previous configuration, I was able to...
Microsoft Access / VBA
8
ODBC Call Failed
by: Jimbo | last post by:
I have a form in access 97 that is populated by a query against some sql server tables....when ever the query pulls up only one record im fine..however if it pulls up more than one record i have...
Microsoft Access / VBA
1
ODBC... call failed using parameter, okay enteredas criteri
by: TheKval | last post by:
I'm trying to pull records on a Macola table linked to Access. If enter a value in the criteria field of the query form, the quer executes okay. If I change the criteria to a parameter, ala, , I...
Microsoft Access / VBA
1
ODBC--call failed
by: Harmony407 | last post by:
Hello: Our organization uses MS Access to store all of our members information. Our primary database computer is running on Windows 98. This is where the main database exists. The MS Access...
Microsoft Access / VBA
12
ODBC Call -- Failed
by: mukeshhtrivedi | last post by:
We have 4 MS Access Databases with few tables on each DB. There are some reports which uses ODBC. When I try to use the report and input date range and click ok I get error "ODBC --call failed" it...
Microsoft Access / VBA
4
ODBC -- call failed Error
by: alegria4ever | last post by:
I have an Access 2000 database that links several tables from Oracle 9. For some reason or another, one of our user repeatedly gets the following error when accessing queries within this database:...
Microsoft Access / VBA
0
Wordpress or something else?
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
Content Management Systems
0
isladogs
Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
General
0
One-click Importing Excel Data into a*Database
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
Microsoft Excel
0
How to turn on java script in a villaon keypad mobile phone
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
Java
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Batch import of multiple excel files into the database
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Data Management
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
How to build RAID in BIOS?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
Computer Hardware

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!