Problem executing a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem executing a stored procedure

Author

Topic

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 06:42:01

Hi,

I am getting the following error message while executing a stored procedure through an application :

[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
Any idea about the resolution for this ?

Regards
Ramesh

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-06-22 : 06:50:22

Hi!

Does it run fine when executed with Query Analyzer?

It would help if you post the code of your procedure and what the call looks like.

/Andraax

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 07:11:57

Here is the Stored Procedure :

CREATE PROCEDURE dbo.xxx_Z_JDE_PROJECTS

@Project_No varchar(12), -- Niku Project Number
@Status varchar(6), -- Niku Project Status
@Create_Date datetime, -- Niku Project Create date
@Closed_Date datetime, -- Niku Project Closed date

@intMode int=0 OUTPUT, -- Mode 0=Insert/Update, 1=Forced ADD, 2=Forced UPDATE
@intRetVal int=NULL OUTPUT, -- Return value, 0=No error
@strErrMsg varchar(255)=NULL OUTPUT -- Error messages

/* WITH ENCRYPTION */
AS
/*
*
*
* Returns:
*
* Result code (0=No error)
*
* ERRORS:
*
* -1 RowCount <> 1
* -2 INSERT failed, record exists
* -3 UPDATE failed, record does not exist
* -4 Error INSERTING
* -5 Error UPDATING
*
* HISTORY:
*
* 18-Jun-2004 KBM Started
*/

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @intErrNo int,
@intRowCount int

SELECT @intErrNo = 0, -- Assume no error
@strErrMsg = '',
@intRetVal = 0 -- Return value (Assume no error)

IF EXISTS (SELECT * FROM dbo.Z_JDE_PROJECTS WHERE Project_No = @Project_No)
BEGIN
-- Record exists
IF @intMode = 1 -- Only INSERT permitted
BEGIN
SELECT @intMode = -2, -- Show failure
@intRetVal = -2,
@strErrMsg = @strErrMsg + 'INSERT failed, record exists. '
END
ELSE
BEGIN
SELECT @intMode = 2 -- Use UPDATE
PRINT ('INSIDE SP: SET THE MODE TO UPDATE')
END
END
ELSE
BEGIN
-- Record does not exists
IF @intMode = 2 -- Only INSERT permitted
BEGIN
SELECT @intMode = -3, -- Show failure
@intRetVal = -3,
@strErrMsg = @strErrMsg + 'UPDATE failed, record does not exist. '
END
ELSE
BEGIN
SELECT @intMode = 1 -- Use INSERT
PRINT ('INSIDE SP: SET THE MODE TO INSERT')
END
END
IF @intRetVal <> 0 GOTO xxx_SP_Z_JDE_PROJECTS_EXIT -- Daily out

-- Validation Section
--TODO Add validation code here, set @intRetVal to NON-ZERO if error
--TODO Can use GOTO xxx_SP_Z_JDE_PROJECTS_EXIT at this point, if necessary

IF @intRetVal = 0 -- No validation errors
BEGIN
-- Save Section
--BEGIN TRANSACTION xxx_SP_Z_JDE_PROJECTS_01
BEGIN TRANSACTION
--SAVE TRANSACTION xxx_SP_Z_JDE_PROJECTS_02
IF @intMode = 1
BEGIN
-- Insert

INSERT INTO dbo.Z_JDE_PROJECTS
(
Project_No,
Status,
Create_Date,
Closed_Date
) VALUES
(
@Project_No,
@Status,
@Create_Date,
@Closed_Date

)

PRINT ('INSIDE SP: INSERTED THE RECORD SUCCESSFULLY')
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT -- , @MyID = scope_identity()
if @intErrNo <> 0
BEGIN
SELECT @intRetVal = -4,
@strErrMsg = @strErrMsg + 'Error INSERTING Z_JDE_PROJECTS. '
GOTO xxx_SP_Z_JDE_PROJECTS_ABORT
END
END
ELSE
IF @intMode = 2
BEGIN
-- Update

UPDATE U
SET
-- Project_No = @Project_No, -- Primary Key field, no update needed
Status = @Status,
Create_Date = @Create_Date,
Closed_Date = @Closed_Date
FROM dbo.Z_JDE_PROJECTS U
WHERE Project_No = @Project_No

PRINT ('INSIDE SP: UPDATED THE RECORD SUCCESSFULLY')
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
if @intErrNo <> 0
BEGIN
SELECT @intRetVal = -5,
@strErrMsg = @strErrMsg + 'Error INSERTING Z_JDE_PROJECTS. '
GOTO xxx_SP_Z_JDE_PROJECTS_ABORT
END
END
ELSE
BEGIN
SELECT @intRetVal = -6,
@strErrMsg = @strErrMsg + 'Incorrect @intMode [' + CONVERT(varchar(20), @intMode) + '] '
GOTO xxx_SP_Z_JDE_PROJECTS_ABORT
END

IF @intRetVal = 0 AND @intRowCount <> 1 -- No error but rowcount wrong
BEGIN
SELECT @intRetVal = -1, -- Incorrect row count (should be 1).
@strErrMsg = @strErrMsg + 'Row count error [' + CONVERT(varchar(20), @intRowCount) + '] '
GOTO xxx_SP_Z_JDE_PROJECTS_ABORT
END

xxx_SP_Z_JDE_PROJECTS_ABORT:
IF @intRetVal = 0
BEGIN

COMMIT TRANSACTION
PRINT ('INSIDE SP: COMMITTED THE RECORD SUCCESSFULLY')

END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT ('INSIDE SP: ERROR - ROLLBACKED THE RECORD SUCCESSFULLY')

END
END

xxx_SP_Z_JDE_PROJECTS_EXIT:

SET NOCOUNT OFF
SELECT 'FINISHED'

RETURN -- Return error number, 0=No error
END

The application will just pass the INPUT parameters and use the 'execute' method (provided as a part of the application API) to execute the Stored Procedure.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 07:31:27

Can you run this procedure fine from Query Analyzer? Also, have you set up a Profiler trace to see where the error is actually coming from?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-22 : 07:39:39

I'm guessing this is the problenm

PRINT ('INSIDE SP: UPDATED THE RECORD SUCCESSFULLY')
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

the print statement will lose @@error and @@rowcount from the previous statement.
@intRowCount will always be 0.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 07:48:09

Infact the version of SP which i ran through the query analyser doesn't have the PRINT statements !! As i said, through the Query analyser, it ran successfully !! Only when i run through my application, i am getting this problem.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 07:53:20

Check you MDAC version.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;294138

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 08:14:54

But it is for SQL Server 6.5. Mine is SQL Server 2000 !!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 08:16:18

It will still apply if the client is running an old version of MDAC and is using one of the older technologies that utilized that library. What version of MDAC?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 08:22:47

Let me be clear.

My customer's SQL SERVER ver is 2000 and i am accessing that database through my application, using SQL SERVER driver (DSN is created using this driver in my machine). The SQL Server Driver version is 2000.80.380.00. I am using ODBC-JDBC bridge to connect to the database.

So, the client, in my case, is not a SQL Server Client. Also, as i mentioned, if i run this SP through a query analyser (situated in some other machine), it runs great

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 08:39:32

My SQL Server driver version is 2000.85.1025.00. I would still try to update the MDAC. It's a painless thing to do. It probably takes longer to talk about it. Query Analyzer will not utilize the driver the same way your application is going to utilize it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 08:55:49

Ok, thanks for the patience. If i go for MDAC updation, what i need to do ? Basically, where do i need to update ? In my machine, i don't have an SQL client. I am running my appln(it is a third party product) as mentioned above, Do i need to ask the customer to update the MDAC on their side ?

I am sorry for dragging this....

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 09:00:14

yes. They can go to www.microsoft.com/data. The download is on the right-hand side menu (unless they've changed it).

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rameshkg
Starting Member

22 Posts

Posted - 2004-06-22 : 09:20:43

Thanks a lot ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources