April 9, 2012 at 12:14 pm
Hello All,
Little stumped!
Have a simple select stmt (SQL 2008):
1. Returns the correct result when ran alone in management studio
2. Returns a value (this value was an older value which was there at one point in time, but no longer there), that is different when ran inside a simple stored procedure
Any ideas?
Thanks.
April 9, 2012 at 12:16 pm
mymail.default (4/9/2012)
Hello All,Little stumped!
Have a simple select stmt (SQL 2008):
1. Returns the correct result when ran alone in management studio
2. Returns a value (this value was an older value which was there at one point in time, but no longer there), that is different when ran inside a simple stored procedure
Any ideas?
Thanks.
Nope. We can't see from here what you see there. I guess our crystal balls are broken.
April 9, 2012 at 12:17 pm
Lynn Pettis (4/9/2012)
mymail.default (4/9/2012)
Hello All,Little stumped!
Have a simple select stmt (SQL 2008):
1. Returns the correct result when ran alone in management studio
2. Returns a value (this value was an older value which was there at one point in time, but no longer there), that is different when ran inside a simple stored procedure
Any ideas?
Thanks.
Nope. We can't see from here what you see there. I guess our crystal balls are broken.
+1
Without seeing your code (ateast a mockup version of it), what we say are just shot in the dark!
April 9, 2012 at 12:20 pm
create proc Test
as
select CodeValue from TableA whereID = 1334802
Exec Test returns a value different from running the select stmt by itself
April 9, 2012 at 12:23 pm
Run in the same scope?
Any schemas in use (besides dbo, of course)?
Data volatile?
Isolation levels in use?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 12:24 pm
mymail.default (4/9/2012)
where ID = 1334802
Is the ID value parameter-driven? Or even in your SP u hard-code 1334802 ? [Dumb question, i know :-D]
April 9, 2012 at 12:26 pm
Hard-coded!
April 9, 2012 at 12:26 pm
Please read and follow the instructions in this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 9, 2012 at 12:31 pm
Thanks, but doesn't help here 😉
Very stumped on this one!!
April 9, 2012 at 12:34 pm
mymail.default (4/9/2012)
Thanks, but doesn't help here 😉Very stumped on this one!!
How can you say that providing the ddl and sample data to the people trying to help you won't help here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2012 at 12:38 pm
Sean Lange (4/9/2012)
mymail.default (4/9/2012)
Thanks, but doesn't help here 😉Very stumped on this one!!
How can you say that providing the ddl and sample data to the people trying to help you won't help here?
Fully agree, Sean!
April 9, 2012 at 12:43 pm
I doubt sample tables and DDL/DML will help on this. It'll be something that wouldn't normally be included in those.
Check the questions I posted earlier. See where those take you.
Those are all things that could easily result in the same query with the same parameter value giving different results, in the same database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 12:45 pm
Sorry, I am not following you here...
April 9, 2012 at 1:17 pm
GSquared (4/9/2012)
Run in the same scope?Any schemas in use (besides dbo, of course)?
Data volatile?
Isolation levels in use?
Those questions.
Are the two scripts (query and proc execute) being run in the same scope?
Example:
select * from MyTable where MyColumn = 5;
exec dbo.MyProc 5;
If those are run as a single script, they'll be in the same scope as each other. At least the same connection, same connection settings, same credentials, etc.
Are there any schemas in use for database objects, besides "dbo".
Example:
select * from dbo.MyTable;
select * from Bob.MyTable;
"dbo" vs "Bob" will actually access two separate tables. Depending on schema-specification in the queries, and the connections used, "select * from MyTable" might select data from either one of those tables, probably with different data in them.
You can find if the database has more than one schema in it by querying "select * from sys.schemas".
Is the data volatile? Meaning is it constantly changing. Some databases, updates/inserts/deletes are several times per second in some tables. Thousands per second in some cases. Is the data like that, or are you the only one accessing it?
What isolation levels are the queries using? For example, does either one have "with nolock" in the query? Or "set isolation level read uncommitted"? Or snapshots? Or repeateable-read on an unclosed transaction?
Depending on the answers to those things, what we look for next will be different in different scenarios.
More clear? Or am I muddying it even worse now?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 2:56 pm
Thank you GSquared. I think you have nailed it. Here is how I have:
The stored proc I created is in a different schema:
create proc Schema1.Test
as
select CodeValue from TableA whereID = 1334802
1. Exec Schema1.Test
2. select CodeValue from TableA whereID = 1334802
The above 2 stmts return different values.
However, if I recreate the stored proc as Test (under dbo), I get identical results.
My question still though is, what difference is cauisng the different results.
Thanks again.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply