Stored Proc outputs differently than QA

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)

Stored Proc outputs differently than QA

Author

Topic

reactancexl
Starting Member

6 Posts

Posted - 2013-04-25 : 21:58:49

I have a stored proc that is determining the differnece of two tables. One table has more columns than the other table, but only care about the PK columns and few others. When I run the T-sql in query analyzer it returns one record(which is correct) when executing the proc from query anlyzer and outside program returns all the records. Below is the proc code.

Use ePO
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prc_epo_get_supplier_pi_value_map]

AS

SET NOCOUNT ON

Select supplier_id, company_code, sa_xml_ind
FROM tbl_epo_supplier a
WHERE NOT EXISTS
(SELECT 1 FROM tbl_epo_supplier_pi_value_map b
WHERE a.supplier_id = b.supplier_id
AND a.company_code = b.company_code
AND a.sa_xml_ind = b.sa_xml_ind
)
SET NOCOUNT OFF
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 00:24:37

are you sure you've applied this logic in proc which exists in db. check the definition of proc in db using sys.sql_modules view and make sure its same the above. Otherwise execute this script to alter proc and then execute it using exec

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

reactancexl
Starting Member

6 Posts

Posted - 2013-04-26 : 09:05:12

Yes, it is there. COuld it be some setup in the environment? this is really annoying as this is a simple proc.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 09:32:27

There is nothing in the stored procedure that I can see that would cause a different behavior when run as an ad-hoc query. Nor is there anything that can be impacted by the environment settings such as ANS_NULLS etc.

Just for laughs, do the following: script the stored procedure (by right-clicking on the stored procedure name in SSMS object explorer; in the window that comes up, highlight just the select statement and execute. Compare that with what you said you were getting when you run the query by itself.

reactancexl
Starting Member

6 Posts

Posted - 2013-04-26 : 09:40:40

I did that and the results were 1 record which I expect to see. This is driving me crazy.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 10:08:13

How are you running the stored procedure? In that same window where you scripted the stored procedure to, at the very bottom, type the following, highlight it and execute:

exec [dbo].[prc_epo_get_supplier_pi_value_map]

reactancexl
Starting Member

6 Posts

Posted - 2013-04-26 : 10:15:21

I am running it from a different query window. I typed in the syntax provide and get the same results which is all the records in the table. expected result is 1 as there is only one difference in a row. thx

reactancexl
Starting Member

6 Posts

Posted - 2013-04-26 : 10:24:48

Just a note one table has more columns than the other table, but I eleiminated that as an issue as i tried this on two tables with same columns and datatype with same result, thx

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 11:00:40

quote:
Originally posted by reactancexl

I am running it from a different query window. I typed in the syntax provide and get the same results which is all the records in the table. expected result is 1 as there is only one difference in a row. thx

Don't run it from a different window just so we can eliminate the possibility that you are connected to a different database or different server or whatever. So, script the stored procedure, highlight the select statement, run it, and in the SAME window, type exec [dbo].[prc_epo_get_supplier_pi_value_map] highlight it and execute it.

reactancexl
Starting Member

6 Posts

Posted - 2013-04-26 : 14:53:16

I bascally moved the code to a different environment and it worked fine. Something must be going on in the dev environment I was working in. thanks to all for the help. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources