Inconsistent Behaviour of SELECT in Stored Proc...

[BaldEddy]

The problem we are currently experiencing is bizzare to say the least. Some background info;
The SQL Server 2000 databse provides recipe and material management facilities to a SCADA application (Wonderware's InTouch v7.1). This particular problem involves a VERY simple stored-proc and table combination, the table consists of 2 columns, Created as follows;

"CREATE TABLE [dbo].[Bins_Sources_T] (
[iBinNumber] [int] NOT NULL ,
[sSourceName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO "

The stored-proc is also simple, created as follows;
"SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE BinsSources_ListSelection_SP
@iBin int
AS
-- NB return column names for bind list Util_ListBox_IntMsg
SELECT iBinNumber As iItemNumber, sSourceName AS sItemDesc FROM Bins_Sources_T
WHERE iBinNumber = @iBin
ORDER BY sSourceName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"

THE PROBLEM: the stored-proc returns a recordset of possible sources for the bin number provided in the argument @iBin. This works fine from SQL Query Analyser, but when triggering the proc from the SCADA application the proc resturns zero records.

All the obvious things have been checked, i.e. the stored-proc call contains the correct bin number, records exist for that bin in the table, but for some reason from certain PC's ( we have one working fine and another not - both running the same SCADA application and both with identical ODBC connection set-up) here in the office, the SELECT statement returns no records from the table.

Modifying the stored-proc to grab an integer and string recordset from another table in the database works fine. Modifying the stored-proc to return the argument supplied and a test string on the faulty system also works fine.

This problem first occurred on our customer's site, I managed to recreate the problem on my own PC by executing the in-built stored procedure "sp_changeobjectowner" on the table in question, changing the table owner from dbo to "intouch" and then back again. It's as if some kind of trust has been lost (I am now at clutching-at-straws point).

As I said earlier - BIZARRE! - can anyone out there think of possible causes and more importantly the remedy to this oddball problem?

Many thanks

 

[tlbroadbent]

Add SET NOCOUNT ON to the stored procedure prior to the SELECT statement.

CREATE PROCEDURE BinsSources_ListSelection_SP
@iBin int
AS
SET NOCOUNT ON

-- NB return column names for bind list Util_ListBox_IntMsg
SELECT iBinNumber As iItemNumber, sSourceName AS sItemDesc FROM Bins_Sources_T
WHERE iBinNumber = @iBin
ORDER BY sSourceName
GO
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[BaldEddy]

Thanks for the suggestion - but it doesn't work. I don't think the problem actually lies with the stored proc syntax, as it's working fine from one PC and not the other.

I think the crucial point here in the office occurred when I had my PC working fine (i.e. the SCADA application was retrieving and displaying the recordset fine) and this stopped working after I changed the owner of the table in question using "sp_changeobjectwoner" from Enterprise Manager SQL Query Analyser (changed from "dbo" to "intouch" and then back to "dbo" again) - Why should this break the mechanism?, and why only from my PC? (the other PC in the office still works fine)

Confused? - you will be.

 

[tlbroadbent]

Have you used the SQL Profiler to trace the execution of the SP from the application on each PC and from Query Analyzer? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[BaldEddy]

Thanks for the tip, the SQL Profiler didn't reveal anything though.

It's beginning to look more like a corrupt SCADA application, as the stored procedure is executing fine when run from MS Query in MS Excel on the apparently suspect PC. Further revelations yesterday were that the apparently good PC was displaying rather odd behaviour also, but in a more subtle incarnation. Reverting to an old version of the SCADA application does not eradicate the problem - suggesting that the installation of Wonderware's InTouch SCADA has become corrupt itself.

Hope to have a conclusion to this by the end of the week after trying the application on a new PC with a virgin install of Wonderware's wonderful(!) software.

Regards.