results differ in SSMS vs User interface

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)

results differ in SSMS vs User interface

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 18:29:59

After many hours of coffee and exposed hair follicles , I need to ask this question so I do not lose my mind

When executing a stored procedure from the front end via adodb call I get one result but when I execute the proc manually in SSMS I get another.


With cmd
  .ActiveConnection = CurrentProject.Connection
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.usp_GetBranchCapacityByCommodity"
  .Parameters.Append .CreateParameter("@BranchID", adInteger, adParamInput, , Me.cmbBranchNumbers)
  .Parameters.Append .CreateParameter("@CommodityID", adInteger, adParamInput, , lngCommodityID)
  .Parameters.Append .CreateParameter("@FirstDayOfWeek", adDate, adParamInput, , dtFirstDayOfWeek)
  .Parameters.Append .CreateParameter("@RequestedShipDate", adInteger, adParamInput, , Me.vwMonth)
  .Parameters.Append .CreateParameter("@WeekCount", adInteger, adParamInput, , lngWeek)
End With

EXEC dbo.usp_GetBranchCapacityByCommodity 1, 2, '3/28/2009', '04/01/2009', 1

Ever seen anything like this? LEt me know if you need more details.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 18:53:15

Here is the actual result I get when I debug the stored procedure


Date                            BranchCapacity DefinitionValue           DefinitionDate          Week   
------------------------------- -------------- ------------------------- ----------------------- ------ 
04                              94518          0                         4/4/2009                Week 2 
05                              94518          0                         4/5/2009                Week 2 
06                              94518          124214                    4/6/2009                Week 2 
07                              94518          148538                    4/7/2009                Week 2 
08                              94518          50783                     4/8/2009                Week 2 
09                              94518          142330                    4/9/2009                Week 2 
10                              94518          57492                     4/10/2009               Week 2

But that Branch capacity field is coming back 0.
Can I not do the following, assign value to a variable and use that variable in another SELECT statement?

[code]
SELECT @BranchCapacity = COALESCE(DefinitionValue, 77777)
FROM dbo.VBranchCapacity vjBranchCapacity WITH (NOLOCK)
WHERE vjBranchCapacity.BranchID = @BranchID
AND vjBranchCapacity.CommodityID = @CommodityID
AND DefinitionDate = @RequestedShipDate

SELECT Date
,@BranchCapacity AS BranchCapacity
,SUM(DefinitionValue) AS DefinitionValue
,DefinitionDate
,Week
FROM dbo.fn_BranchWorkLoad(@BranchID, @CommodityID, @FirstDayOfWeek,@RequestedShipDate, @WeekCount)
GROUP BY DefinitionDate, Date, Week, BranchCapacity
ORDER BY Week, DefinitionDate
[code]

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 19:01:42

When you say you get a different result set back than SSMS, are you running the stored procedure in SSMS passing in the sample values or are you running a query instead?

Yes you can use variables, however you need to know that @BranchCapacity will only contain the value of the last row in the result set where you are assigning it. So if you have multiple rows being returned, you can't use a variable like that.

What does this return:

SELECT COUNT(*)
FROM dbo.VBranchCapacity vjBranchCapacity WITH (NOLOCK)
WHERE vjBranchCapacity.BranchID = @BranchID
AND vjBranchCapacity.CommodityID = @CommodityID
AND DefinitionDate = @RequestedShipDate

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 19:12:04

Thank you Tara.

1. Running the proc in Query analyzer with the above mentioned parameters or debugging the proc with the above mentioned parameters and calling that same proc from the user front end with the above mentioned parameters bring back totally differing results.
2. It returns 1 row, it will always return one row. It is something produced from an SSIS package once per month.

Thanks

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 19:14:56

Do you have access to SQL Profiler? If you do, then I'd suggest running a trace with simply RPC:Completed in it and catching the command that is being executed by the stored procedure. It must be different from what you are running in order for it to return different results, or perhaps you are connecting to different databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 19:32:18

Tara,

I did as you suggested and I see the profiler capturing the activity from that stored procedure. We are using a proxy user so I see the login name in the captured log which I directed SQL profile to dumped into. Could this have to di anything with service packs etc. Now if I manually enter any bogus number in the place of @BranchCapacity, it comes through. This is real strange.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 19:34:47

So what did it show for RPC:Completed? And if you run that same exact query using the same exact account in SSMS, what happens?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 19:55:58

junk! Tara, you are the best! Ok it is time to go home. Staring at this code all day long ha smessed me up. I knew it was a simple mistake. Man I hate this, wasted a whole day but not really I learned how to effectively use SQL Profiler. Notice my fourth paramater in my adodb call

Parameters.Append .CreateParameter("@RequestedShipDate", adInteger, adParamInput, , Me.vwMonth)

I am calling it as integer :(
So here is how Profiler helped me out

Call from client side
exec dbo.usp_GetBranchCapacityByCommodity 1,2,'2009-03-28 00:00:00:000',39904,2

Call from SSMS side
EXEC dbo.usp_GetBranchCapacityByCommodity 1, 2, '3/28/2009', '04/01/2009', 2
Be careful of cut and paste code.

THank you thank you thank you!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-02 : 13:52:54

You're welcome, glad to help!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources