Same code return different values!!

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 2005 Forums

Transact-SQL (2005)

Same code return different values!!

Author

Topic

dempen
Starting Member

7 Posts

Posted - 2009-08-25 : 10:12:41

Hi all,

I'm having a problem that is so strange that I hardly know how to explain it. But here it goes:

I have an existing stored procedure. If I execute it from the Object Eplorer it returns a certain value (it's a stored procedure doing a quite simple summary).
If I choose to modify the sproc and only give it a new name and run the new stored procedure, it returns a different value!!!

I know, you probably think that I am running the new stored procedure against another database or something like that but I assure you that I run it against the same as the other. And the code is identical!! (And it doesn't seem like I am running some cached code or something like that either, I will write about that further on.)

When this happened I went into some colleagues room and did the exact same thing and of course the new stored procedures returned the same value as the old one. Just like it should. They didn't believe what I told them happened for me and came with me into my room but could only ascertain that I in fact was correct.

And here comes the even more strange part. If I open one of the new stored procedures that they have created it shows the correct value. But if then re-save it here, without changing anything, it returns the "wrong" value again!! And this is of course a stored procedure that I have never opened on my computer before, so it can't be a cache issue either...

And if my colleagues then run "their" stored procedures again, on their computer, it returns the "wrong" value for them as well! And now it doesn't matter if they re-save the stored procedure, it keeps on returning the wrong value.

I logged on into another computer, with the same accout as on my own, and did the same thing and there everything worked as it should, so it seems like there's something wrong with my Management Studio!! That's the only conclusion I can reach in this straaange, strange situation.

Has anyone ever heard of something like this before??

The strange thing is that if you open the stored procedure on any computer, it always shows the same code, so I can't in my mind understand how it can return different values with the exact same code against the same database!! And I don't think that it could be a security issue either, since everything works fine with my account on another computer.
Is a stored procedure saves in two ways? Is it stored both as a textfile somewhere AND as compiled machinecode?

I am extremely confused, could someone please bring some clarity into this!?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 10:15:57

Can you post the code in the SP?

An infinite universe is the ultimate cartesian product.

dempen
Starting Member

7 Posts

Posted - 2009-08-26 : 03:39:55

YES!! I found the error. Although the stored procedure was saved with:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF

for some reason when I choose to modify the stored procedure in my Manangement Studio, it opens up with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

And I guess the ANSI_NULLS setting is the one causing the problem in this case? Does anyone know why my Management Studio opens up the stored procedure with these settings set to ON? Shouldn't it open it up with the same settings as it had when it was saved?

I tried to change my settings under Tool > Options > Query Execution > SQL Server > ANSI but it doesn't seem to change anything, it still opens with the settings set to ON.
I have also checked with my colleagues and they have the same settings in Management Studio as I have...

The only thing I can think of that I have installed lately, that could possibly change the settings in Management Studio, is Visual Studio 2008, but I doubt that it can have anything to do with this...?

dempen
Starting Member

7 Posts

Posted - 2009-08-26 : 03:43:17

Hmmmm... now I noticed that if I right-click on the Stored Procedure in question, in the Object Explorer, and choose "Modify" the first rows say:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

But if I choose "Script Stored Procedure as > CREATE To > New Query Editor Window" the first rows say:

USE [SME]
GO
/****** Object:  StoredProcedure [dbo].[ic_spr_report_orderintake_country]    Script Date: 08/26/2009 09:37:04 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 03:53:51

Which version of SSMS are you using?
I use SSMS 2008 (10.0.2531.0), and it works both ways.

N 56°04'39.26"
E 12°55'05.63"

dempen
Starting Member

7 Posts

Posted - 2009-08-26 : 04:06:46

quote:
Originally posted by Peso

Which version of SSMS are you using?
I use SSMS 2008 (10.0.2531.0), and it works both ways.

I use Management Studio for SQL Server 2005.

Detailed info:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.5512
Microsoft .NET Framework 2.0.50727.3082
Operating System 5.1.2600

I noticed that a colleague had SP3 applied and I'm gonna try to apply that and see if anything changes!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 05:44:57

Does the problem persist if you install the latest service pack?
You are currently on RTM (sp0), and the latest service pack is 3 with cumulative update 5
http://support.microsoft.com/kb/972511/en-us

N 56°04'39.26"
E 12°55'05.63"

dempen
Starting Member

7 Posts

Posted - 2009-08-26 : 06:55:28

SP3 did the trick! In the sense that I now get the same behaviour and same code if I do "Modify" or "Script Stored Procedure as > ALTER To > New Query Editor Window".

Why my Management Studio was acting so strange before I don't know, but I can only assume that it was a bug that had been fixed in a Service Pack OR that another Microsoft product has tampered with some settings of some kind...

Anyway, now I can't reproduce my problem anymore, so I guess my "problem" is solved!

A big thanks to all of you!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-08-26 : 09:55:13

I ran into that problem once using Crystal Reports, which had a different default setting for its connection than SQL Server query analyzer. Consequently running my procedure through QA return a different result set than Crystal Reports was showing calling the same sproc.
Took me two days to track down the problem. Much cursing was involved.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources