1

I'm really new to SQL Server development and I learn everyday.

Today I encountered a problem while executing multiple stored procedures in a query. I saw only one of both start. After a long research I could see that the query stops after running the first one.

This is my example:

begin
update Web.dbo._CoinPVPRegistered set Points=Points+1, [Last Kill]=@1target, [Date]=GETDATE(), pvp = pvp + 1 where CharID=@1killerid
insert Web.dbo.PlusNotice(Sent, Message)values(0,@1killername + ' has killed ' + @1target + ' in PVP!')
Exec Web.dbo._Achivement @1killerid,'18','0','0'
Exec Web.dbo._Achivement @1targetID,'19','0','0'
end

The system stops executing this query just after Exec Web.dbo._Achivement @1killerid,'18','0','0'

The next one does not start after the first is executed.

Do you have an idea or am I doing something completely wrong?

Thank you for any help

Edit: Basic procedure for _Achievement

USE [Web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_Achivement]
@CharID int,
@EventID int,
@ItemID int,
@strDesc nvarchar(max)
as
BEGIN
If @EventID = 18 
    Begin
        update web.dbo._Achievement set win = win +1 where CharID = @CharID
end
If @EventID = 19 
    Begin
    update web.dbo._Achievement set fail = fail +1 where CharID = @CharID
end
end

Edit2:

Complete Procedure:

USE [Web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[_CoinpvpregisteredSP]
@CharID        int,
@EventID        tinyint,
@Data1        int,
@Data2        int,
@strPos        varchar(64),
@Desc        varchar(128)

as
BEGIN

declare @1strip varchar(120)
declare @1killername varchar(120)
declare @1killerid int
declare @1target varchar(12)
declare @1targetID int
declare @Date datetime
declare @1targettime datetime
declare @2targettime datetime
declare @lvl nvarchar (max)
declare @Dlvl nvarchar (max)

set @1strip = REPLACE(@Desc,'[My: no job, Neutral, Kirin] [His(', '')
set @1killername = REPLACE(@1strip,'): no job, Neutral, Kirin]', '')
set @1killerid = (select CharID from _Char where CharName16=@1killername)
set @1target = (select CharName16 from _Char where CharID=@CharID)
set @1targetID = (select CharID from _char where Charname16 = @1target)
Set @Date = GetDate()
declare @IP1 varchar(64) = (select ip from _CharIP Where Charname = @1killername)
declare @IP2 varchar(64) = (select ip from _CharIP Where Charname = @1target)

Print @IP1
Print @IP2

    IF (@IP1 = @IP2)
            begin
            print 'sameip'
                Insert into _CheatTable (CharID,Reason,Date) Values (@1killerid,'Trying to Cheat on pvp mode by ip',GETDATE())      
            end
    else
        begin
        Set @lvl = (Select CurLevel from _Char where CharID=@1killerid)
        Set @Dlvl = (Select CurLevel from _Char where CharID=@CharID)
        print @lvl
        print @Dlvl
            If @lvl = @Dlvl
                begin
                    Set @1targettime = (select Date from _CoinPVPRegistered where CharID = @1killerid)
                    Set @2targettime = dateadd(minute, -1, getdate())
                        If @1targettime >= @2targettime  
                            begin
                                Insert into _CheaterTable (CharID,Reason,Date) Values (@1killerid,'Trying to Cheat on pvp mode coin',GETDATE())
                            end
                        else
                            begin
                                update _CoinPVPRegistered set Points=Points+1, [Last Kill]=@1target, [Date]=GETDATE(), pvp = pvp + 1 where CharID=@1killerid
                                insert PlusNotice(Sent, Message)values(0,@1killername + ' has killed ' + @1target + ' in PVP!')
                                --print @1killerid
                                --print @1targetID
        Exec _Achivement @1killerid,18,0,'0'
        Exec _Achivement @1targetID,19,0,'0'                                    
                                end
                end
        end
end 

Original Information Comming form a table By a external software So this is a Stored Procedure Who start to Run

Since This procedure is automated The Procedure Stop at the second exec (exec _achievement @1targetID,19,0,'0') She do not run this part

But If run the Procedure Whit the Value myself everything works

USE [Web]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[_CoinpvpregisteredSP]
        @CharID = 85307,
        @EventID = 20,
        @Data1 = 0,
        @Data2 = 0,
        @strPos = N'0',
        @Desc = N'[My: no job, Neutral, Kirin] [His(No_Maresy): no job, Neutral, Kirin]'

SELECT  'Return Value' = @return_value

GO

Result Whit manual launch = Success

41.47.240.109
5.107.179.65
140
140

(1 row(s) affected)
90086
85307
event18

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Event 19

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

How i can know she do not working as automated That simple To be sure i added a new Table

USE [Web]
GO

/****** Object:  Table [dbo].[_SystemEventConfirmation]    Script Date: 12/23/2015 11:21:07 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_SystemEventConfirmation](
    [CharID] [int] NULL,
    [Charname] [nvarchar](max) NULL,
    [EventID] [int] NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK__SystemEventConfirmation] PRIMARY KEY CLUSTERED 
(
    [Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Placed the table at the end of the _Achievement procedure to re-checking data

Insert into Web.dbo._SystemEventConfirmation (CharID,Charname,EventId,Date) values (@CharID,@Charname,@EventID,GetDate())

Whit automated system The result is

85307   SpOokShoW   18  2015-12-22 19:55:23.397

Whit the procedure Send manualy whit exactly same data

85307   No_Maresy   19  2015-12-22 19:57:15.123
90086   SpOokShoW   18  2015-12-22 19:57:15.020

Im totaly lost

Why that should works While running it by hands and not Whit automated fonction on software who send exactly same data

closed as unclear what you're asking by Max Vernon, Philᵀᴹ, RolandoMySQLDBA, RLF, Shawn Melton Dec 28 '15 at 1:35

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Any chance there was an error when executing the first call? – Kenneth Fisher Dec 22 '15 at 15:23
  • 1
    First thing that I see is that you are passing a string '18' or '19' to the SP, but you are working with an integer value. This is bad by design. Change this and then add some print 'start' / print 'end' commands into the SP. Then have a look again at the results. – Magier Dec 22 '15 at 15:23
  • Have you added a Try/Catch around all of the SQL and then printing/catching any errors ? When you say it does not start do you get any errors, timeout warnings etc ? Has it ever worked and recently just stopped working or has it never worked ? – armitage Dec 22 '15 at 16:25
  • @KennethFisher I have tested Result using 'Print Value' but seem working and give to me the proper result before executing Query – Fabien Bureau Dec 22 '15 at 17:23
  • @Magier I didn't get the point where the design is bad could you give me a exemple of it ? At the Print result for value i got proper value before Sending to _achievement Procedure it's just got a problem To send both of them since first one is working – Fabien Bureau Dec 22 '15 at 17:23
1

This sample will work, maybe you want to continue based on this:

CREATE PROCEDURE [dbo].[_Achivement_test]
@CharID int,
@EventID int,
@ItemID int,
@strDesc nvarchar(max)
as
BEGIN

    If @EventID = 18 
    Begin
        print 'win part' --update web.dbo._Achievement set win = win +1 where CharID = @CharID
    end

    If @EventID = 19 
    Begin
        print 'fail part' --    update web.dbo._Achievement set fail = fail +1 where CharID = @CharID
    end

    if @EventID <> 18 and @EventID <> 19 
    begin
        print 'Not implemented exception part'
    end
end

GO

--TEST:
Exec [dbo].[_Achivement_test] 1,18,0,'0'
Exec [dbo].[_Achivement_test] 1,19,0,'0'
Exec [dbo].[_Achivement_test] 1,99,0,'0'
  • Whit the Test i got a succesfull result as win part fail part Not implemented exception part So that should be a issue Before it – Fabien Bureau Dec 22 '15 at 17:27
  • 1
    I don't really get what you say here. Is your Problem solved? – Magier Dec 22 '15 at 21:13
  • No the Problem is not Solved ... Ill add more information about system in main thread – Fabien Bureau Dec 23 '15 at 10:12
0

Since your are still struggeling with this, please follow those advise to figure it out:

Create another test log table:

create table dbo.mySyslog (i int identity (1,1), txt varchar(100), dt datetime) 
GO

Use the _Achievement test Procedure from my last answer for testing. Change it and add the following line after the beginning AND end of the procedure:

Insert into dbo.mySyslog (txt, dt) values ('Procedure started', getDate()) 
/* ... */ 
Insert into dbo.mySyslog (txt, dt) values ('Procedure completed', getDate()) 

Double check the indenting of your [dbo].[_CoinpvpregisteredSP] Procedure!! Looking at this code and the calls for Procedure _Achievement, it looks like they are not at the correct position of the procedure.

Then i would remove all unneccesary code from the procedure [dbo].[_CoinpvpregisteredSP] to make sure that it does not cause the problem and run it again. You want to know if the procedure _Achievement is started or not and in case not, why, so I would remove all logic and try it without first.

Sample:

create PROCEDURE [dbo].[_CoinpvpregisteredSP]
@CharID        int,
@EventID        tinyint,
@Data1        int,
@Data2        int,
@strPos        varchar(64),
@Desc        varchar(128)

as
BEGIN

    declare @1strip varchar(120)
    declare @1killername varchar(120)
    declare @1killerid int
    declare @1target varchar(12)
    declare @1targetID int
    declare @Date datetime
    declare @1targettime datetime
    declare @2targettime datetime
    declare @lvl nvarchar (max)
    declare @Dlvl nvarchar (max)

    set @1strip = REPLACE(@Desc,'[My: no job, Neutral, Kirin] [His(', '')
    set @1killername = REPLACE(@1strip,'): no job, Neutral, Kirin]', '')
    set @1killerid = (select CharID from _Char where CharName16=@1killername)
    set @1target = (select CharName16 from _Char where CharID=@CharID)
    set @1targetID = (select CharID from _char where Charname16 = @1target)
    Set @Date = GetDate()
    declare @IP1 varchar(64) = (select ip from _CharIP Where Charname = @1killername)
    declare @IP2 varchar(64) = (select ip from _CharIP Where Charname = @1target)

    Print @IP1
    Print @IP2

        IF (@IP1 = @IP2)
        begin
            print 'CASE 1: sameip'
            --Insert into _CheatTable (CharID,Reason,Date) Values (@1killerid,'Trying to Cheat on pvp mode by ip',GETDATE())      
        end
        else
        begin
            print 'CASE 2: no same Ip'
            Set @lvl = (Select CurLevel from _Char where CharID=@1killerid)
            Set @Dlvl = (Select CurLevel from _Char where CharID=@CharID)
            print @lvl
            print @Dlvl
            If @lvl = @Dlvl
            begin
                Print 'CASE 3: ldl=dlvl'
                Set @1targettime = (select Date from _CoinPVPRegistered where CharID = @1killerid)
                Set @2targettime = dateadd(minute, -1, getdate())
                If @1targettime >= @2targettime  
                begin
                    print 'CASE 4: cheater'
                    --Insert into _CheaterTable (CharID,Reason,Date) Values (@1killerid,'Trying to Cheat on pvp mode coin',GETDATE())
                end
                else
                begin
                    print 'CASE 5: no cheater => Execute Achievement Procedure'
                    --update _CoinPVPRegistered set Points=Points+1, [Last Kill]=@1target, [Date]=GETDATE(), pvp = pvp + 1 where CharID=@1killerid
                    --insert PlusNotice(Sent, Message)values(0,@1killername + ' has killed ' + @1target + ' in PVP!')
                    --print @1killerid
                    --print @1targetID
                    print 'Starting Excuting _Achievement'
                    Exec dbo.[_Achivement] 1,18,0,'0'
                    print 'Starting Excuting _Achievement'
                    Exec dbo.[_Achivement] 1,19,0,'0'                                    
                end
            end
        end
end 

Then Call the [dbo].[_CoinpvpregisteredSP] SP and you will see in your logtable what is going on.

If everything is logged as you expected, then try to add one command we have disabled after another again to see which of them causes problems.

If you want to try the "automated" call from third party software you mentioned, you may want to change the informational PRINTS to Insert Into dbo.mySyslog .... as well to see what has happened during the call.

Generally speaking I would also try to use a TRY CATCH just in case something really weird happens.

And one more suggestion - a command like this (as you frequently use it):

set @1target = (select CharName16 from _Char where CharID=@CharID)

can basically fail very easily in case the column you filter on are not the primary key and there are more than 1 results. Then you will see the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Not the answer you're looking for? Browse other questions tagged or ask your own question.