ODBC-- call failed. - Microsoft SQL Server

7 New Member

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)

Sep 24 '07 #1

Subscribe Reply

3733

davef

New Member

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.

Sep 24 '07 #2

Kirby1

New Member

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

Sep 24 '07 #3

davef

New Member

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.

Sep 24 '07 #4

Kirby1

New Member

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

Sep 24 '07 #5

davef

New Member

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:

Expand|Select|Wrap|Line Numbers

  
SET @tabletime = (SELECT crdate FROM sysobjects

 WHERE name = @table_name)

See if the subquery returns more than one record.

Sep 24 '07 #6

Kirby1

New Member

It was working just fine before the system admin moved from the sql 7.0 to sql 2000??

Sep 24 '07 #7

davef

New Member

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

New Member

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

New Member

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

Similar topics

6170

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 function should work. I get an error at this point Set StoredProcRecordSet = DB.OpenRecordset(StoredProcQryName, DB_OPEN_SNAPSHOT) The error is this ODBC--call failed.

Microsoft Access / VBA

3495

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 matter the query, a connection is created in MySQL. Initialy, this connection stayed opened 8 hours (default. controled

Microsoft Access / VBA

4160

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 life of me: "Error 3146: ODBC Call Failed" Now, the stored proc uses @RETURN_VALUE to pass back an integer result

Microsoft Access / VBA

17031

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 access any of the Oracle tables on another machine but now I am having problems. Unfortunately, I don't remember the correct syntax for the ODBC connect string and I am hoping that is my whole problem. I am trying to connect to an Oracle 9...

Microsoft Access / VBA

7024

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 to make sure i click the the navigation arrows through all the records or else I get an odbc error and all the fields go to #Name? If I click the arrows and scroll through all the records Im ok.. What is causing this ?

Microsoft Access / VBA

1645

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 get the ODBC... call failed error. The data ite is a 3 character text field. I've tried to us the parameter AFTE trimming the table field. no luck Any helpful hints? :

Microsoft Access / VBA

2421

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 version we are running is MS Access 95, V7. I know it is very old, but the organization is afraid to update to a newer version for fear of losing data. So, I must work with what I have.

Microsoft Access / VBA

14477

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 happens on my computer only. I have just setup Windows XP SP2 with latest updates and MS Office Prof. 2000 and in ODBC I have same dirvers installed as other users who are able to access reports without any problem. Can anybody tell me what...

Microsoft Access / VBA

11658

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: ************************************************************************************ ODBC -call failed. ORA-01017: invalid username/password; logon denied (#1017) Driver's SQLSetConnectAttr failed IM006 0 Driver's SQLSetConnectAttr failed...

Microsoft Access / VBA

9482

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10062

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9901

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9878

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8733

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6551

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5322

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3827

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2694

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General