Empty resultset from some of the stored procedures (SQL-server and LabVIEW 7.0 and db-toolkit)

[boussard]

I have problem with two of my stored procedures. One of them works fine but two of them give no feedback on success or not which yields in an empty recordset. The one that works gives me a recordset so that's OK. The stored procedures are created in a SQL-server by a colleague and gives him recordsets when he tests them.

As I can see I do _EXACTLY_ the same in the code... with the stored procedures, see attached picture.

All stored procedures does the actual work, but the ones that do insertion fails to give me response as a recordset.

Two pictures show the different (or similar) code

Any suggestions?

[boussard]

Can someone help me out here?

[Dennis_Knutson]

Well, your bmp doesn't help much. You are not doing exactly the same thing according to the picture and the name of the sp and parameters can't even be seen. Have you run all the stored procedures on your machine using eithe MS Query or SQL Query Analyzer? Frankly, without having the database or procedures, helping you find an answer might be very difficult.

[boussard]

I've tried to execute the stored procedures in SQL query analyzer and they work. But in LabVIEW, as soon as I do an UDATE in the procedure, the recordset is not delivered. But the SP still does its action (insert values etc.)

This one works Recordset returned! (note parts are removed with /*...*/):
=======================================
CREATE PROCEDURE spDEBUG

@RorNr Char(5),
@Kasskod Char(3),
@Kst Char(5)

AS

DECLARE @Error int

SET @Error = 0

--BEGIN TRAN

/*
--Märk röret som kasserat i tRor
UPDATE tRor
SET Kasserat=1, Vattentryck=1
WHERE RorNr=@RorNr


--Om röret inte fanns i tRor, lägg till det
IF @@ROWCOUNT=0
BEGIN
INSERT
INTO tRor(RorNr, Kasserat, Vattentryck)
VALUES (@Rornr, 1,1) --1,1=Kasserat, Vattentryckprovat

IF @@ROWCOUNT=0
SET @Error=1

END

--Skapa post med kassinfo i tKassation
IF @Error=0
BEGIN
INSERT
INTO tKassation(RorNr, Kasskod, Kst)
VALUES (@RorNr, @Kasskod, @Kst)

IF @@ROWCOUNT=0
SET @Error=1
END
*/
--@Error=0 AND @@ERROR=0
IF 1=1
BEGIN
-- COMMIT TRAN
--SELECT 0 as Status, 'OK' as Msg
SELECT 0 , 'OK'
END
ELSE
BEGIN
-- ROLLBACK TRAN
--SELECT 1 as Status, 'Fel' as Msg
SELECT 1, 'Fel'
END
GO
=======================================

And, this one does the actual INSERT as expected but does NOT give me a recordset (0,'OK') or (1,'Fel'):

=======================================
REATE PROCEDURE spDEBUG

@RorNr Char(5),
@Kasskod Char(3),
@Kst Char(5)

AS

DECLARE @Error int

SET @Error = 0

--BEGIN TRAN

--Märk röret som kasserat i tRor
UPDATE tRor
SET Kasserat=1, Vattentryck=1
WHERE RorNr=@RorNr


--Om röret inte fanns i tRor, lägg till det
IF @@ROWCOUNT=0
BEGIN
INSERT
INTO tRor(RorNr, Kasserat, Vattentryck)
VALUES (@Rornr, 1,1) --1,1=Kasserat, Vattentryckprovat

IF @@ROWCOUNT=0
SET @Error=1

END

--Skapa post med kassinfo i tKassation
IF @Error=0
BEGIN
INSERT
INTO tKassation(RorNr, Kasskod, Kst)
VALUES (@RorNr, @Kasskod, @Kst)

IF @@ROWCOUNT=0
SET @Error=1
END

--@Error=0 AND @@ERROR=0
IF 1=1
BEGIN
-- COMMIT TRAN
--SELECT 0 as Status, 'OK' as Msg
SELECT 0 , 'OK'
END
ELSE
BEGIN
-- ROLLBACK TRAN
--SELECT 1 as Status, 'Fel' as Msg
SELECT 1, 'Fel'
END
GO
==========================================================

Anyone that has experienced anything like this?

[boussard]

Solved it!

Add this:

SET NOCOUNT ON
and
SET NOCOUNT OFF
instead of the comments in previous post's first SP-code snippet.

Had to go next Recordset on the recordset-reference to get the last one...