SQL-2000 - SQL Server Forums

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

Transact-SQL (2000)

SQL-2000

Author

Topic

Digico
Starting Member

3 Posts

Posted - 2010-11-07 : 18:04:26

Executing a Stored Procedure or a script from a Win32 application, goes fine at first time, but dose not execute again in the same session. Have to close the app and open again to execute the script.
This same happens even under Query analyzer, firs [Open] generates the execute script and runs ok. If I change a parameter and run this script again, nothing happens..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-07 : 18:33:45

Post the stored procedure code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Digico
Starting Member

3 Posts

Posted - 2010-11-11 : 10:48:06

quote:
Originally posted by tkizer

Post the stored procedure code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

-- The main Procedure is DCM15EXEC

/****** Object: Stored Procedure dbo.DCM15EXEC Script Date: 11-11-2010 17:38:54 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DCM15EXEC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DCM15EXEC]
GO

/****** Object: Stored Procedure dbo.DCM15EXEC Script Date: 19-07-2010 11:32:33 ******/

CREATE PROCEDURE DCM15EXEC @ZIC INT
AS
BEGIN
DECLARE @RQM INT,@TID2 INT,@RC INT

DECLARE Qv CURSOR SCROLL FOR
SELECT DCRQM FROM QDCMCID15 WHERE CID=@ZIC
ORDER BY SOIJAZA

OPEN QV
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Qv INTO @RQM
EXEC @RC = SP_DCMCID15 @RQM
END -- FETCH
Close Qv
DEALLOCATE Qv
END
GO

/****** Object: Stored Procedure dbo.SP_DCMCID15 Script Date: 11-11-2010 17:39:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_DCMCID15]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_DCMCID15]
GO
/****** Object: Stored Procedure dbo.SP_DCMCID15 Script Date: 22-07-2010 20:44:23 ******/
CREATE PROCEDURE [DBO].[SP_DCMCID15]
@ZRQM Int
AS
IF (SELECT NOXIJAZA FROM DCMCID15 WHERE DCRQM=@ZRQM)<>'??????????'
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
SET DATEFORMAT DMY
DECLARE @BB INT,@ROPEN REAL, @STARTDATE DATETIME,@ONDATE DATETIME,@ZID INT,@ISTHQ INT

SET @ONDATE = (SELECT SOIJAZA FROM DCMCID15 WHERE DCRQM=@ZRQM)
SET @ZID = (SELECT CID FROM DCMCID15 WHERE DCRQM=@ZRQM)

IF EXISTS (SELECT * FROM DCMCID15 WHERE NOXIJAZA='??????????' AND SOIJAZA<@ONDATE
AND CID=@ZID)
BEGIN -- HE HAS PREVIOUS TASWIA
SET @ROPEN= (SELECT TOP 1 RASIDTODATE FROM QDCMCID15
WHERE NOXIJAZA='??????????' AND SOIJAZA<@ONDATE AND CID=@ZID
ORDER BY SOIJAZA DESC)
SET @STARTDATE = (SELECT TOP 1 SOIJAZA FROM QDCMCID15
WHERE NOXIJAZA='??????????' AND SOIJAZA<@ONDATE AND CID=@ZID
ORDER BY SOIJAZA DESC)
END ELSE BEGIN -- HE HAS NO TASWA, START FROM BTQCID11
SET @ROPEN= (Select TOP 1 isnull(IFTIAHIJAZA,0)From BTQCID11 WHERE CID=@ZID)
SET @STARTDATE=(Select TOP 1 isnull(DOIFTITAHIJAZA,0)From BTQCID11 WHERE CID=@ZID)
END

SET @BB=(SELECT DATEDIFF(yy,DOBIRTH,@ONDATE) FROM BTQNPERSONAL WHERE CID=@ZID)
IF (@BB>=50) SET @ISTHQ=45 --ELSE SET @ISTHQ=30

SET @BB=(SELECT SANWATKIBRA+DATEDIFF(yy,DOMUBASHARA,@ONDATE) FROM BTQCID11 WHERE CID=@ZID)
IF (@BB>=20) SET @ISTHQ=45 --ELSE SET @ISTHQ=30

UPDATE DCMCID15
SET RASIDTODATE =ISNULL(DBO.GETRASID(CID,SOIJAZA),0)+AYAM3OTLA,
IJAZMULAHDA ='FROM..'+convert(char(12),@STARTDATE)+'..Rasid..'+STR(@ROPEN,5,1),
RASIDFROM = @STARTDATE ,
RASIDX = @ROPEN,
ISTHQAQ2= @ISTHQ,
EOIJAZA = SOIJAZA+MUDA

WHERE (DCRQM = @ZRQM)-- AND NOXIJAZA<>'??????????'

END --(SELECT NOXIJAZA FROM DCMCID15 WHERE DCRQM=@ZRQM)<>'??????????'

GO

/****** Object: User Defined Function dbo.GETRASID Script Date: 11-11-2010 17:40:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GETRASID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GETRASID]
GO

CREATE FUNCTION dbo.GETRASID (@ZID INT,@ONDATE smalldatetime)
RETURNS REAL
AS
BEGIN

DECLARE @BB INT,@ROPEN REAL, @STARTDATE DATETIME,@ISTHQ INT
IF EXISTS (SELECT * FROM DCMCID15 WHERE NOXIJAZA='??????????' AND EOIJAZA<@ONDATE AND CID=@ZID)
BEGIN -- SET DATEFORMAT DMY
SET @ROPEN= (SELECT TOP 1 RASIDTODATE FROM QDCMCID15
WHERE NOXIJAZA='??????????' AND SOIJAZA<@ONDATE AND CID=@ZID
ORDER BY SOIJAZA DESC)
SET @STARTDATE = (SELECT TOP 1 SOIJAZA FROM QDCMCID15
WHERE NOXIJAZA='??????????' AND SOIJAZA<@ONDATE AND CID=@ZID
ORDER BY SOIJAZA DESC)
END ELSE BEGIN
SET @ROPEN= (Select TOP 1 isnull(IFTIAHIJAZA,0)From BTQCID11 WHERE CID=@ZID)
SET @STARTDATE= (Select TOP 1 isnull(DOIFTITAHIJAZA,0)From BTQCID11 WHERE CID=@ZID)
END

SET @BB=(SELECT DATEDIFF(yy,DOBIRTH,@ONDATE)FROM BTQNPERSONAL WHERE CID=@ZID)
IF (@BB>=50) SET @ISTHQ=45 --ELSE SET @ISTHQ=30

SET @BB=(SELECT SANWATKIBRA+DATEDIFF(yy,DOMUBASHARA,@ONDATE) FROM BTQCID11 WHERE CID=@ZID)
IF (@BB>=20) SET @ISTHQ=45 --ELSE SET @ISTHQ=30

SET @BB= ( -- DAYS TO EXCLUDE FROM RASID CALC.
SELECT ISNULL(SUM(MUDA),0) from QDCMCID15
WHERE (CID=@ZID) AND (SOIJAZA>=@STARTDATE AND SOIJAZA<@ONDATE)AND (NOXIJAZA<>'?????')
AND NOXIJAZA<>'??????????')

-- ISTEHQAQ FOR PERIOD
--SELECT (ISTHQAQ*(DateDiff(dd, @STARTDATE, @ONDATE)-@BB)/365 )
RETURN (Select @ISTHQ*(DateDiff(dd, @STARTDATE, @ONDATE)-@BB)/365
from BTQCID11 WHERE CID=@ZID )+ @ROPEN -
-- (SELECT (ISNULL(SUM(MUDA),0)- Sum(AYAM3OTLA)) from QDCMCID15 -- GIVEN IJAZA
(SELECT (ISNULL(SUM(MUDA),0)) from DCMCID15 -- GIVEN IJAZA
WHERE (CID=@ZID) AND (SOIJAZA>=@STARTDATE AND SOIJAZA<@ONDATE)
AND (LTRIM(RTRIM(NOXIJAZA))='?????') )
END

GO

ICT SP.

Digico
Starting Member

3 Posts

Posted - 2010-11-24 : 12:51:41

Sorry folks.. and thank you tara.
Done.. dum i was!

ICT SP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources