SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Proc outputs differently than QA
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reactancexl
Starting Member

6 Posts

Posted - 04/25/2013 :  21:58:49  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/26/2013 :  00:24:37  Show Profile  Reply with Quote
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
Go to Top of Page

reactancexl
Starting Member

6 Posts

Posted - 04/26/2013 :  09:05:12  Show Profile  Reply with Quote
Yes, it is there. COuld it be some setup in the environment? this is really annoying as this is a simple proc.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/26/2013 :  09:32:27  Show Profile  Reply with Quote
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.
Go to Top of Page

reactancexl
Starting Member

6 Posts

Posted - 04/26/2013 :  09:40:40  Show Profile  Reply with Quote
I did that and the results were 1 record which I expect to see. This is driving me crazy.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/26/2013 :  10:08:13  Show Profile  Reply with Quote
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]
Go to Top of Page

reactancexl
Starting Member

6 Posts

Posted - 04/26/2013 :  10:15:21  Show Profile  Reply with Quote
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
Go to Top of Page

reactancexl
Starting Member

6 Posts

Posted - 04/26/2013 :  10:24:48  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/26/2013 :  11:00:40  Show Profile  Reply with Quote
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.
Go to Top of Page

reactancexl
Starting Member

6 Posts

Posted - 04/26/2013 :  14:53:16  Show Profile  Reply with Quote
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. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000