Strange things with SPs - 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

SQL Server Administration (2000)

Strange things with SPs

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-04-04 : 18:21:00

I have noticed that today I am getting strange stuff when I execute a stored proc. I am running SQL2k Standard with SP2.

I created an SP that has the following:

update blah set col1 = 0 - col1 where type = 1
update blah set col1 = 0 where type = 2

exec sp_blah
results:

(117 row(s) affected)

(5 row(s) affected)

(81 row(s) affected)

(4 row(s) affected)

5 rows? and 4 rows? Where did that come from?
When I run the updates by themselves the results are:

(117 row(s) affected)

(81 row(s) affected)

I really dont understand whats going on here... Why are there extra "row(s) affected" when I run the sp?

Thanks in advance...

Daniel
SQL Server DBA

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-04-04 : 18:23:55

If you can post the entire stored procedure then we might be able to get a better look at whether or not something else is causing the updates. I trust you have posted the only updates, but without more information, it makes it a little harder to help debug.

Jeremy

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-04-04 : 18:29:37

This is the SP. I only replaced the name and table names other than that its the entire SP...

CREATE PROCEDURE sp_Blah AS

update blah
set amt = 0 - amt
where tran_type = 2

update blah
set amt = 0
where tran_type = 3
GO

No triggers exist...

Nothing unusual in the Server Config or system startup params...

Also Please note... The server now seems to be doing this with all SPs that do DML

Daniel
SQL Server DBA

Edited by - sqlserverdba_dan on 04/04/2002 18:30:59

Edited by - sqlserverdba_dan on 04/04/2002 18:36:13

dsdeming

479 Posts

Posted - 2002-04-05 : 08:08:53

Are you using referential integrity constraints? Could this be cascading?

Jay99

468 Posts

Posted - 2002-04-05 : 09:29:02

Try using profiler to capture statement starting . . . that may give you some insight.

Jay
<O>

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-04-05 : 15:03:50

quote:

Try using profiler to capture statement starting . . . that may give you some insight.

Jay
<O>

Yeah I hadnt thought of that

Good idea.

Although I found that it was specifically with the SQL file that I saved. When I cut and paste the text from the SQL file or opened it as a template this was not occurring. It only happens when I open the sql file in SQL QA and run stuff in it. So there must be some setting in SQLQA that is causing this to occur... I'll post what I find the cause to be.

Daniel
SQL Server DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-04-05 : 15:08:23

BAH! I found the problem... This is not something that SQL 7 would do and I am much more familiar with it than 2k. Man I was looking for signs that a hacker got into my SQL Server and put triggers on tables and all sorts of stuff. When all along it was the fact that I had CTRL-K (the execution plan) turned on in SQL QA. It really makes no since to my why the SP and running only the DML should return different stuff when the Execution plan is on... Can someone explain why?

Daniel
SQL Server DBA

Edited by - sqlserverdba_dan on 04/05/2002 15:14:52

Jay99

468 Posts

Posted - 2002-04-05 : 15:56:27

That doesn't make since to me either

. . . I've never had that happen on 2k. Have you looked into sp_MSExorcist? . . .

Glad you at least found the problem

Jay
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources