returning records in store procedure

[Roy Goldhammer]

Hello there

I have some huge problem in one of my store procedures

when i run it on sql it returns rowset fine

but when i run it from adp first it brings an error "Store procedure made
successfult but did not return any records"

and in the next run it doesn't do anything

the store procedure has temp table and many calculations made on it. What
can cause this problem?

 

[RJ]

Try inserting

Set NOCOUNT On (right after the AS)

AND

Set NOCOUNT OFF (right before the Return)

This will likely solve the problem. Check online as to the why!

Good luck,
RJ

 

[Roy Goldhammer]

Whell RJ

here is the store procedure
whats wrong with it?

USE [dbmCdrDataGenerator]

GO

/****** Object: StoredProcedure [dbo].[SeteliteTables_prc_new] Script Date:
07/12/2007 15:32:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[SeteliteTables_prc_new]

@TableId int,

@Data varchar(8000)


AS

BEGIN

set nocount on

DECLARE @SQL varchar(8000), @ID int

SET @SQL = ''


CREATE TABLE #Setelite (

ID int identity(1,1),

DatabaseName varchar(100),

SchemaName varchar(100),

TableName varchar(100),

Apperance int,

SchemaId int,

TableId int,

FieldId int,

TempApperance int,

Criteria varchar(200)

)


EXEC (@sql)

INSERT #Setelite(DatabaseName, SchemaName, TableName

,Apperance ,SchemaId ,

TableId ,FieldId ,Criteria, TempApperance)

SELECT DatabaseName, SchemaName, TableName,

0, S.SchemaId, T.TableId, F.FieldId,

'where ['+ F.FieldName + '] like ''' + @Data + '''',

case when exists(select 1

from sys.tables

where name = TempTable.TempTable_fn(T.TableId))

then 1 else 0 end

FROM [References] R

JOIN ReferenceFields RF ON R.ReferenceId = RF.ReferenceId

JOIN Fields F ON RF.ForeignFieldId = F.FieldId

JOIN Tables T ON R.ForeignTableId=T.TableId

JOIN Schemas S ON T.SchemaId = S.SchemaId

JOIN Databases D ON S.DatabaseId = D.DatabaseId

WHERE PrimaryTableId=@TableId

SELECT @SQL = @SQL + '

UPDATE #Setelite SET Apperance = (SELECT count(1)

from ' + tempTable.TempTable_fn(TableId) + '

' + Criteria + ')

WHERE Id=' + ltrim(Id)

FROM #Setelite

WHERE TempApperance=1


EXEC (@SQL)

SET @SQL = ''

SELECT @SQL = @SQL + '

UPDATE #Setelite SET Apperance = (SELECT count(1)

from ['+ DatabaseName + '].['+ SchemaName + '].['+

TableName + ']

' + Criteria + ')

WHERE Id=' + ltrim(Id)

FROM #Setelite

WHERE Apperance=0


EXEC (@SQL)


select TableName ,

Apperance ,

SchemaId ,

TableId ,

FieldId ,

Criteria, TempApperance

FROM #Setelite

drop table #Setelite

set nocount off

END